Dynamically activate “OnClick” function in command button in a subform in access 2007

Question

I've created a form with 3 subforms in it to display an user's details and the inventory the user has. The form enables user to update the details displayed. Thus each subform has a "save" and "undo" button. I'm trying to create a "Clear All" button on the parent form which undo all changes there are in all the 3 subforms.

I don't really want to retype the same codes used in the 3 "undo" buttons, so is there a way to make use of the Onclick function of the 3 buttons?

I've tried the following with one subform first:

Private Sub ClearAllParentForm_Click()
    Me.Subform1.Form.clearButton_Click
End Sub

However, the form invokes the subform's beforeupdate event instead (a messagebox that prompts user to save the updated record). I've also tried to change the codes to Me.Subform1.Form.Undo which produces the same issue. Is there somewhere which I did wrongly or is my concept wrong?

Sorry, just started using Microsoft Access 2007 only recently so quite confused with some stuff.

Excellll Excellll · Accepted Answer · 2012-05-23T14:30:12

@JeffO is right, but I thought I'd expand on what he said with a little more guidance.

If you have the following in a Worksheet module:

Private Sub ClearButton_Click()
    'ClearButton code here.
End Sub

You need to move the ClearButton code into a Sub in a general module. So in a new module, you need the following:

Sub clearbtn()
    'ClearButton code here.
End Sub

Now, back in the Worksheet module you call this code on button click with one line:

Private Sub ClearButton_Click()
    Call clearbtn
End Sub

Making these changes to your event triggers will allow you to use them elsewhere in your code. The implicit lesson here is that you can't call an event trigger sub from elsewhere in VBA, but other subs can be called from within an event trigger.

Dynamically activate “OnClick” function in command button in a subform in access 2007

2 Answers