How to Use Form and Subform Variables in Microsoft Access

In today's episode, we'll take a look at how to use form and subform variables in our Microsoft Access database. Using these two variables can make your code more reusable and easy to read compared to using other methods. We'll show how to open a form, set a variable to access its properties and methods. As an example, we'll set some text box values on a parent and subform as well as set the captions for more than one form using reusable code.
Related Videos:
2 Ways to Create Command Buttons on MS Access Forms
• 2 Ways to Create Comma...
How to Make a Listbox in MS Access Part 1: Single-selection
• How to Make a Listbox ...
How to Create a Multi-Column Combo Box in MS Access, Adjust the Width and Bind it to the Table
• How to Create a Multi-...
How to Use Form and Subform Variables in Microsoft Access
You are watching this video now!
How to Retrieve Any Column Value from a Combo Box or List Box in Microsoft Access
• How to Retrieve Any Co...
How to Create a Subform in MS Access
• How to Create a Subfor...
How to Use the Tab Control in Access: Tabs with Subforms Example
• How to Use the Tab Con...
MS Access Option Groups - How to Use Radio Buttons
• MS Access Option Group...
How to Use Reference Expressions to Get Form Values in MS Access
• How to Use Reference E...
How to Use Nz in Microsoft Access to Handle Null and Empty Values
• How to Use Nz in Micro...
After Update on Form Controls in Microsoft Access
• After Update on Form C...
For developers looking for a new role, check out and sign up:
www.toptal.com/qKaO2b/worlds-...
Needing to hire technical resources for your project? Get the best:
www.toptal.com/qKaO2b/worlds-...
Want to get access to premium content made just for you and have a chat with me? Find me on Patreon :
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.com/posts/how-to-...
Want to work with me 1:1? Contact me today and book your free 20 min consultation!
Contact form you can find at www.mackenziemackenzie.com/
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksynergy.com/fs-bin/...
Got a TH-cam Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy.com/seanmackenz...
#msaccess #vba

[@inukpaw]

Make so much sense when it is well presented - love the videos

[@seanmackenziedataengineering]

Thanks Paul!

[@interestingamerican3100]

Commenting for the algorithm. Love the content! I learn every time I watch.

[@seanmackenziedataengineering]

Awesome! Thank you!

[@rogerio74c]

Wow! Fantastic! Love to know that! Access doesnt stop to surprise me.

[@seanmackenziedataengineering]

Glad it helped! Cheers

[@dbdata9836]

10/10

[@seanmackenziedataengineering]

Thanks!

[@dbdata9836]

thankssss

[@seanmackenziedataengineering]

You’re welcome!

[@dbdata9836]

great

[@seanmackenziedataengineering]

Thx!

[@jerrydfowler3480]

Sean could this be used to open a form and give it a certain Record Source? I have a form that I could use with multiple queries. One is just for members that have emails and one is for those that don't or rather get item per the mail. So when I choose one button I would like it to open the form and give it the email records, or I choose another button and open the same form but with records without emails or prefer mail. Thanks for you top notch videos

[@seanmackenziedataengineering]

Great question. Indeed, you can swap out the Record Source at runtime in order to change the records you are working with. When doing this exercise, you can check out the properties when you type frm. The dot after frm will let you see different properties. One of them is Record Source. Put in an alternate SQL statement and it will switch the view. Alternatively, if the different views are all from the same table, you can set the form filters instead. Like this video: th-cam.com/video/uq3cgaHF6fc/w-d-xo.html

[@jim27105]

I would like to use a subform to display various forms, which the user can run a query from. In other words the subform ‘window’ would just display form 1, form 2, form 3. It seems like it would be easy to do but all the videos i find are for data entry, not navigation. Any suggestions or perhaps a future tutorial?

[@seanmackenziedataengineering]

Interesting idea. You may be able to change the control source for the subform control in order to switch the subform viewed. As an alternative, you may want to use the tab control, then put different subforms on each tab. You can control which tab is shown, based on some criteria on the parent form.

[@ibrahimalrabiah1179]

Nice, but I have figured a problem it works once for me . After a while ( Dim as Form ) stopped working , so I replaced all controls to Full original bath FORMS!Mainform!CboResult , like that and it works again , is there any solution to find what stopped the Dim FORM FROM WORKING , THANKS

[@seanmackenziedataengineering]

Interesting! Yes, if you have frm as Form A, referring to another open form B, but that form B is closed (and reopened), the reference in form A will break. This is also true if you refer to forms from a Sub or Function in a module.

[@EstoYOtro]

Certainly a way to go. IF. If the line 'Set frm = Forms(strForm)' would work (4:20). On all my machines, it produces Error 438, Object doesn't support this property or Method. And yes, I used exactly your code. Any idea?

[@seanmackenziedataengineering]

In the code, when you go Tools > References, what entries are checked off there?

[@EstoYOtro]

Sean, thanks for coming back to my problem. I thought about the references as well, but I found the error, and it's absolutely weird: The form I put the code into had a tab-control. Once I deleted that, all was working fine. Go figure...@@seanmackenziedataengineering