Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Action Query?

Discussion in 'Information Technology' started by Blair, Nov 6, 2005.

Tags:
  1. Blair

    Blair
    Expand Collapse
    Guest

    I have a simple question. I have a form with several text boxes and a button
    to save the data. When the user clicks the save button the data will be
    added to the database. I only want the data entered when the button is
    pressed to stop partial info from being put in the database. I am wondering
    the best way to add this data to the database. My prof from my intro class
    said to use an action query but the more I research these I get the
    impression this is incorrect for this situation. My question is in this type
    of situation is an action query appropriate for this situation or is there a
    more correct way for this? Thanks in advance.....
     
  2. Loading...

    Similar Threads Forum Date
    Movies Ashdoc's short review---Action Jackson Theatre, Movies & Cinema Dec 12, 2014
    Arts/Society What are your thoughts on the interaction of male / female relationship of marriage? Language, Arts & Culture Oct 15, 2013
    Opinion US must take substantial military action in Syria now Breaking News Aug 30, 2013
    Opinion Facing Adversity - Different Reactions Breaking News Aug 18, 2013
    Controversial Former president of Delhi Sikh Gurdwara Management Committee could face religious action Hard Talk Jul 21, 2013

  3. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Blair.

    > I only want the data entered when the button is
    > pressed to stop partial info from being put in the database. I am wondering
    > the best way to add this data to the database.


    One may use an unbound form and then use an append query to add the new
    record to the table. For example:

    Private Sub SaveBtn_Click()

    On Error GoTo ErrHandler

    CurrentDb().Execute "INSERT INTO tblRequests " & _
    "(Qty, ItemDesc, ItemSize, NeedDate) " & _
    "VALUES (" & Me!Qty.Value & ", '" & Me!ItemDesc.Value & _
    "', " & Me!ItemSize.Value & _
    ", #" & Me!NeedDate.Value & "#);", dbFailOnError

    Exit Sub

    ErrHandler:

    MsgBox "Error in SaveBtn_Click( ) in " & vbCrLf & _
    Me.Name & " form." & vbCrLf & vbCrLf & _
    vbCrLf & "Error #" & Err.Number & vbCrLf & Err.Description
    Err.Clear

    End Sub

    .. . . where tblRequests is the table that the new record will be appended
    to, Qty is a numerical value, ItemDesc is a text string, ItemSize is another
    numerical value, and NeedDate is a Date/Time data type, and the dbFailOnError
    argument will allow the user to not be required to confirm the "Do you really
    want to add this record?" message, without having to turn off the System
    Warnings messages.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips.

    (Please remove ZERO_SPAM from my reply E-mail address so that a message will
    be forwarded to me.)
    - - -
    If my answer has helped you, please sign in and answer yes to the question
    "Did this post answer your question?" at the bottom of the message, which
    adds your question and the answers to the database of answers. Remember that
    questions answered the quickest are often from those who have a history of
    rewarding the contributors who have taken the time to answer questions
    correctly.


    "Blair" wrote:

    > I have a simple question. I have a form with several text boxes and a button
    > to save the data. When the user clicks the save button the data will be
    > added to the database. I only want the data entered when the button is
    > pressed to stop partial info from being put in the database. I am wondering
    > the best way to add this data to the database. My prof from my intro class
    > said to use an action query but the more I research these I get the
    > impression this is incorrect for this situation. My question is in this type
    > of situation is an action query appropriate for this situation or is there a
    > more correct way for this? Thanks in advance.....
     
  4. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    Access doesn't save the information from a form one control at a time -- you
    can intercept and cancel storing the record in the Form's BeforeUpdate
    event. There are discrete situations which trigger the Record being
    written -- when you move to a different Record, when you close the Form,
    when code issues a DoCmd.RunCommand acSaveRecord, or when you leave the main
    Form to enter a Subform Control.

    There are times, I suppose, when it is convenient to use unbound Forms and
    code/Queries, but the vast majority of the time, bound Forms are a simpler,
    easier, and more straightforward approach, not to mention a lot less work.

    Larry Linson
    Microsoft Access MVP



    "Blair" <Blair@discussions.microsoft.com> wrote in message
    news:6B1AE73E-09A1-463C-B3FC-476D185AF4EF@microsoft.com...
    >I have a simple question. I have a form with several text boxes and a
    >button
    > to save the data. When the user clicks the save button the data will be
    > added to the database. I only want the data entered when the button is
    > pressed to stop partial info from being put in the database. I am
    > wondering
    > the best way to add this data to the database. My prof from my intro
    > class
    > said to use an action query but the more I research these I get the
    > impression this is incorrect for this situation. My question is in this
    > type
    > of situation is an action query appropriate for this situation or is there
    > a
    > more correct way for this? Thanks in advance.....
     
  5. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    With a bound form you need to prevent the row being committed to the table in
    any other way than via the command button, so first declare a Boolean
    variable in the form's module's Declarations area:

    Dim blnSaveMe as Boolean

    In the form's BeforeUpdate event procedure set the return value of the
    Cancel argument to True if the variable's value is False and reset the value
    to False if it is True:

    Cancel = Not blnSaveMe
    blnSaveMe = False

    In the button's Click event procedure set the value to True and save the
    record:

    blnSaveMe = True
    RunCommand acCmdSaveRecord

    There is no real answer as to whether this method is more 'correct' than
    using an action query. The use of an unbound form from which the data is
    written to the table by means of an SQL ststement can be more appropriate in
    some circumstances. You can also use DAO or ADO to update the table. The
    following is the code from a simple form's module which does this by
    establishing a recordset of people selected by surname and populating the
    controls on the form one record at a time. In this case each field IS
    updated individually as soon as the user edits a control:

    Option Compare Database
    Option Explicit

    Dim dbs As DAO.Database, rst As DAO.Recordset

    Private Sub cboFindContact_AfterUpdate()

    Dim strSQL As String

    If Not IsNull(Me!cboFindContact) Then
    strSQL = "SELECT * FROM Addresses WHERE LastName = " & _
    Chr(34) & Me!cboFindContact & Chr(34)

    Set rst = dbs.OpenRecordset(strSQL)

    With rst
    Me!txtFirstName = !FirstName
    Me!txtLastName = !LastName
    End With
    Else
    Set rst = Nothing
    Me!txtFirstName = Null
    Me!txtLastName = Null
    End If


    End Sub

    Private Sub cmdNext_Click()

    On Error Resume Next
    With rst
    .MoveNext
    If Err = 0 Then
    If .EOF Then .MoveLast
    Me!txtFirstName = !FirstName
    Me!txtLastName = !LastName
    End If
    End With

    End Sub

    Private Sub cmdPrevious_Click()

    On Error Resume Next
    With rst
    .MovePrevious
    If Err = 0 Then
    If .BOF Then .MoveFirst
    Me!txtFirstName = !FirstName
    Me!txtLastName = !LastName
    End If
    End With

    End Sub


    Private Sub Form_Close()

    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

    End Sub

    Private Sub Form_Load()

    Set dbs = CurrentDb

    End Sub


    Private Sub txtFirstName_AfterUpdate()

    With rst
    .Edit
    !FirstName = Me!txtFirstName
    .Update
    End With

    End Sub

    Private Sub txtLastName_AfterUpdate()

    With rst
    .Edit
    !LastName = Me!txtLastName
    .Update

    End With

    Me!cboFindContact.Requery

    End Sub


    "Blair" wrote:

    > I have a simple question. I have a form with several text boxes and a button
    > to save the data. When the user clicks the save button the data will be
    > added to the database. I only want the data entered when the button is
    > pressed to stop partial info from being put in the database. I am wondering
    > the best way to add this data to the database. My prof from my intro class
    > said to use an action query but the more I research these I get the
    > impression this is incorrect for this situation. My question is in this type
    > of situation is an action query appropriate for this situation or is there a
    > more correct way for this? Thanks in advance.....
     
  6. Blair

    Blair
    Expand Collapse
    Guest

    Thanks to everyone who replied. It was all very helpful. i decided in the
    end to use a bound form. My primary key is an autonumber field so I was
    having difficulties with the unbound form to popluate this field. I didn't
    now about canceling the action in the before update event on a form. This
    will definetly come in handy! Thanks,
    Blair
     
  7. Andrew R in Canberra

    Andrew R in Canberra
    Expand Collapse
    Guest

    "Blair" wrote:

    > I have a simple question. I have a form with several text boxes and a button
    > to save the data. When the user clicks the save button the data will be
    > added to the database. I only want the data entered when the button is
    > pressed to stop partial info from being put in the database. I am wondering
    > the best way to add this data to the database. My prof from my intro class
    > said to use an action query but the more I research these I get the
    > impression this is incorrect for this situation. My question is in this type
    > of situation is an action query appropriate for this situation or is there a
    > more correct way for this? Thanks in advance.....


    My immediate thought was just to make the fields you need filled in
    "required" in the table design - then access won't let you add a record
    without those fields being filled - Cheers A
     

Share This Page