Ms-Access Advanced VBA Programming Techniques, Tips and Tricks.

Ads

Dots and Exclamation Marks Usage with Objects in VBA

Beginner VBA enthusiasts often get confused as how to address a control on a Form or on Sub-Forms. It becomes more difficult when there is an inner-form within a Sub-Form. Specifying the address of the control on the inner sub-form correctly will only help to access the Text control's contents or to set the control's value directly through VBA. How, to join each element of the address? Join each element with dot (.) or is it exclamation mark (!) or both?

Let us start with the simple usages of dots and exclamation marks in object specifications.

I have designed a sample Main Form and two Sub-Forms. The second sub-form is inside the first Sub-Form. The Forms design view and normal view images are given below for reference.

Sample Form in Normal View

General Usages of dot (.) and exclamation symbol (!) in object references.

  • dot (.) - used after an object name to access it's methods or properties.
  • exclation mark (!) - used after an object name to refer to the sub-object/control of the Top-level Object.

Sample Forms:

  1. Main Form:
  2. frmMain
  3. Sub-Form of Main Form:
  4. frmSubForm
  5. Inner Sub-Form on frmSubForm:
  6. frmInnerSubForm
  7. Text Control on frmInnerSubForm:
  8. Text7
  9. Another Unbound Text Control on frmInnerSubForm:
  10. Text3

All the three forms are designed without linking to any Table (all form's Record Source Property is empty). All the Text controls on the Form are unbound.

You may design three sample forms with few unbound text boxes and set them up one inside the other and open it in normal view. Open VBA editing Window and open the Debug Window (Ctrl+G). Now you are ready to try out the following examples after typing the code directly on the Debug Window and pressing Enter Key.

First, we will try to read the contents of Text7 textbox (i.e. 'Innermost Form') from frmInnerSubForm and display the result on the Debug Window.

? Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text7

Printed Result is: Innermost Form

I have written an expression ="Innermost Form" inside the Text7  Text box, that is displayed in the Debug Window.

Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text3 = "ms-access"

Result: Text3 textbox of the Innermost Form is assigned with the text: ms-access.

Check on your opened Form after executing the above statement on the Debug Window.

Let us examine each part of the above command joined by exclamation mark or dot.   I call this a command because we directly execute this program statement in the Debug Window, with the ? (Print VBA command).

  • Forms : Collection of all Forms loaded in memory. All Forms opened in memory are indexed in the order in which they are loaded into memory, i.e. first form opened will have the index number 0, second form is 1 and so on. If your main form have two more forms on it as Sub-Forms they are treated as controls of the Main-form, like any other control (combobox, labels, textbox etc.). They will not appear as a separate form in the Forms collection. We can display the name of the first open form in memory with the following command using zero as it's index number and printing it's Name Property Value.
     ? Forms(0).Name

    Result: frmMain - Form's Name property value is printed.

    ? Forms(0).Width

    Result: 21225 (you will get a number similar to the one given to the left) - the width of the form given in the measure of Twips. 1 Inch = 1440 twips. VBA converts the value you set in the Width Property of the Form in Inches or Centimeters or any other regional value it will be converted into twips.

    If you know the Form's name then the above command can be given as below:

    ? Forms("frmMain").Width 

    In this form of command Form's name can be given as a string in parenthesis.

    OR

    ? Forms!frmMain.Width

    In this command, immediately after the Forms Collection name we have used the ! symbol to give the name of next level of object of the Forms collection object, not a property or method. The next item Width is a property of the frmMain object, so a dot is required, not the other symbol.

    Note: You cannot use the ! symbol in place of dot (.) to address the Width or Name or any other property of the Form. There are over 200 properties for a form. You can display the Name of all the properties by typing the following Code, on one line, on the Debug Window and pressing Enter Key:

    For j=0 to Forms(0).Properties.count-1:? j,Forms(0).Properties(j).Name:Next

    In the above statement we have called the Count() Method of the Forms Property to take a Count of properties of the first form open in memory and print the Name of each Property, one by one.

    OR

    For j=0 to Forms("frmMain").Properties.count-1:? j,Forms("frmMain").Properties(j).Name:Next

    OR

    For j=0 to Forms!frmMain.Properties.count-1:? j,Forms!frmMain.Properties(j).Name:Next

    Note the usage of Forms("frmMain") and !frmMain, two different ways to refer to the frmMain object of the open Forms Collection Object.  In both cases form's name is given immediately after the open Forms collection name. But,  frmMain form is not open at the time of executing this statement it will end up with Error.  Forms(0) refers to any form that is open first in memory. It will fail only when no form is open at the time of executing this statement.

    ? Forms.Count

    The Count() method of the open Forms Collection Object will print the count of open Forms

We will explore this topic further in the next Post.

Share:

No comments:

Post a Comment

Trending

Infolinks Text Ads

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports Animations msaccess animation msaccess forms Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards ms-access functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security DOS Commands Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Array Auto-Number Command Button Custom Functions Join Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess functions msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Copy Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Rich Text Sequence SetFocus Summary Tab-Page Time Difference Union Query User Users Variables Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Activity Dates and Quarterly Reports

There are four Quarters in a Year: Jan - Mar = 1st Quarter Apr - Jun = 2nd Jul - Sep = 3rd Oct - Dec = 4th First three months of the yea...

Labels

Blog Archive

Recent Posts