Passing a Form 2
By Richard Rost
2 years ago
Pass a Form as Object Variable to Global Sub, Part 2
In this Microsoft Access tutorial, I will show you how to pass a form as an object variable to a global subroutine in VBA. We'll explore making fields optional for flexible use across different forms, using the Tag property for temporary variables, and ensuring your form's original background color is preserved. This is part 2.
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Recommended Courses
Keywords
TechHelp Access, passing form object variable Microsoft Access VBA, global subroutine Microsoft Access VBA, optional parameters Access VBA, form background color VBA, dynamic form background color VBA, modifying form properties VBA, advanced Access VBA techniques, Richard Rost VBA tutorial, event handling Access VBA
Intro
In this video, we continue exploring how to pass a form as a parameter to a subroutine in Microsoft Access VBA. You'll learn how to make your ChangeBackground subroutine flexible by accepting an optional field name for checking form values, making it easy to use across different forms. We'll also cover how to store and retrieve a form's original background color using the Tag property and the On Load event, so each form keeps its unique appearance. Debugging, compiling, and key tips on handling form and control references in VBA are also discussed. This is part 2.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today is part two of my passing a form to a subroutine or function or procedure—whatever you want to call it. If you haven't watched part one yet, go watch that first, then come on back.
All right, so in part one, we created our On Current event here, which calls ChangeBackground and sends Me. Me is the current form that you're on, and when we go and take a look at this, it receives it as F the form, and then checks if F is active. And then if it's active, it makes it bluish, and if it's not active, it makes it gray.
Okay, we want this to now work with other forms. Now, the first problem is that other forms are not going to have IsActive as a field. It might be something different. It's got to be some kind of field you want to be able to check to see if it's true or false. So we're going to add that in here as another variable we're going to pass.
Now, I'm going to make it optional. Because I have this, let's pretend you got ChangeBackground already in use in like five or six different forms. You don't have to go and change all of them. So by making this optional, you can say, okay, optional FieldName as a string equals IsActive. That means if I don't specify what the field name is, use IsActive. So I have to change all the existing ones. And we'll pretend like, you know, IsActive is the one that you currently use. So we'll take FieldName and we'll put that right here.
Now, is it inside quotes or not inside quotes? What do you think? Is it the actual word FieldName? All right, if it's a field called FieldName, it would look like that, but it's not. We want IsActive to go there. So we want a field name like that. This is a variable. The variable has a string like IsActive inside of it. Okay, so now our ChangeBackground subroutine can work with any form.
So let's save it. Give it a debug compile. Let's come back over here. Now let's go to the order form. Let's just open it up right here so it gets rolled through the records. Okay, let's come off top design view. Open up the properties, go into On Current, and ChangeBackground. Me is the form, and this time we're going to say IsPaid. Is the field name IsPaid? I think that's what it is. Let's go check. Get out of here. Yeah, IsPaid. Okay, all right. So we're going to say ChangeBackground. Me is the form. You always got to send me. There's no way around that one. And then IsPaid is the field name. Save it. Debug compile. Come back out. Yeah, let's close it. Close it. Open it. And there we go. Okay, look at that. It's working.
Okay, now the next thing I said we'd tackle is that color issue. It's changing to a blue. I don't want to use blue. I want the gray. Everybody who's not active should be gray. But I want to stick with whatever color this guy had when it opened. Now, unfortunately, there is no way in VBA to go in and say, all right, I want you to look at the detail section and find out what its BackColor property was. You can't do that at the module, excuse me, at the global module level. What you can do though is when this form opens, you can have it remember what its background color was and save that somewhere.
Now, where are you going to save it? Well, you don't really want to save it. I mean, you could save it as a form field. You could save it as a global array. You could put it in a table. There's a lot of places you could put it. But there's one handy dandy place. You can stuff it where it's great for putting temporary variables. And that is the Tag property. Now I'm going to use the form's Tag property right there. You could put anything you want in there. And it's great for storing temporary information. Right? So when this form opens, you're going to look at whatever the color of the detail section is and save it in the Tag property.
Then your global module just says, hey, set the background color to whatever's in that Tag property. Let's see how this works. All right. So in the form's open event, let's go to events. You can either put it on load or on open. It doesn't matter. I'll put it in on load. This is right there. It's closer. I'm going to say Me.Tag equals Me.Section(0).BackColor. Okay. That's saying in the Tag property, put the BackColor when the form opens. Or loads. Okay. Copy this. And let's do the same thing. Come here. Close. Close. Save it. Let's do the same thing in the customer form.
Oh, what looks like? Oh, yeah. Okay. I was going to say. Yeah. It's still got that background color. One of the things you have to be careful of is if you change the color in VBA and then go into design mode, it might save that in the design. That's not something you want. All right. So now in your on-load event, do the same thing. Put the BackColor in the Tag property. Okay. Now we just have to tell ChangeBackground to read that Tag property. So definition right here instead of this bluish, how do we get the Tag for the current form that we're working with? Well, it's F.Tag. That's it. Okay. Save it. Give me a debug compile. Let's close that. Close it. Close it. Open it. Oh, look at that. I got my background color that I'm used to. Move through some records and look at that. Uh-huh. Uh-huh. See? Let's open up an order. Okay. There's nothing in there. Let's go to the order form. We can cycle through. Well, look, it's green. And gray. See? It's keeping its background color.
So there you go. It's pretty easy to do. It's just really, it's mostly just knowing this nomenclature. It's knowing how to talk to Access and knowing how to refer to these things. Right? FFieldName is how to get a particular field's value off of a form. Right? That's how you get a section. You can refer to any of its properties, controls, values, any of that stuff using this nomenclature like that.
Do you like this stuff? Do you like learning with me? I got tons and tons of developer lessons available on my website. Check it out. There's a link. I take it from a newbie of VBA programming to advanced developer stuff. I got right now 45 levels of developer classes, I think. And each one's at least an hour long. Some of them are three or four hours long. We have fun. We get into it. So check it out. And if you join as a member, you get a free class. Platinum members can get a free developer class every month after they finish the beginner and the expert classes. So check it out.
But that's going to do it for today, folks. That's your TechHelp video. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
Hey, special thank you and shout out to our diamond sponsors. First, we have Juan Soto with Access Experts Software Solutions, Manufacturing Experts Specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at accessexperts.com. Another shoutout to Sammy Shama from Shama Consultancy. Sammy is a certified Microsoft Office specialist. And he not only offers Access application development, but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access project, Sammy is your guy. Check them out at ShamaConsultancy.com.
TOPICS:
Passing a form as a parameter to a subroutine
Creating the On Current event
Calling the ChangeBackground subroutine
Using the Me keyword in forms
Checking if a form is active
Changing background color based on form activity
Handling forms without IsActive field
Making a parameter optional in subroutines
Using FieldName variable for different forms
Debugging and compiling VBA code
Modifying the background color in VBA
Storing temporary variables in the Tag property
Using the Tag property to save state information
Retrieving saved background color from the Tag property
Implementing On Load event to set Tag property
Using Me.Section(0).BackColor to get background color
Reading Tag property in a global subroutine
Applying background color from Tag property
Understanding Access object model and nomenclature
COMMERCIAL:
In today's video, I'm going to teach you how to pass a form to a subroutine or function in Access VBA. First, we'll review how to use the On Current event to call ChangeBackground and send the current form. Then, I'll show you how to make this work with other forms, including passing an optional field name. Next, we'll tackle a color issue by using the Tag property to remember and set background colors. You'll see practical examples and get tips on handling form properties and controls. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper, my friends.
Quiz
Q1. What does the ChangeBackground subroutine initially check to determine the form's background color?
A. The form's caption
B. The form's BackColor property directly
C. The form's Tag property
D. The form's IsActive field
Q2. What new feature was added to the ChangeBackground subroutine in part two?
A. It can now work with multiple forms using different fields to check for activity
B. It can create new forms dynamically
C. It can run background tasks in a separate thread
D. It can import data from an external database
Q3. How does the ChangeBackground subroutine determine which field to check?
A. By hardcoding the field name in the subroutine
B. By passing the field name as an optional parameter
C. By reading a configuration file
D. By using a global variable
Q4. What is the default field name used by the ChangeBackground subroutine if none is provided?
A. IsPaid
B. FieldName
C. IsActive
D. Status
Q5. Why is the Tag property useful in this scenario?
A. It is a hidden field that cannot be modified by the user
B. It is an excellent place to store temporary information such as the form's original BackColor
C. It allows saving data that persists after the form is closed
D. It automatically encrypts any data stored in it
Q6. Which event is used to save the form's BackColor to the Tag property?
A. On Activate
B. On Close
C. On Load or On Open
D. On Click
Q7. How does the ChangeBackground subroutine retrieve the form's original BackColor?
A. From a global array
B. From the Tag property of the form
C. From a configuration file
D. From a database table
Q8. To make the subroutine compatible with any form, how is the field name passed?
A. As a number
B. As an integer
C. As an optional string parameter
D. As a Boolean value
Q9. Why is On Current event important in this tutorial?
A. It initializes global variables
B. It calls ChangeBackground and sends the current form object to it
C. It handles user authentication
D. It performs a database backup
Q10. What happens if you modify the form's design after changing its color in VBA?
A. The form will revert to a default color
B. The form may save the changed color in its design, which is not usually desired
C. The form will reset all its properties
D. The form's color will be reset every time it's opened
Answers: 1-D; 2-A; 3-B; 4-C; 5-B; 6-C; 7-B; 8-C; 9-B; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone focuses on how to pass a form as a parameter to a subroutine or function in Microsoft Access VBA. In part one of this lesson, I covered how to use the On Current event to call a ChangeBackground subroutine, passing in the current form by using Me. In that setup, the procedure checked if the form was active, and then set the background color accordingly. If the form was active, it became blue, otherwise gray.
Now, we want this technique to work for other forms, not just the current one. The challenge is that other forms may not have the same field, such as IsActive, to determine if the form is active or not. The field potentially varies from form to form. To solve this, we add an optional parameter to the subroutine for the field name. By making it optional and setting a default value, I ensure that existing forms using ChangeBackground will continue to work without any changes. If no field name is specified, it defaults to IsActive; otherwise, you can provide another field name, such as IsPaid. The logic inside ChangeBackground now checks the value of whichever field name is supplied.
It's important to format the code correctly so you're referencing the field via the variable, not a literal string. This allows the ChangeBackground routine to be flexible and function across different forms with various field names.
After saving and compiling the updated code, I demonstrate how to use this on a different form, such as an order form which uses the IsPaid field. By specifying ChangeBackground and passing Me and the correct field name, the routine works as expected and highlights the forms based on the correct activity status.
Next, I address the issue of colors. By default, the routine was setting the background color to blue for active forms, and gray for inactive ones. However, you might want all inactive forms to stick with the gray color, and use whatever color a form originally had as its background when it opened. The problem is that VBA does not provide a simple way to get the original BackColor property of the detail section at the global module level. The solution is to capture the starting background color when the form opens, and store that value temporarily.
The best place to keep such temporary information is in the form's Tag property, which is designed for holding custom data. When the form loads, I have it store the current background color in the Tag property. This way, the global ChangeBackground routine can retrieve this value and apply it when needed.
To implement this, you just add a line in the form's On Load event that copies the current background color into the Tag property. It's important to do this before anything else changes that color. This process should be repeated for every form where you want to use this logic.
One thing to be careful about is changing the background color in VBA and then switching to design mode, as this can save the color in design view and alter your defaults unexpectedly. Make sure to set the Tag property in the On Load event for each relevant form.
Lastly, I update the ChangeBackground subroutine so that instead of setting a hard-coded color, it retrieves the color stored in the Tag property for the given form. After saving and compiling, you can test this by opening and navigating through records in different forms. The inactive records will use the default gray, and the forms will retain their original background colors as intended.
The core of this solution is understanding how to reference form properties and controls through the Access object model. Using the appropriate nomenclature lets you refer to fields and sections correctly across all your forms.
If you enjoy learning these concepts, I offer a variety of developer lessons on my website, starting from basic VBA programming up to more advanced topics. There are over 45 levels of developer classes to explore, each packed with practical content. Members can even get free classes with their subscriptions.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Passing a form as a parameter to a subroutine
Creating the On Current event
Calling the ChangeBackground subroutine
Using the Me keyword in forms
Checking if a form is active
Changing background color based on form activity
Handling forms without IsActive field
Making a parameter optional in subroutines
Using FieldName variable for different forms
Debugging and compiling VBA code
Modifying the background color in VBA
Storing temporary variables in the Tag property
Using the Tag property to save state information
Retrieving saved background color from the Tag property
Implementing On Load event to set Tag property
Using Me.Section(0).BackColor to get background color
Reading Tag property in a global subroutine
Applying background color from Tag property
Understanding Access object model and nomenclature
|
