🔧 Passing a Form in Microsoft Access

Today, I will show you how to pass a form as a variable to a subroutine or function in Microsoft Access. This allows you to send the actual form as an object to your code and modify it based on the received form.

Clara from Pureland, Texas, a platinum member, sent in a question. She asked if it's possible to create a global function to set the form's background color to gray when a record is not active. This is smart because it avoids duplicating code across multiple forms.

The goal is to use a global subroutine to prevent repetitive code. If all you need is to check if 'is active' is true and set the background accordingly, then the same code can be utilized across different forms.

First, you need to understand how to pass a form as a variable. Although you can technically use a function for this, a subroutine is sufficient because it does not return a value. Functions typically do return values. Both subroutines and functions are called procedures. However, for simplicity, I often refer to them as functions.

This topic came up in the forums as well. Richard Van Wagner had a similar query, trying to use "me" in a public global function. However, "me" only works within forms and reports, not globally. Adam, Kevin, and Sammy provided helpful inputs, and we concluded that passing the form as a variable is the way to go.

This tutorial is for those with some VBA experience. If you're new to VBA, I recommend watching a beginner tutorial first. The key concept here is using the "on current" event to trigger actions when you navigate through records or when the form opens.

First, let's work on this in the customer form. Open the customer form in design view. You want the code to run when you move from record to record and when the form opens. Insert it in the "on current" event.

In the "on current" event, check if the 'is active' field is not active. If it's not, set the form's background color to gray. Forms themselves do not have background colors, but their sections do. Specifically, the detail section's background color can be set to gray using RGB values. For instance, RGB(100, 100, 100) gives a medium gray. If the field is active, set it to another color, like blue.

After testing and verifying that the code works for one form, the next step is to make it work globally. Cut out the existing code from the form module and paste it into a global module. Name the subroutine, for example, "public sub change background." Ensure you move the "end sub" to the correct place.

During compilation, the code won't recognize "is active" or "me" because they are specific to the form. You need to tell the subroutine which form you want to apply changes to by passing the form as a variable.

Instead of using screen.active form, you should pass the form as a variable. Define the subroutine to receive a form variable, making it an object of type form. Use object variable references for fields and sections within that form accordingly.

After modifying the subroutine to use the form variable, compile it to check for errors. Then, in the form's "on current" event, call the subroutine with the "me" keyword, which refers to the current form.

Finally, test the updated customer form to ensure the changes apply correctly. The same subroutine can now be used for other forms. For instance, you can modify it to work with an "is paid" field in the orders form, and adjust colors as needed for different forms.

To explore additional concepts, such as restoring the default design color of each form or different field names, stay tuned for the next part of this tutorial. Members can watch it immediately.

For a complete video tutorial with step-by-step instructions, visit my website using the link below.

For a complete video tutorial on this topic, please visit https://599cd.com/PassingaForm?key=Dev.To