Action Query?

B

Blair

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

'69 Camaro

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.
Click to expand...

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.
 
L

Larry Linson

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
 
K

Ken Sheridan

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
 
B

Blair

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
 
A

Andrew R in Canberra

Blair said:
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.....
Click to expand...

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
 
Top