Passing a Form 2
By Richard Rost
15 months 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
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.
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
|
