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.