Control if a form updates table

[DaveM]

I would like to know how best to accomplish this data entry form requirement.

I have a form with a number of fields which is linked to a table. When the
operator opens the form i would like all the fields to be blank and the user
can tab through the fields as necessary. I have two command buttons. One
allows to exit the form (all set with that one) with no saving of the form
data, while the second button opens a message box which asks the operator if
they wish to submit the information on the form (within the fields) Y/N and
the message box has all the data from the fields listed for confirmatin.

What i need help with is how do i NOT allow the fields to update the table
if the operator tabs past the last field in the form and it arrives back to
the first field? Anotherwords i want the form to only cycle through the
fields without updating the table. The table update is left for once the
command button event is intitated. If the operator selects yes i wish the
form data to be added to the table and closes the form. Also, how do i clear
all the fields if the operator decides not to accept the information once it
arrives in the message box? That is he/she selects No on the message box
message.

Thanks
DaveM

 

[Allen Browne]

The only way to block updates is the cancel the BeforeUpdate event of the
*form* (not controls.) Access fires that event regardless of what triggered
the update (e.g. closing the form, tabbing past the last control, moving
record, closing Access, toolbar/menu/ribbon, pressing Shift+Enter, ...) The
event doesn't fire if nothing was updated.

Your 'cancel and close' button's Click event procedure would contain code
like this:
If Me.Dirty Then Me.Undo
DoCmd.Close acForm, Me.Name

Your 'save and close' button's Click event procedure:
If Me.Dirty Then Me.Dirty = False
DoCmd.Close acForm, Me.Name
You'll need error handling too: if the save is cancelled, Access will notify
you that the attempt to set the Dirty property to False didn't work.

The confirmation is then handled in Form_BeforeUpdate, like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Save?", vbOkCancel, "Confirm") <> vbOk Then
Cancel = True
End If
End Sub

 

[DaveM]

Thank you Allen. Before i go to work on this would it be easier to fire an
Update Query that would append the table with these fields/data if the
operator chooses to accept the information? If not that's fine and i'll
proceed. If so, what would the "save and close" button code look like? The
one item i want to note is that one field in the table is an "Auto Number"
field which is not visible in the form and whatever proceedure i use to get
the data into the table will need to consider the auto num field. This may
not be a issue but just wanted to point it out.

Thanks Allen, your help is very appreciated.

DaveM

 

[Allen Browne]

a) It will be *much* easier to work with the bound form events than to build
action queries to insert/delete/edit the data in an unbound form.

b) The save'n'close button will have just the 2 lines posted previously,
plus error handling. If error handling is new, here's an example:
http://allenbrowne.com/ser-23a.html
(You don't have to use the logging that page: the first "simplest" example
at the top will do.)

c) You don't need to worry about the autonumber. Access will assign a number
without you needing to do anything.