2115 BeforeUpdate Problem

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

Guest

this code below almost works

Private Sub Patient_Number_BeforeUpdate(Cancel As Integer)
If Me.Dirty = True And Not IsNull(Me.Patient_Number.OldValue) Then
If vbCancel = MsgBox("You are about to change the ID Number of this Patient
from " & Me.Patient_Number.OldValue & " to " _
& Me.Patient_Number.Value & "!! This change will be have the effect of
occuring everywhere throughout this database", _
vbCritical + vbOKCancel + vbDefaultButton2, "SUPER HYPER ULTRA CRITICAL") Then
Me.Patient_Number.Value = Me.Patient_Number.OldValue
End If

but for the error message. what's gone awry w/ this. when i hover over the
fields in the statement below, vba shows me the correct values

Me.Patient_Number.Value = Me.Patient_Number.OldValue

so why won't it get past this line?
 
You cannot change the value of the control in the control's BeforeUpdate
event; that creates a conflict because you're creating a loop.

Instead, use the .Undo method of the control to return to the previous
value. (This works only for bound controls.)

Replace this line
Me.Patient_Number.Value = Me.Patient_Number.OldValue

with this line
Me.Patient_Number.Undo
 
i think i gotta take back what i said about it's working (check mark)....at
least for the moment:

here's the latest iteration of this vba


Private Sub Patient_Number_BeforeUpdate(Cancel As Integer)
If Me.Dirty = True And Not IsNull(Me.Patient_Number.OldValue) Then
If vbCancel = MsgBox("You are about to change the ID Number of this Patient
from " & Me.Patient_Number.OldValue & " to " _
& Me.Patient_Number.Value & "!! This change will be have the effect of
replacing " & Me.Patient_Number.OldValue & " throughout this database!!", _
vbCritical + vbOKCancel + vbDefaultButton2, "SUPER HYPER ULTRA CRITICAL") Then
Me.Patient_Number.Undo
End If
End If
End Sub

clicking cancel did not replace the newly entered value with the old one?

what've i done rong?
 
You also need to set the Cancel variable to True in order to cancel the
BeforeUpdate actions.

Private Sub Patient_Number_BeforeUpdate(Cancel As Integer)
If Me.Dirty = True And Not IsNull(Me.Patient_Number.OldValue) Then
If vbCancel = MsgBox("You are about to change the ID Number of this Patient
from " & Me.Patient_Number.OldValue & " to " _
& Me.Patient_Number.Value & "!! This change will be have the effect of
replacing " & Me.Patient_Number.OldValue & " throughout this database!!", _
vbCritical + vbOKCancel + vbDefaultButton2, "SUPER HYPER ULTRA CRITICAL")
Then
Cancel = True
Me.Patient_Number.Undo
End If
End If
End Sub
 
Back
Top