Power Tip: Use the Form property when referencing subforms in your code

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

Today’s Power Tip is provided by Juan Soto.

Form variables can make your code sizzle

I love using form variables. They make my code faster and easier to read, and they also provide a great way to remote control another form without being on that form or having it be the focus. Another advantage is that reading record values off a form is far quicker than using a recordset using a table off the disk. If you know the data you’re looking for is already loaded on a form, reading it by using a form variable can speed up your application.

For example, let’s assume I’m coding in a form called frmOrders and I need to read controls on an open form called frmCustomers (you can only associate a form variable to an open form). I would use the following code to instantiate my variables:

Dim frm as Form_frmCustomers
Dim lngCustomerID as Long

If CurrentProject.AllForms(“frmCustomers”).IsLoaded then
Set frm = Forms!frmCustomers
    ‘Use the frm variable to read fields off the form 
lngCustomerID = frm.CustomerID
‘Use another technique to find CustomerID
End  If

One of the advantages of declaring frm as Form_frmCustomers instead of as the generic Form type is that IntelliSense will pop up to help you refer to controls on the form. You can do this with any form that’s already loaded; just make sure you use “Form_” followed by the form name when declaring variables of this type.

Subforms work too, but you need to use the Form Property

Subforms are considered part of the main form and lose their own identity once they are loaded. In order to reference a subform, you need to use the Form property to associate the form variable with the subform. Let’s assume frmOrders is the parent form and frmOrderItems_sub is the name of the subform inserted into frmOrders. If you need to reference the subform, you could do so in the following matter:

Dim frm as Form_frmOrderItems_sub

Set frm = Forms!frmCustomers.frmOrderItems_sub.Form

Notice the reference to the .Form property at the end of  the second statement, which is needed in order to associate the subform with the variable frm.

Note 1: You can only use form variables on forms that have modules; otherwise, the compiler will error out.

Note 2: Make sure you reference the correct name of the subform on the main form or the code will not work. For example, if the subform control has a SourceObject set to a form named “frmOrderItems_sub” but has a control name of “OrderItems” on the main form, then your code should look like this:
Dim frm as Form_frmOrderItems_sub

Set frm = Forms!frmCustomers.OrderItems.Form

I encourage you to start using form variables in your code when you need to reference one form from another. It’s another great tool in Access that will make sharing information easier across your application and can even speed it up.

Juan Soto is senior Access developer at AccessExperts.netHis blog is at can subscribe to it here. Juan is a frequent speaker at Access and SQL Server user groups nationwide and is a contributor to the Access blog.

Send your Power Tips to!

–Chris Downs