Simulate XP's Form_Undo event in ACC2000

[kadelarsen]

I have read that Access 2002 has an "Undo" event for forms as well as for controls. Access 2000 does not support this feature. However, I am investigating any possible back-door methods of implementing this functionality.

The problem: I have a form based on a single table with a button to open another form based on a multi-table Oracle view referencing the record on the first form. I am trying to enable/disable the button dynamically. Fields in the first table's key must be filled, and a couple date fields in the record must also be filled, before the button is valid. I have added handlers to the "Change" and "After Update" events for the controls to call a function enabling/disabling the button. However, the user may fill out all the fields (causing the button to be enabled), then choose "Undo," and the button will still be enabled even though the fields are now blank. This is why I want to respond somehow to the "Undo" event (including activation through the menu and ctrl-Z or ESC keypress).

Alternatives: I have considered putting a timer on the form to enable/disable the button. However, I'd prefer to enable/disable appropriately based on events.

Possibilities: I might be able to intercept the "Undo" menu choice using the Office2000 CommandBars interface. Even if this worked, it probably wouldn't intercept the keyboard ctl-Z or ESC commands. To handle these, I might be able to intercept all keypresses going to the form. Another problem with these solutions is that I want the enable/disable code to run AFTER the "Undo" operation has completed and the fields have become blank.

Any suggestions (other than migrating to Access 2002)?

 

[nickjar2]

What about the on_Current event. Does that get triggered when Undo is 'Done'?

Also, have you thought about putting code behind the button which checks that all fields are filled in? Just a thought!

Nick

 

[nickjar2]

Sorry, not the on current event. I meant the change event.
Some event must get triggered if the user undoes or undo's.

 

[kadelarsen]

Thanks for the feedback! I already have code in the change events for two comboboxes and one textbox. There is no change event for the form. The change events for the controls do not get invoked when the user chooses "Undo."

You're right about code behind the button. Right now, the button code is this:

Private Sub cmdOpenMyOtherForm_Click()
    DoCmd.RunCommand acCmdSaveRecord  'save current record to avoid errors in MyOtherForm
    DoCmd.OpenForm "frmMyOtherForm"
End Sub

I'm relying on Access/ODBC/Oracle errors to occur if the button is clicked when fields contain invalid entries. Of course, I will look at bullet-proofing this later, but right now I'm concentrating on attempting to enable/disable the button at the right times.

This issue is still open. I could not find anything in the MSDN Knowledge Base about it, or anywhere on the Web, other than that the feature is introduced in Access 2002 (which shows it is lacking in Access 2000).

 

[kadelarsen]

Update: I was able to invoke a procedure when the "Undo" button is clicked on the toolbar, and when Ctrl-Z is pressed using an AutoKeys.^z macro. There's an obscure way to customize the "OnAction" property of menu items and toolbar buttons by right-clicking on the button (or menu item) to get the "Customize..." dialog, then right-clicking again on the toolbar button to get a long menu including "Reset" and "Properties." I wasn't sure I liked how this was going, however, so I found Office 2000 Developer documentation on "Working with Command Bar Events." They suggest creating a class called "clsCBEvents" which I have modified. Here's what I have so far:

Public WithEvents colCBars         As Office.CommandBars
Public WithEvents cmdUndoTyping    As Office.CommandBarButton

Private Sub cmdUndoTyping_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    Dim frm As Form
    
    On Error Resume Next
    Set frm = Screen.ActiveForm
    On Error GoTo 0
    If Not frm Is Nothing Then
        'TODO: call form UndoTyping event handler through the pointer "frm"
        'pass the parameters through so that the handler can cancel default event handling
    End If
End Sub

The trick will be to make this a generic message-routing mechanism that will send the undo event to whatever form is the active one (concept similar to MFC's message map mechanism).

 

[kadelarsen]

It works! (sort of...) I created the class module "clsCBEvents" with the following code:

Private mfrmNotify As Form
Private WithEvents mcmdUndo As Office.CommandBarButton

Public Sub InitEvents(frmNotify As Form)
    Set mfrmNotify = frmNotify
    Set mcmdUndo = CommandBars("Edit").Controls("&Undo")
End Sub

Private Sub mcmdUndo_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
'call form Undo event handler through the pointer "mfrmNotify"
'pass the parameters through so that the handler can cancel default event handling
    If Not mfrmNotify Is Nothing Then
        mfrmNotify.cmdUndo_Click Ctrl, CancelDefault
    End If
End Sub

Next, I added code to my form:

Private CBEvents As clsCBEvents

Public Sub cmdUndo_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    DoCmd.RunCommand acCmdUndo
    EnableMyOtherFormButton
End Sub

Private Sub Form_Close()
    Set CBEvents = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
    Set CBEvents = New clsCBEvents
    CBEvents.InitEvents Me
End Sub

Now the button is disabled when the user clicks "Undo" on either the toolbar or the "Edit" menu. Ctl-Z also disables the button, but I'm not sure if this is caused by my old combobox change event handler instead of the new code. ESC still performs "Undo" without disabling the button, however.

One problem with this is that "Undo" is now essentially being called twice -- first from my code, then from the built-in default "Undo" event handler (because my code does not set CancelDefault to True). The reason I did this was to clear the combobox before running my enable/disable button subroutine. I'd still prefer to have the Undo function normally and get notification AFTER the Undo is complete.

 

[kadelarsen]

Update: I cleaned up the code and resolved several problems.

First, the above code only works when the MDB is opened fresh and the form is opened the first time. After that, an error occurs on the line getting the Undo CommandBarButton:

    Set mcmdUndo = CommandBars("Edit").Controls("&Undo")

I fixed this problem by using the ID of the Undo button to find it:

    Set mcmdUndo = CommandBars.FindControl(, 128)

Second, I set the form's KeyPreview property to True and added a KeyDown event handler. For ESC and Ctrl-Z keydown events, the handler invokes Undo and enables/disables the button.

Finally, I fixed the problem with Undo being called multiple times and inappropriately when no Undo should have been available. The code now sets CancelDefault to True and wraps the Undo to ignore errors if Undo is not available.

Here is the current working code:

Class module "clsCBEvents:"

Private mfrmNotify As Form
Private WithEvents mcmdUndo As Office.CommandBarButton

Public Sub InitEvents(frmNotify As Form)
    Set mfrmNotify = frmNotify
    'Set mcmdUndo = CommandBars("Edit").Controls("&Undo")
    Set mcmdUndo = CommandBars.FindControl(, 128)
End Sub

Private Sub Class_Initialize()
    Set mfrmNotify = Nothing
    Set mcmdUndo = Nothing
End Sub

Private Sub Class_Terminate()
    Set mfrmNotify = Nothing
    Set mcmdUndo = Nothing
End Sub

Private Sub mcmdUndo_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
'call form Undo event handler through the pointer "mfrmNotify"
'pass the parameters through so that the handler can cancel default event handling
    If Not mfrmNotify Is Nothing Then
        mfrmNotify.cmdUndo_Click Ctrl, CancelDefault
    End If
End Sub

Form module "Form_frmMyForm:"

'Set KeyPreview property to True in Design View

Private CBEvents As clsCBEvents

Private Sub cmdOpenMyOtherForm_Click()
    DoCmd.RunCommand acCmdSaveRecord  'save current record to avoid errors in MyOtherForm
    DoCmd.OpenForm "frmMyOtherForm"
End Sub

Public Sub cmdUndo_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    CancelDefault = True
    DoCmd.RunCommand acCmdUndo
    EnableMyOtherFormButton
End Sub

Private Sub EnableMyOtherFormButton()
    Dim blnEnable As Boolean
'check fields for valid values to determine blnEnable
'use Text property instead of Value if control is active
    cmdOpenMyOtherForm.Enabled = blnEnable
End Sub

Private Sub Form_Close()
    Set CBEvents = Nothing
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyEscape Or KeyCode = vbKeyZ And ((Shift And acCtrlMask) > 0) Then
        KeyCode = 0
        On Error Resume Next
        DoCmd.RunCommand acCmdUndo
        On Error GoTo 0
        EnableMyOtherFormButton
    End If
End Sub

Private Sub Form_Open(Cancel As Integer)
    Set CBEvents = New clsCBEvents
    CBEvents.InitEvents Me
End Sub

Conclusion: Maybe using a timer would have been simpler. However, it is possible to create your own Form_Undo event handler in Access 2000. B-)