locked
Syntax for referencing objects in Access 2010 RRS feed

  • Question

  • What's the syntax for referencing objects on other forms?  I've looked a lot in Access Help and online, but haven't found it.  The general syntax is apparently:

      [Forms]![form name]![control name]

    That makes sense, but where do I find an Object Browser showing the hierarchy of objects?  The Property Sheet shows a list of objects on one level, but it doesn't reveal the hierarchy.

    I've experimented a lot, but haven't found an object reference that works.  I can see there's a "Navigation Form" and a "NavigationSubform", plus the controls on each page including Form and Detail sections.  In clicking around on the visual objects, it's not apparent which controls are contained within which other controls.

    I see an example:

      Forms!NavigationForm.frmCalendar.Form!cboDepartment

    But I have no idea if this is correct.  If it is correct, when do you use exclamation points, and when do you use dots/periods?

    My immediate goal is to use the use the GoToControl command where the specified control is somewhere else in the hierarchy.  In all the examples I see, the specified control is on the same level, so it's only necessary to provide the control name without any hierarchy.

    Also, somewhat unrelated, but the syntax for naming macros is apparently like this example:

      Navigation Form : NavigationSubform : OnEnter : Embedded Macro

    This uses colons instead.  Is the hierarchy for finding macros the same as the hierarchy for finding controls, and the only difference is the use of colons rather than exclamation points or dots/periods?

    Thanks for the help.

    Sunday, April 24, 2011 6:21 AM

Answers

  • >[Forms]![form name]![control name]

    Actually, the above is correct but what is often a source of confusing is you have two choices in a form on how to reference controls.

     

    You are free to use "dot (.) " or "bang  (!)"

     

    In fact, when writing VBA code, in most cases you better off to use dot notation. With dot you get compile time checking and resolution of the object in question. It also a bit faster, but then again no one going to be running enough code to ever notice the difference anyway.

     

    So, while both of the following will work:

     

    MsgBox me!LastName

    Or

    MsgBox me.LastName

     

    In the above if lastName does not exist, using ! (bang) will not error out until the code is run.  

     

    So not only does dot fail before you run but using dot notation gives you auto complete (inteli-sense) while entering the VBA code.

     

    As noted there is some long drawn out complex reasons as to why dot works in a form, but suffice to say that fields and controls become properties of the form.

     

    However, in other parts of Access, dot for the most part references a property of the object in question, not a collection.

     

    So, if you drop a text box on a form, a property of that text box control is .Value. And, if you drop a sub-form control then one property to reference the form it points to is .Form (so you MUST use .Form to reference the form property of the sub-form object, ! (bang) will not work).

     

    So:

    Forms!NavigationForm!frmCalendar.Form!cboDepartment

    Or

    Forms!NavigationForm.frmCalendar.Form!cboDepartment

     

    Will work. Note how I changed the second ! to a dot, but the .Form MUST remain as "dot" since .form is a property of the control called frmCalendar. And I going to stress and re-stress that frmCAlendar is a no a form object, but a control on the current form of type sub-form control.

     

     

    >where do I find an Object Browser showing the hierarchy of objects? The Property Sheet shows a list of objects on one level, but it doesn't reveal the hierarchy.

     

    Well, a2010 has changed this, and if you editing a query, or in the property sheet of a control or even edting and typing in the control source on a form, then the hierarchy of objects will show if you use bang.  So, for these types of expressions, you might as well use ! since you do get inteli-sense now.

     

    However, in the VBA editor, you will not see this intel-sense for the hierarchy.  

     

    ·         >I can see there's a "Navigation Form" and a "NavigationSubform",

    CCareful here. The above Navigation Form is just a form name, and it could be named anything you want.  So, when you create a navigation form, the default name is Navigation Form. What is MORE important in the above is that Navigation Form is the name of a form. However, NavigationSubform IS NOT THE NAME of a form BUT ONLY the name of a control on the form Navigation Form.

    As I stated there no such thing as a sub-form, but only a sub form control. When you want a combo box on a form, you place a combo box control on the form. When you want a text box on a form, you place a text box control on a form. And when you want to display some sub form, then you place a sub-form CONTROL on the form.

    This sub-form control does NOT need to have the same as the underlying form it going to reference and point to and display as a sub-form.

    Now, with above in mind we can now look at:

    >Forms!NavigationForm.frmCalendar.Form!cboDepartment

    In the above the main form = NavigationForm

    frmCalendar is ONLY the name of a sub control on the NavigationForm. What form name that frmCAlendar will display is not known. That control frmCalendar may or may not be displaying a form called frmCalendar.   

      

    Forms!NavigationForm.Hello.Form!cboDepartment

    Forms!NavigationForm.ThisIsSilly.Form!cobDepartment

     

    However, as noted, since the DEFAULT sub form name (which you can change if you want) is normally called NavigationSubform then I would have assumed for the sake of sanity and not confusing everyone here, it would have been written as:

     

    Forms!NavigationForm. NavigationSubform.Form!cobDepartment

     

    Now, I suppose it is possible that a control frmCalendar was placed on the navigation form IN ADDITION to the standard defaulted control name of NavagationSubForm.

     

    So just keep in mind that the new "navigation" system we have in Access 2010 actually works really much how any sub form worked in previous editions.

     

    In fact, in the past it was quite common to setup one sub-form on a form, and then change what form it will display. So this is what occurs when you click on a different button in the new navigation form system for a2010 – it not hard to recreate this effect in previous versions.

     

    I spend some time on this since I wanted you to keep in mind that the name of the sub-form control has nothing to do with the actual name of the form that the sub-form control will display and render inside of an existing form (they are often the same, but often not). So in the case of a Navigation form, the sub form to display and show all the different forms is called navagationsubform.

     

    Here is another reference to dot vs bang:

     

    http://blogs.office.com/b/microsoft-access/archive/2008/05/30/dot-or-bang.aspx

     

    ·         > Also, somewhat unrelated, but the syntax for naming macros is apparently like this example:

    Navigation Form : NavigationSubform : OnEnter : Embedded Macro

     

    Nope, no real change here. The ":" must have been in some document or some such, but a forms reference still occurs in a macro with ! for the most part.

     

    Albert D. Kallal  (Access MVP)

    Edmonton, Alberta Canada

    kallal@msn.com

    • Marked as answer by JM6 Monday, April 25, 2011 11:01 PM
    Sunday, April 24, 2011 3:24 PM

All replies

  • >[Forms]![form name]![control name]

    Actually, the above is correct but what is often a source of confusing is you have two choices in a form on how to reference controls.

     

    You are free to use "dot (.) " or "bang  (!)"

     

    In fact, when writing VBA code, in most cases you better off to use dot notation. With dot you get compile time checking and resolution of the object in question. It also a bit faster, but then again no one going to be running enough code to ever notice the difference anyway.

     

    So, while both of the following will work:

     

    MsgBox me!LastName

    Or

    MsgBox me.LastName

     

    In the above if lastName does not exist, using ! (bang) will not error out until the code is run.  

     

    So not only does dot fail before you run but using dot notation gives you auto complete (inteli-sense) while entering the VBA code.

     

    As noted there is some long drawn out complex reasons as to why dot works in a form, but suffice to say that fields and controls become properties of the form.

     

    However, in other parts of Access, dot for the most part references a property of the object in question, not a collection.

     

    So, if you drop a text box on a form, a property of that text box control is .Value. And, if you drop a sub-form control then one property to reference the form it points to is .Form (so you MUST use .Form to reference the form property of the sub-form object, ! (bang) will not work).

     

    So:

    Forms!NavigationForm!frmCalendar.Form!cboDepartment

    Or

    Forms!NavigationForm.frmCalendar.Form!cboDepartment

     

    Will work. Note how I changed the second ! to a dot, but the .Form MUST remain as "dot" since .form is a property of the control called frmCalendar. And I going to stress and re-stress that frmCAlendar is a no a form object, but a control on the current form of type sub-form control.

     

     

    >where do I find an Object Browser showing the hierarchy of objects? The Property Sheet shows a list of objects on one level, but it doesn't reveal the hierarchy.

     

    Well, a2010 has changed this, and if you editing a query, or in the property sheet of a control or even edting and typing in the control source on a form, then the hierarchy of objects will show if you use bang.  So, for these types of expressions, you might as well use ! since you do get inteli-sense now.

     

    However, in the VBA editor, you will not see this intel-sense for the hierarchy.  

     

    ·         >I can see there's a "Navigation Form" and a "NavigationSubform",

    CCareful here. The above Navigation Form is just a form name, and it could be named anything you want.  So, when you create a navigation form, the default name is Navigation Form. What is MORE important in the above is that Navigation Form is the name of a form. However, NavigationSubform IS NOT THE NAME of a form BUT ONLY the name of a control on the form Navigation Form.

    As I stated there no such thing as a sub-form, but only a sub form control. When you want a combo box on a form, you place a combo box control on the form. When you want a text box on a form, you place a text box control on a form. And when you want to display some sub form, then you place a sub-form CONTROL on the form.

    This sub-form control does NOT need to have the same as the underlying form it going to reference and point to and display as a sub-form.

    Now, with above in mind we can now look at:

    >Forms!NavigationForm.frmCalendar.Form!cboDepartment

    In the above the main form = NavigationForm

    frmCalendar is ONLY the name of a sub control on the NavigationForm. What form name that frmCAlendar will display is not known. That control frmCalendar may or may not be displaying a form called frmCalendar.   

      

    Forms!NavigationForm.Hello.Form!cboDepartment

    Forms!NavigationForm.ThisIsSilly.Form!cobDepartment

     

    However, as noted, since the DEFAULT sub form name (which you can change if you want) is normally called NavigationSubform then I would have assumed for the sake of sanity and not confusing everyone here, it would have been written as:

     

    Forms!NavigationForm. NavigationSubform.Form!cobDepartment

     

    Now, I suppose it is possible that a control frmCalendar was placed on the navigation form IN ADDITION to the standard defaulted control name of NavagationSubForm.

     

    So just keep in mind that the new "navigation" system we have in Access 2010 actually works really much how any sub form worked in previous editions.

     

    In fact, in the past it was quite common to setup one sub-form on a form, and then change what form it will display. So this is what occurs when you click on a different button in the new navigation form system for a2010 – it not hard to recreate this effect in previous versions.

     

    I spend some time on this since I wanted you to keep in mind that the name of the sub-form control has nothing to do with the actual name of the form that the sub-form control will display and render inside of an existing form (they are often the same, but often not). So in the case of a Navigation form, the sub form to display and show all the different forms is called navagationsubform.

     

    Here is another reference to dot vs bang:

     

    http://blogs.office.com/b/microsoft-access/archive/2008/05/30/dot-or-bang.aspx

     

    ·         > Also, somewhat unrelated, but the syntax for naming macros is apparently like this example:

    Navigation Form : NavigationSubform : OnEnter : Embedded Macro

     

    Nope, no real change here. The ":" must have been in some document or some such, but a forms reference still occurs in a macro with ! for the most part.

     

    Albert D. Kallal  (Access MVP)

    Edmonton, Alberta Canada

    kallal@msn.com

    • Marked as answer by JM6 Monday, April 25, 2011 11:01 PM
    Sunday, April 24, 2011 3:24 PM
  • Thank you, all of that information is extremely helpful.  I'm especially glad to see that when entering a reference to a control, bangs make the Intellisense display the next level of objects.  I'll probably use the bangs to initially enter the reference, and then maybe switch them to dots to get the compile-time checking.  But maybe the checking isn't needed, since with the Intellisense, it's sure to be correct already.
    Monday, April 25, 2011 11:01 PM