Microsoft

How to reference an Access subform

Referencing a control on an Access form is easy. Referencing a subform and its controls is a little more complex. To Access, a subform isn't a form at all, but a control, so you must reference it as such.

One of the most common mistakes I see, and hear about it, is improper syntax when referencing controls on a subform. As far as Access is concerned, a subform is just another control on the main form -- and that's what confuses folks.

First, let's review referencing controls in general. Use either of the following syntax statements to reference a control on a main form:

Forms!formname!controlname
Me!controlname

(In more recent versions, you can substitute bang (!) with dot (.) between objects.)

To refer to a subform or a control on a subform, you must remember that Access treats the subform as a control. Essentially, you have a form with a control with a control. To express that arrangement in terms Access can decipher, you need the Form property as follows

Forms!mainform!subform.Form.controlonsubform
Me!subform.Form.controlonsubform

In other words, subform is simply a control on the main form.

What I commonly see is a simple transposition of the Form property and subform, which generates a runtime error:

Forms!mainform.Form.subform.controlonsubform

In this form, Access assumes Form is a control. When it can't find a control named form, Access returns an error.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

10 comments
neilawoolley
neilawoolley

I've been using this solution with mixed results. I've just identified that I've confused the subform's Source Control with the subform's Name. Here's a description of my confusion so that you can avoid a similar problem. The following code returned a runtime error, even though it conforms to the naming described in the solution earlier: ListItems = me!fManageObjects_Subform.Form.[Object entries].ListCount The problem arises when I take an existing form, make a copy and use it as a subform on another form. For example, the original form is called fManageObjects, I'll name the subform fManageObjects_subform. When changing the subform's properties, I must have inadvertently changed it's Name property, not realising that this is crucial when referring to it from the main form. The subform's control's name is crucial not any other attributes such as it's source control.

Tink!
Tink!

that your explanation is for calling controls on the subform when focus is on the main form. Within the subform itself, the subform controls are able to be called just like main form controls [i]Me!controlonsubform[/i]

stasinos
stasinos

You did an EXCELLENT job on writing this tip! This is the absolute best, most clear and concise explanation of how to reference an Access subform field that I have ever read!!

askolits
askolits

I found a way to reference all the controls in a subform, by clicking in the subform without having to know the subform's control name. Example: I wanted to lock all the controls in a subform when someone moved to a different record within that 'same' subform. Using the OnCurrent event of the subform, I put "UnlockAllControlsOnSubform()", then wrote the follwing Public function: Function UnlockAllControlsOnSubform() Dim frmForm As Form Dim ctrl Set frmForm = Screen.ActiveForm(Screen.ActiveForm.ActiveControl.Name).Form For Each ctrl In frmForm.Controls ctrl.Locked = False Next End Function My main form has 6 subforms in it within a tab group, and I didn't want to write the same function 6 times. So I created one function to handle all of the subforms. Note: Did not test with Access 2007.

PBA IT
PBA IT

I've been designing in Access for 15+ years. I have never had a problem until 2007. Now, none of my Subform references work ... this code example IS NOT what I've been using & it doesn't work either! THANKS CHILDREN of MICROSOFT for coming up with your own personal changes and not referencing most of what's been going on over the last DECADE!

Marshwiggle
Marshwiggle

... My! What tiny print you have! :-O

edward.talerico
edward.talerico

Thank you so much for this post!! I was trying to requery a control on a subform and just couldn't get it to work. Your a life saver!!

peter.weir
peter.weir

This is good, I usually have to spend a few minutes finding out how to reference subforms each time I develop in Access (not that often). The problem I usually hit is referencing subforms within subforms.

Editor's Picks