Microsoft Access - Before Update - Asked By D on 19-Dec-11 08:04 PM

I have Before Update code that creates a prompt when a record is revised (see below).  Can I have more than one Before Update code?  I also want to have a revised date field auto populated when a revision is ok'd after the prompt.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 'If the form data has changed a message is shown asking if
   'the changes should be saved. If the answer is no then
   'the changes are undone
 
   On Error GoTo BeforeUpdate_Error
 
   If Me.Dirty Then
    'if record has been changed the dirty property
    ' is set to true Display message to save the record
    If MsgBox("The record has changed - do you want to save it?", _
    vbYesNo + vbQuestion, "Save Changes") = vbNo Then
     Me.Undo
    End If
   End If

BeforeUpdate_Exit:
   Exit Sub

BeforeUpdate_Error:
   MsgBox Err.Description
   Resume BeforeUpdate_Exit
End Sub

Web Star replied to D on 19-Dec-11 10:25 PM
No, only one BeforeUpdate event use in code. but you can call multiple function within it so you can put your all code in same event event you doing multiple function than sperate different method and call it in BeforeUpdate  event simply.

wally eye replied to D on 20-Dec-11 09:33 AM
Web Star is right, just change your code a bit here:

    If MsgBox("The record has changed - do you want to save it?", _
    vbYesNo + vbQuestion, "Save Changes") = vbNo Then
   Me.Undo
  Else
    me.RevisedDate = Date()
    End If
D replied to wally eye on 20-Dec-11 11:02 AM
Thanks so much - works perfect!
D replied to wally eye on 06-Jan-12 08:11 PM
The code that creates a prompt if any changes are made works great. 

However, I'm wondering if this code can be amended so that I only get the prompt if the LastName field is changed?  If changes are made to any other field I would not want the prompt.

Thank you.
wally eye replied to D on 06-Jan-12 08:27 PM
You could use the LastName_AfterUpdate event to pop up a message box, or populate a second control with the LastName when the record is loaded and compare LastName to the second box before popping up the message or updating the date.
D replied to wally eye on 06-Jan-12 08:33 PM
How would I accomplish your first suggestion.."You could use the LastName_AfterUpdate event to pop up a message box"

I can copy code but I'm not experienced with writing code.

Thanks in advance.

wally eye replied to D on 08-Jan-12 10:42 PM
It would looks something like this:

public sub LastName_AfterUpdate()

    If MsgBox("The Last Name has changed - do you want to save it?", _
     vbYesNo + vbQuestion, "Save Changes") = vbNo Then
     Me.Undo
   Else
     me.RevisedDate = Date()
      me.dirty = false
    End If

End Sub

It's been a while since I've actually done this, but I think setting me.dirty to false will cause the changes to be written.  Actually, I'm not sure if the AfterUpdate event will do what you want, you might have to use the BeforeUpdate event:

public sub LastName_BeforeUpdate(Cancel As Integer)

    If MsgBox("The Last Name has changed - do you want to save it?", _
vbYesNo + vbQuestion, "Save Changes") = vbNo Then
 Cancel = true
 Else
me.RevisedDate = Date()
me.dirty = false
End If

End Sub