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
651 total views, 2 views today