Gina's Coffee Shop

Calling an Event Procedure from one Form to another…

Every once in a while I need to use the same code I have in the Event Procedure of a one Control in another Forms Event Procedure, whether it be the On_Click event of a Command Button or the After_Update of a Combo Box.  99% of the time I would just turn that into in Function or a Sub and drop it into a Module and done, I can now reuse it over and over with no issues.  So why would I or anyone need to do this?  Simple… the Users makes an update on a Subform and I need to refresh the Main Form and that code is specific to the Controls on that Form/Subform, what easier way than to just reuse the code already available in an Event Procedure in another Form.  (This happens about 1% of the time, if not less.  It is not something I would recommend to do regularly.)  Now, I could just copy/paste it to the Subform but if I ever have to make a change I have to remember to hit both places!  Leaving it where it is makes sense, now, I just need to change it in one place.

So now, the specifics…

First and foremost, you must either remove the Private from in front of the Sub and/or change it to Public but it’s not necessary.  I tend to change it to Public, only because if I need to find it I can use Find > Public Sub as a parameter finding it quickly.  (Using Find > Sub will cause me to stop at every Event in every Module!)

Example…
Was Private Sub cboSearch_AfterUpdate changed to Public Sub cboSearch_AfterUpdate()

And the difference between Public Sub and Private Sub?  Ken Sheridan explains it best…

Declaring a function in a standard module Public exposes it throughout the database, so it can be called from queries, forms, code in other modules, etc.  Declaring a function in a standard module Private exposes it only within the module, so should be done where a function is called by another function or procedure in the same module, but not by anything outside the module.   The same name can be used for Private functions in different modules, but a Public function’s name must be distinct within the whole database.

From: Private or Pubic Functions?

Then you will need to make sure the Form that you are referencing is open.  That said, you can open it minimized or hidden but it must be open or you cannot access the Public Function.

From Subform (Child) to Main Form (Parent)
Function or Sub

Call Forms.frmYourForm.pubYourPublicFunction

OR…

Call Forms("frmYourForm").pubYourPublicFunction

Combo Box

Call Forms("frmYourForm").cmdYourComboBox_AfterUpdate

OR…

Me.Parent.cmdYourComboBox_AfterUpdate

Command Button

Forms("frmYourForm").cmdYourCommandButton_Click

OR…

Me.Parent.cmdYourCommandButton_Click

From Main Form (Parent) to Subform (Child) Form
Function or Sub

Me.sfrYourSubform.Form.pubYourPublicFunction

Combo Box

Me.sfrYourSubform.Form.cmdYourComboBox_AfterUpdate

Command Button

Me.sfrYourSubform.Form.cmdYourCommanButton_Click

From another Control on the same Form (Parent or Child)
Function or Sub (The same way you would call a normal Module)

Call pubYourPublicFunction

Combo Box

Call cboYourComboBox_AfterUpdate

Command Box

Call cmdYourCommanButton_Click

From Subform (Child) to Main Form (Parent) of Main Form (Parent) Form
Function or Sub

Me.Parent.Parent.pubYourPublicFunction

Combo Box

Me.Parent.Parent.cmdYourComboBox_AfterUpdate

Command Button

Me.Parent.Parent.cmdYourCommandButton_Click

From a Public Module to Subform (Child) via Main Form (Parent)
Function or Sub

Forms("frmMain")("sfrYourSubform").pubYourPublicFunction

Combo Box

Forms("frmMain")("sfrYourSubform").cmdYourComboBox_AfterUpdate

Command Button

Forms("frmMain")("sfrYourSubform").cmdYourCommandButton_Click

 624 total views