How best to verify a successful update to a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I would like to verify that a successful update has taken place and then
display a simple message indicating the update to the user. CAn anyone offer
a strategy to verify an update? The only idea I've come up with is to make a
temp copy of the record being updated then compare the original to the
after-updated version. If they differ, assume a successful update. I'll use
a simple MsgBox to info the user.

Thanks,
Rich
 
Hi Rich,

Not knowing all of the requirements of your appplication, it's a little hard
to guess exactly what the best solution is for you, but the fact that you are
considering a MsgBox implies you have some level of comfort with VBA
programming. If I were you, I'd try having the AfterUpdate event on the data
entry Form trigger your MsgBox message.

From the Access online help file: "The AfterUpdate event occurs after
changed data in a control or record is updated."

That way you leave the "heavy lifting" to Access--just what it's good at!

(tongue firmly in cheek) Warning: once you get into the deep pool of serious
event-triggered programming, your users will only ask you to do it more and
more ;-)

Good luck,

Ed
 
rich said:
I would like to verify that a successful update has taken place and then
display a simple message indicating the update to the user. CAn anyone offer
a strategy to verify an update? The only idea I've come up with is to make a
temp copy of the record being updated then compare the original to the
after-updated version. If they differ, assume a successful update. I'll use
a simple MsgBox to info the user.
Click to expand...


How are you doing the update?

If it's a bound form, then the Form's AfterUpdate event only
occurs if the record was saved correctly and the form's
Error event will fire if the save failed. (There is a bug
that doesn't report a failed save if the save was triggered
by closing the form and the record fails a validatation or
referentional integrity check,)

If you are using an UPDATE or INSERT INTO query in a VBA
procedure, then use the Execute method to run the query.
The dbFailOnError argument will trigger an error if the
query can not save the data

If you are just messing with records in a table/query
datasheet view, you will have to visually check the records.
 
Thanks Ed and Marshall for your replies.

I guess it's easiest to describe this is as an update to an existing
employee record. I have a form which displays a single record from the
master table. The user can update practically any field on the form. After
making 1 or more changes, user clicks the Save Updates button which fires the
Click event. The code at the moment for this event is:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

I can change this to whatever is best for a 1-click record save. There are
several individual fields which can be changed at a time so I did not use any
After Update events for the controls. I've never used the event at the form
level. I don't know what would actually trigger it.

After the user clicks Save, I want the record to save, (without closing the
form, although I am fine with closing/re-opening the form), then a
confirmation message telling the user that the update was successful. When
the user responds to the MsgBox, then the form closes and the
previous/calling menu will reopen.

The form is currently bound to the employee master table.

I hope this spells it out clearly for everyone. Let me know if more detail
is needed.

Thanks,
Rich
 
Unless you are still using A2, you should not use those
DoMenuItem things, even if a wizard creates them for you.
Find another way, even if it's the RunCommand method.

To save a record, use this line in the button's Click event
procedure:
Me.Dirty = False

You can put the confirmation MsgBox in the Form's
AfterUpdate event:

Sub Form_AfterUPdate()
MsgBox "record saved"
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub
 
Back
Top