Need VBA Code Guidance - BeforeUpdate Event

[phillfri1]

I'm afraid I'm beyond my expertise level on this one. I have some BeforeUpdate event code that I would like to figure out how to utilize in a single function to be run from the form's property events tab (passing the ActiveControl, or the Form itself) rather than having to enter VBA code in the form's VBA module for every data field on my Form. I'm seeing 2 issues here. It seems that the Ctrl.Undo command works ONLY in the BeforeUpdate event (?) AND I can't figure out a way to trigger the BeforeUpdate Cancel parameter via a called function. Code below. Any advice/suggestions would be appreciated.

Private Sub cbxTag_BeforeUpdate(Cancel As Integer)
Dim ctrl As Object
10 If Not Me.tglSafety.Caption = "P" (Windinga 2 font checkmark) Then Exit Sub
20 Set ctrl = Me.ActiveControl
30 If Me.NewRecord = False And Not Confirm(ctrl) Then ctrl.Undo:: Cancel = True
End Sub

Line 10 is a Form level toggle button that controls the application of the BeforeUpdate code to all bound data controls on the form (it's too easy to make accidental mods to an open Access data form in a multi-application, multi-monitor environment). The toggle gets turned "on" in the Form's current event. The user can manually turn the toggle to "off" and "on" if they are doing a bunch of updates to a record.
Line 20 just grabs the Form's ActiveControl
Line 30 firstly passes through any new records. If the record is already existing then the Confirm function asks the user if they really want to modify the value of the ActiveControl. If the user response is negative the ActiveControl is set back to its pre-edited value and the event chain is canceled.

 

[jdraw]

You may get some insight from this Video tutorial by Richard Rost. I recommend you watch it at least to get some context.

 

[The_Doc_Man]

It seems that the Ctrl.Undo command works ONLY in the BeforeUpdate event (?)

I believe that ctrl.UnDo works any time after the form's .Current event but before the form's internal Update event. This "Update" event is an internal (i.e. not visible to us) event that occurs after the BeforeUpdate event but before the AfterUpdate event.

More precisely, the ctrl.UnDo can only affect what has been changed on the form but not yet written back to the form's underlying recordset. This is condition is on bound controls for which the .Value and .OldValue mismatch. The primary function of .UnDo in that case is to restore .OldValue to .Value and reset the the control's appearance. Because this depends on .OldValue, be aware that .Undo has no effect on any control that is unbound, because unbound controls don't have a .OldValue property. That is also why you can't undo an unbound control.

 

[theDBguy]

It seems that the Ctrl.Undo command works ONLY in the BeforeUpdate event (?) AND I can't figure out a way to trigger the BeforeUpdate Cancel parameter via a called function.

Hi. Welcome to AWF!

I think you're correct and so you might have to employ something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If ValidateMe(Me) = False Then
    Me.Undo
    Cancel = True
End If
End Sub

ValidateMe() would be your validation function in a Standard Module. For example:

Public Function ValidateMe(frm As Access.Form) As Boolean
If IsNull(frm.Controls("ControlName") Then
    ValidateMe = False
Else
    ValidateMe = True
End If
End Function

 

[LincolnWatson]

Thank you so much for the info.

 

[Isaac]

I believe that ctrl.UnDo works any time after the form's .Current event but before the form's internal Update event. This "Update" event is an internal (i.e. not visible to us) event that occurs after the BeforeUpdate event but before the AfterUpdate event.

More precisely, the ctrl.UnDo can only affect what has been changed on the form but not yet written back to the form's underlying recordset. This is condition is on bound controls for which the .Value and .OldValue mismatch. The primary function of .UnDo in that case is to restore .OldValue to .Value and reset the the control's appearance. Because this depends on .OldValue, be aware that .Undo has no effect on any control that is unbound, because unbound controls don't have a .OldValue property. That is also why you can't undo an unbound control.

In other words, while Dirty=True

 

[The_Doc_Man]

In other words, while Dirty=True

Well, ... that IS a requirement for UnDo to have a visible effect - but if the form was not dirty, you could try to issue the UnDo anyway. Not sure what would happen if the form WASN'T dirty even though it was in between the two referenced events - which is, by the way, the most likely condition for a form to be... in between _Current and _BeforeUpdate. You MIGHT get a "not available at this time" error. I remember making my forms test Me.Dirty before enabling the command button CANCEL as a way to discard what had been entered. If I did that, it was probably because the UnDo operation wasn't always available.