Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Dots and Exclamation Marks Usage with Objects in VBA

Introduction.

Beginner VBA enthusiasts often get confused as to how to address control on a Form or on Sub-Forms. It becomes more difficult when there is another 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 do we join each element of the address? Can we join each element of the Object address with a dot (.) or with an exclamation mark (!) or with both of them?

Let us start with the simple usages of dots and exclamation marks in object address 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

Dots and Exclamation Symbols.

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

  • A dot (.) -  after an object name to access its methods or properties.
  • The exclamation mark (!) - after an object name, refers to the sub-object or control of the Top-level Object.

Sample Forms:

Main Form: frmMain

Sub-Form of Main Form: frmSubForm

Inner Sub-Form on frmSubForm:  frmInnerSubForm

Text Control on frmInnerSubForm:  Text7

Another Unbound Text Control on frmInnerSubForm:  Text3

All three forms are designed without linking to any Table (i.e. The Form's Record Source property is empty). All the Text controls on the Form are unbound.

You may design three sample forms with a few unbound text boxes and set them up one inside the other and open it in the 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 in the Debug Window and pressing Enter Key.

Practical Exercises.

First, we will try to read the contents of the Text7 textbox (i.e. 'Innermost Form') from frmInnerSubForm and display the result in 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: ms-access

The Text3 textbox is in the Innermost Form and its Conttrol Source Property is loaded 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 an 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., the first form opened will have the index number 0, the second form is 1, and so on. If your main form has two more forms on it as Sub-Forms they are treated as controls of the Main-form, like any other control (Combobox, labels, textbox, and others). They will not appear as separate forms in the Forms collection. We can display the name of the first open Form in memory with the following command using zero as its index number and printing its 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 in Twips. VBA converts the value you set in the Width Property of the Form, in Inches or Centimeters, or in any other regional value, it will be converted internally 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 the command, the 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 the next level of the 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. The exclamation ! symbol is given in parenthesis for legibility only.

    Note: You cannot use the symbol (!) in place of the 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 the first form Properties, and print the Name of each one.

    Take a Listing of the Property Names.

    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 in the open Forms Collection.  In both cases, the form's name is given immediately after the open Forms collection name. But,  if the frmMain form is not open, at the time of executing this statement, then it will end up with an Error.  Forms(0) refer 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

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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