Field and record updates

[garfieldsevilla]

I’m having problems understand how and when Access 2003 saves data.

I have a simple form where a user can view or modify data in a table. I have disabled the X on the menu bar to stop them exiting and provide two VBA buttons, one for save/exit and another for abandon/exit.

On exit, there is a routine which needs to check if a field was modified and update a change log:

Private Sub CloseFS_Click()
    If Me.EA <> Me.EA.OldValue Then UpdateHistory Me.EA
        End If
End Sub

While testing we found that after changing the EA field, UpdateHistory is not always called as Me.EA.OldValue does not always contain the old value on exit. If the user activates/ modifies another field, Me.EA and Me.EA.OldValue can have the same value on exit.

I have also tried DLookup but it appears that the field change is updated in the database on modifying another field. Can this behaviour be changed/controlled?

Is there a more reliable way to check if a field has been changed since the form was opened as you close it? (without simply creating a change variable)

Also, can I be sure no field will be modified on closing with the following abandon code?

Private Sub Abandon_Click()
    Me.Undo
    DoCmd.Close , , acSaveNo
End Sub

Many thanks!

[TheSmileyCoder]

You could use the Dirty property of the form. The only "problem" is that if you say add a HELLO to a textbox, and then delete the HELLO, the form will still consider itself dirty, even when it is actually not (anymore)

If Me.Dirty then UpdateHistory Me.EA

I would also put code like that into the forms BeforeUpdate. Imagine someone presses Ctrl-S instead of using your button. Putting the code in the BeforeUpdate would be sure to catch cases such as that.


The Me.Undo should do fine in preventing the record update.
The Docmd.Save ,,acSaveNo just means your not saving DESIGN changes to the FORM. This has nothing to do with RECORD changes.