If you really want something and can’t reach it, what do you do? Do you say, “Oh well, I guess I can’t have that”? No – if you really want it, you might grab a step stool or a ladder, or you might get someone taller to get it for you. You extend your reach.
In Access applications, every once in a while you want to make something happen in a form when you are not working on that form. For instance, say you are working in a client list form and you might want a pop-up message box providing details (notes from your last conversation perhaps) about the client. Say that you have a button on the client detail form that reaches into your conversation log and provides just that info. Wouldn’t it be neat if, as you are reviewing the client list form, you could get those details from the other form without having to reprogram a new button?
Now you might think that because you are working in one form that you can’t push a button programmatically in another form. But in fact you can; and that is exactly what we will show in this month’s
Wizard. We will extend our reach beyond our grasp by reaching into another form and effectively pushing a button.
Private vs. Public
Before we can reach into another form to call code that lives there, it is important that we understand the difference between private and public. Programmers use the term scope to distinguish how far a variable, routine, or function exists. A scope can be either private or public.
Private means that the scope exists only within its own container. For example, the subroutines in a reports or a form are private by default, which means that are available only to that form or report and no further.
Public means that a variable, routine, or function can be seen from anywhere within the application. Good examples of this are subroutines and functions that live in modules. They, by default, are public so they can be seen across the application.
As a general rule, you want to limit the scope of any variable or subroutine to only where it will be needed. This makes it a lot easier to understand what is going on in your application and also reduces the opportunity of addressing a variable or subroutine from someplace that you don’t intend.
How the Magic Happens
In the
June 2016 Wizard, I showed how to call one routine on a form from another routine on the same form. It involves no more than incorporating the name and event identifier in your code.
To crystallize this, let’s assume 2 forms (frm1, frm2) with one button on each form (cmd1 on Form 1 and cmd2 on Form 2). Put the following code in the on-click event of the button in frm2:
Public Sub cmd2_Click()
MsgBox "I'm on Form 2"
End Sub
Make sure you change the default first line from
Private Sub to
Public Sub. When we make this change the button becomes clickable from anywhere within the application. Now open form 2, click the button and you get the “I’m on Form 2” message.
On Form 1, create the code for Button 1 as follows:
Private Sub cmd1_Click()
Forms("frm2").cmd2_Click
End Sub
Now, on Form 1, run the form and click the button, and you should see the “I’m on Form 2” message.
The key here is the syntax to get to the other form. Generically, it is as follows:
Forms("myForm").myRoutine
myform is the name of the form and
myRoutine is the name of the routine. This tells Access to look in the form “
myForm” for the routine
myRoutine.
The other Key element is that the routine being called must be declared as Public. This allows for it to be seen and used from other places outside of the form.
See this month’s Trap for one other important caveat when using this technique.
When you create an event for a button (or for any other object on a form), the default name consists of two parts: The first is the name of the button, followed by the underscore character, and then the name of the event.
Conclusion
This month, I showed you how to reach across forms to make something happen. With a couple of tweaks and the right syntax, you have now extended your reach further across your application to make things happen.
