Referencing a control on a subform from the parent form in Microsoft Access VBA

[ AccessMain ]  This page last updated 2010-09-27.

Sometimes I get confused when trying to figure out the exact syntax required in VBA code to reference a control on a subform from the parent form. Or another form although this isn't usually done.   One example of the syntax required is Forms![Equipment]![EquipmentPartssbf].Form![cboepPartID]  

Although if this is running from the parent form I'll frequently remove the forms name portion Forms![Equipment] and replace it with Me!.  For example Me![EquipmentPartssbf].Form![cboepPartID]  Just in case I rename the form or some such.

Another item to note is that the subform control name is what is required in the syntax  Which can be different than the name of the subform within Access.  In the above example the control name EquipmentPartssbf is actually a form named Equipment - Parts sbf.

If I'm confused and I don't have an example easily available I use the query expression builder trick.

Open the form with the subforms in design view.  Create a new query or open an existing query.  Click on the Expression Builder toolbar button.

Now click on Forms >> Loaded forms. Click on the plus sign beside the form in question to expand the list of subforms.    Choose the subform in question.  Choose the field in question.  Finally click on Paste.  And there's your syntax.   Highlight the text and Ctrl+C and you're done.

It takes longer to describe the process than to actually do it.  (If required click on the image to enlarge it.)

Also note that red text in the lower right hand corner.   That's a visual reminder to me that I'm doing something weird and unexpected.   Not that I always notice it of course.  <smile>

[ Access | Main | Search ]

Auto FE Updater   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Wrench and gear Granite Fleet Manager - the best designed fleet maintenance tracking and management system available

Comments email Tony  Search Contact Tony's Blog Privacy Policy Table of Contents

Website copyright © 1995-2013 Tony Toews