The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

Reach Beyond Your Grasp – Clicking Buttons Across Forms

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()
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:
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.


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.

Tip of the Month: How to Detect if a Form is Already Open

In Access, forms and reports are different from tables in some unexpected ways. The data in a table is always available regardless of whether the table is open or not. The code and the elements in a form (or report) are not available unless the form is open. If you are using techniques such as those presented this month, you should check to make sure the form is open before trying to do something with it.

The following code will do the trick:

If CurrentProject.AllForms("MyForm").IsLoaded = false Then
    DoCmd.OpenForm "MyForm"
End If

These few lines will make sure that the form you are interested in will be open and ready to manipulate in your code.

Trap of the Month:
Be Aware of a Form's Availability

In this month’s Wizard, I showed how to click a button on a form that is not the active form. In order for this to work, it is critical that the target form already be open. Although the form may exist in Access, if it is not open, then it is not possible to do anything with it.

For a fairly simple way to avoid this problem, see this month’s Tip.

Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886