Passing a Form
By Richard Rost
2 years ago
Pass a Form as Object Variable to Global Subroutine
In this Microsoft Access tutorial, you will learn how to pass a form as an object variable to a global subroutine using VBA. I will show you how to create a global function to change the form's background color based on the "IsActive" field, minimizing code duplication across multiple forms.
Clara from Pearland, Texas (a Platinum Member) asks: I have a field called "IsActive" on several of my forms, which can relate to customers, orders, products, and various other types of data. Is it possible to create a global function to set the form's background color to gray if the current record is not active? I would prefer to use a global function to avoid duplicating the code across multiple forms.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, passing a form as a variable in VBA, Access VBA global subroutine, global function in Microsoft Access, VBA pass form to subroutine, setting form background color VBA, change form background color based on field value, Access form background color inactive records, VBA on current event, is active field VBA
Subscribe to Passing a Form
Get notifications when this page is updated
Intro
In this video, we'll talk about how to pass a form as a variable to a subroutine in Microsoft Access using VBA. You'll see how to create a global subroutine to set a form's background color based on a field value, which helps you avoid duplicating code across multiple forms. We'll cover the difference between functions and subroutines, using the "on current" event, referencing form fields, working with form and global modules, and handling the Me keyword. This video focuses on making your VBA code more efficient and reusable when working with forms.
Transcript
Today we're going to talk about passing a form as a variable to a subroutine or a function. So you can send the actual form as an object to your code and it can do stuff based on whatever form it gets.
Today's question comes from Clara in Pureland, Texas, one of my platinum members. Clara says, "I have a field called 'is active' on several of my forms, which can relate to customers, orders, products, and various other types of data. Is it possible to create a global function to set the form's background color to gray if the current record is not active? I would prefer to use a global function to avoid duplicating the code across multiple forms."
That's very smart Clara. We want to use a global procedure to prevent having to duplicate the same code over and over and over again in multiple forms. And if it's the same thing, if it's just look at 'is active' and if so, set the form background to gray, then you can use that same code across multiple different forms.
If you know how to pass a form as a variable, and I say procedure because you wouldn't need a function for this because technically a function returns a value. It doesn't have to return a value, but usually when you're talking about a function, it returns a value. A subroutine does not, and both of those together are collectively called procedures. Yes, I know. I'm nitpicking, but part of my job is to teach you guys stuff. So that's trivia. Between you and me, "function" is just fine. I call them functions all the time.
This also came up today in the forums. Richard Van Wagner had a very similar question. That's when I tend to make videos on this stuff as one, two, three, or even more people ask the same question. So he posted this and then I'm like, wait a minute, Clara posted something a little while back. So let me put a video together. But he's got the same basic situation. He wants to say, "Okay, I got the same thing going on in multiple forms. How do I do it?"
And now Richard's mistake was he's trying to use "me" in a public global function, which can't do that because "me" is only for forms and reports. It's that specific object and the global module has no idea what "me" is. There's no "me" object. And of course, the guys chimed in. Adam came in first with how do you do it? We're going to pass it as a form variable. Kevin posted some code very similar. I was going to use "active form," but we'll discuss active form in a minute. Sammy posted something.
So let's take a look at what we have to do to get this to work. Now, first of all, this is a developer-level video. What does that mean? Well, you're going to need to know some VBA. So if you're new to VBA, if you've never done VBA programming before, go watch this first. It'll get you started in about 20 minutes. We're going to use the "on current" event. That's how you trigger something when you move from record to record. It also fires when the form opens for the first record.
So go watch this. We're going to create our own global function. Well, technically, it's going to be a subroutine, but watch this if you want to learn how that works. And go watch this video on variable scope and visibility. The same thing applies to subs and functions as well. There's a scope of a function or a subroutine that it only works with a particular form, like within that form, or it can work with the entire database. Right? There's global and then there's local. So go watch this too if you're confused about that.
These are all free videos. They're on my website. They're on my YouTube channel. Go watch them and then come on back.
Okay, so here I am in the TechHelp free template. This is a free database you can download from my website. Let's go into the customer form. Now I already have a field called "is active." Let's use that one. Now let's first write this as if it's going to be just for this form and then we'll port it over to a global function, or very often I'm going to probably say function throughout the whole video, but it's technically a subroutine.
All right, so let's go into design view. And we want this to run when we move from record to record, and when the form first opens, we're going to put it in the "on current" event over here... Okay, we're going to say in here, if not "is active," right, that's the name of the field. If not "is active"... I try not to write stuff that's not whatever, but in this particular case, it's okay. All right, then what are we going to do?
What are we going to do? We're going to set the background color of the form to gray. Now forms themselves don't have background colors. The sections in the form do. So we're going to set the detail section background to gray. Okay, and that's going to look like this: "me.section(0)" is the detail section ".backcolor = whatever color you want."
I like to use RGB. Let's make it 100, 100, 100. And if you're not familiar with the sections and with the RGB function, I've got videos on those, of course. I'll put links down below. This here will be like a medium gray. It's a value from zero to 255. Okay, otherwise, if it is active, we're going to set it to some other color. Let's do like a bluish color. We'll go... See, this is red-green... we'll make this brighter. So 255 blue. Blueish. Okay, so it's going to check the value of "is active."
Blue, if it's true. Gray, if it's false. Always do a quick debug compile. Come back out. Me, let's close her. Open her back up again. All right, there we go. There's that blueish color. All right, and blue and gray. See? And blue again. Okay, that's working.
All right, so that's how you do it for one form. Now I want to do it for other forms as well. Let's say the orders form... Let's say on the order form, we can change this guy as well. If we've got a field here called "paid." So let's just make it work, first of all, for just this one form, but I'm going to take that code and we're going to cut this code out, snip it, and we're going to go over to our global module, which is out here.
Okay, these global modules, the stuff that's in here, can work with any object in the database. All right, if it's in your forms module, which is where we were a minute ago, this thing, right, you're in the customer form's module. This code only works in this form. Okay, and this module here can use "me" as an object because "me" is whatever form you're on.
Right, there are global modules or database level modules, we should call them, right, then there's form level modules and report level modules. Those only work with the form or report that they're in. All right, global module here. So I'm going to come down here. We're going to paste this in here. Let's give it a name. Let's call it "public sub change background."
All right, let's move the "end sub" down underneath it. Okay. Now, save it. Yes. All right, let's go to debug compile now and see what happens. There's no idea what "is active" is. Okay.
It's also going to have no idea what "me" is. Right, if you were to rem this stuff out and just have that and do a debug compile, it's going to say, "I've got no idea." Invalid use of the "me" keyword because you can't use "me" in a global module.
So let's put that back. All right. So what we're going to have to do is we're going to have to tell "change background" what form we want to apply this stuff to.
Now, as a side note, Kevin and I both discovered this, and the forums are talking about it. Initially, I was going to try to use screen.active form, but you can't because when the "on current" event runs for the first time, the form doesn't technically exist yet. It will work the second time after that, moving through the records, but that first time that it loads, it won't work.
I was going to do a whole separate video on active form. I've got another video coming out on active form in the future. I've covered it in a bunch of lessons.
But for this one, we have to specifically tell the subroutine what form we want to do stuff to. So I'm going to send in F as a form. It's an object variable of type form. Okay.
We're familiar with strings and longs and date times and currency and all that stuff. Well, there are object variables too, like form. You can actually say, "This is a form that I'm telling you." Okay. Now, we just have to refer to the stuff down here as being part of that form. Okay. Now "is active" is a field on the form.
So here's how you'd refer to that. It's going to be F and then in parentheses and quotes just like that. That's how you refer to a field on that form. Just like you'd say "F!first name" or "F!customer since" or any of those fields. Okay. And this is going to be super easy. Watch this.
"Me" is technically that form talking about itself, right. All you got to do now is switch this to F. That's it. F.section(0). Okay. Now compile this. It compiles fine. Save it again. All right.
Now we just got to tell the customer form. Let's close this and get back to it here. Okay. Now here in the form "current" for the customer form. Okay. Now I'm going to say "change background me." What is "me"? "Me" is the form that you're in, right. The customer form.
"Change background me" is going to send this as a form variable to "change background" and do that stuff. Okay. Save it again. Let's close it and open it again. And let's move to the fields of the records... Oh, look at that. It's working. It's working. Right. And all you had to do in here, right, in the "on current" event was say "change background me."
Okay. Now there's still a couple things we got to do here. First of all, we're going to make it work with another form as well. Okay. We're going to make it also work with our order form. Now order form doesn't have an "is active," but it has "is paid." So let's do the same thing with the order form using that field. We'll use a different field name.
And also as you'll note, the customer form's original design color, it was like a light blue. This guy's green. I don't want to always use the same colors, right. So we'll come in here and the "change background" right here, and I'll tell you how to change this to whatever the default design color of that form was. So every form can still have its own color. And you don't got to worry about changing that in your code. That'll be cool too.
And we will tackle both of those things in tomorrow's video. So tune in tomorrow, same Bat-time, same Bat-channel. Part two. Or if you're a member, you can watch it right now because one of the benefits of being a member is you can watch stuff before it's available to the public. So yeah, come and check it out.
But that's going to do it for today. There's your TechHelp video. I hope you learned something. Live long and prosper my friends. I'll see you tomorrow for part two.
A 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 shout out to Sami Shama from Shama Consultancy. Sami is a certified Microsoft Office Specialist. 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, Sami is your guy. Check them out at shamaconsultancy.com.
TOPICS:
Passing a form as a variable to a subroutine
Setting a form's background color based on a field
Difference between functions and subroutines
Using the "on current" event in VBA
Writing global procedures
Using form-level versus global modules
Setting section background colors in forms
Using RGB function for color values
Referencing form fields in VBA
Using Me keyword in form modules
Avoiding the active form issue in VBA
Sending form objects as parameters
Compiling and debugging VBA code
Modifying code for multiple forms
COMMERCIAL:
In today's video, I'll show you how to pass a form as a variable to a subroutine or function in Access VBA. Learn how to create a global subroutine that can set your form's background color based on a field value, avoiding code duplication across multiple forms. We'll dive into creating the subroutine, understanding the "on current" event, and correctly using form variables. Whether you're a beginner or an experienced developer, this tutorial will enhance your VBA skills. 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 is the main purpose of passing a form as a variable to a subroutine?
A. To store form data temporarily
B. To allow data changes to persist across multiple sessions
C. To avoid duplicating code across multiple forms
D. To fetch form data from a remote database
Q2. Clara from Pureland, Texas wants to set the form background color based on which field?
A. Is valid
B. Is active
C. Is available
D. Is visible
Q3. What is the difference between a function and a subroutine according to the video?
A. A function cannot accept parameters, while a subroutine can
B. A function must return a value, whereas a subroutine does not have to
C. A subroutine is used only within forms, while a function can be global
D. A subroutine must end with "Return", while a function ends with "End If"
Q4. What keyword cannot be used in a public global function?
A. Public
B. Global
C. Static
D. Me
Q5. What VBA event is used to trigger code when moving from record to record or when the form opens for the first record?
A. On Load
B. On Open
C. On Close
D. On Current
Q6. Which section's background color is changed in the example provided in the video?
A. Header
B. Footer
C. Detail
D. Navigation
Q7. In the global subroutine "Change Background", how do you refer to fields on the form?
A. F('fieldname')
B. Form('fieldname')
C. F![fieldname]
D. F!fieldname
Q8. What object type should be sent to the "Change Background" subroutine to represent a form?
A. Object
B. Variant
C. Form
D. Control
Q9. Which method failed because the form did not technically exist when the "On Current" event ran for the first time after loading?
A. Form.LoadForm
B. Screen.ActiveControl
C. Form.ActiveForm
D. Screen.ActiveForm
Q10. How can the "Me" keyword be effectively replaced when passing a form as a variable?
A. By using a module-level variable
B. By using the current form's name directly
C. By referring to the form as 'F'
D. By embedding the form's code in the subroutine
Q11. Why would you need to change the design color of different forms as mentioned in the video?
A. To differentiate them visually without changing the global subroutine code
B. To apply different themes each time the database is updated
C. To match the color preferences of different users
D. To improve performance and load time of the forms
Answers: 1-C; 2-B; 3-B; 4-D; 5-D; 6-C; 7-D; 8-C; 9-D; 10-C; 11-A
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, 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.
Topic List
Passing a form as a variable to a subroutine
Setting a form's background color based on a field
Difference between functions and subroutines
Using the "on current" event in VBA
Writing global procedures
Using form-level versus global modules
Setting section background colors in forms
Using RGB function for color values
Referencing form fields in VBA
Using Me keyword in form modules
Avoiding the active form issue in VBA
Sending form objects as parameters
Compiling and debugging VBA code
Modifying code for multiple forms
|
