Check form field edit before update

[mickelb]

I have a form bound to a table and need VBA to :

1) Check if a text field (SURNAME) has been edited in the current record (key is MEMNO)
2) If it has then present a Yes/No message box along th elines "You have just edited this field. Is this correct?"
3) If No reset text field to original value and end the routine
4) If Yes then save the updated value in the bound table and also update a second table with a SURNAME field for the record number (MEMNO) open in the form

I won't place any of the failed code here, or it is likely to confuse matters, other than I have tried oldvalue and undo without success.



I am looking at the Before Update event to run this routine.

Any advice appreciated.

[ranman256]

in the form code, paste the code below at the very TOP.
the MV.. variable will capture the original value, the BEFOREUPDATE event will decide to keep it.

Private mvSurnameOrig


     'capture the original value
Private Sub SURNAME_GotFocus()
mvSurnameOrig= SURNAME 
End Sub


Private Sub Addr_BeforeUpdate(Cancel As Integer)
If SURNAME <> mvSurnameOrig Then
  If MsgBox("You have just edited this field. Is this correct?", vbQuestion + vbYesNo, "Accept") = vbNo Then
     Cancel = True
     SendKeys "{ESC}"
  End If
End If
End Sub

[JamesDeckert]

@ranman
shouldn't "SURNAME = mvSurnameOrig" instead be "mvSurnameOrig = SURNAME"
also shouldn't the Addr_BeforeUpdate be SURNAME_BeforeUpdate

[mickelb]

Thanks for replies.

@ranman

Is this code the Before Update event for the SURNAME field?
Private Sub Addr_BeforeUpdate(Cancel As Integer)
If SURNAME <> mvSurnameOrig Then
If MsgBox("You have just edited this field. Is this correct?", vbQuestion + vbYesNo, "Accept") = vbNo Then
Cancel = True
SendKeys "{ESC}"
End If
End If
End Sub

What event does this code go in?:
Private mvSurnameOrig


'capture the original value
Private Sub SURNAME_GotFocus()
SURNAME = mvSurnameOrig
End Sub

I can't get it to work if I put the latter in the On Open event for the form - Invalid Attribute in Sub or Function with Private highlighted

[ranman256]

but yes
mvSurnameOrig =SURNAME