Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Dots and Exclamation Marks Usage with Objects in VBA3

MS-Access Application Objects (Table, Query, Forms, Text box, Label etc.) needs some meaningful names, when we create them. If we don't, then MS-Access assigns default names, like Form1, Form2, Text1, Label1 and so on. These default names doesn't give any clue as what those names represents. We are free to assign appropriate names in relation to what we are building in the Database. That way it is easy to remember those names (if not all of them), when we need them in calculations,  in VBA or in wherever they are referenced. We need these names to address them easily in VBA, like Forms!Employees!Salary rather than the usage  Forms("Employees").Controls("Salary").Value. 

Last week we have started with a simple example, where we can use the symbol ! , to shorten the lengthy object address when dot separators are used, giving you enough insight into what it is all about. When we have the Form’s name and Control name the expression can be written in short form with the symbol ! .  This is true when recordset fields are referenced, like rset!LastName instead of rset.Fields(0).Value.

If you are new on this page then please visit the earlier two pages and continue from here. The links are given below:

I will repeat the first example here, introduced in the first page of this three page series, to go further on this discussion.

? Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text7
'
'The above command without the use of the ! Symbol, you must write it in the following manner to get the same result.
'
? Forms("frmMain").Controls("frmSubForm").Form.Controls("frmInsideSubForm").Form.Text7.value

Note: The frmSubForm when placed as Sub-Form on the frmMain it becomes a control (with it's own controls and properties) of the Main Form and listed among the Controls list. If we take a list of controls of the Main form we can see that the frmSubForm is listed among them.

Open a Form with a Sub-Form  then type the following command in one line, with changes in the Debug Window and press Enter Key to get a listing of control names of main form

for j=0 to forms!frmMain.controls.Count-1:? j, forms!frmMain.controls(j).name:next
'Result of the above command, on my Form.
'
 0            Label0
 1            Text1
 2            Label2
 3            Text3
 4            Label4
 5            frmSubForm
 6            Label5

frmSubForm is listed as a Control of the frmMain with index number 5

Now, about the example given at the beginning, we have three open Forms: frmMain, frmSubForm & frmInsideSubForm, layered one inside the other. We are trying to print the Text7 Text Box contents, from the innermost form in the debug window.  Look at the above address of  Text7 textbox, all elements are joined with the symbol ! except .Form after the name frmSubForm and frmInsideSubForm. This command will work without the .Form part, try the command given below.

? Forms!frmMain!frmSubForm!frmInsideSubForm!Text7

If the address works without the .form part why we need it in the address and what it means? It works without explicit reference because the system knows that it is a Sub-Form control by default.

When you drag and drop a Sub-Form on to the Main Form Microsoft Access creates a container control on the main form and inserts the Sub-Form into it. To be more specific, if you select the outer edge of the sub-form control you can select this container control. Display it's Property Sheet(F4) check the Source Object Property setting. You can see that the sub-form's name is inserted there. This is the control where we set the Link Master Fields and Link Child Fields properties to set relationship between data on master form and sub-form.

You can re-write this property value with any other form's name  to load another form into it, in real-time. When you do that consider the relationship change, if the new form's source data is not related.

Coming back to the point, i.e. what the .Form part in the above address means? It means that the Sub-Form Control created by Access is a control for loading a Form into it and it will be always a form-control, whether you explicitly add the .Form part in the address or not.

But, the interesting part is that you can insert a Table or a Query (not Action Query) into this control as well.

Try that, if you have a Form with a sub-form, open it in design view.

  1. Click on the outer edge of the Sub-Form to select the Sub-Form control.
  2. Display the Property Sheet (F4) and select the Source Object Property.
  3. Click on the drop-down control to the right of the property to display the available forms, Tables and Queries.

    On top of the list all forms will appear, after that the list of Tables and then the Queries list. All the Tables are listed with Table.TableName format and queries with Query.QueryName format indicating the category of object you can insert into the Source Object Property of the Form control.

  4. Select a Table or Query to insert into the Source Object Property of the Sub-Form control.
  5. Save the Form and open it in Form View.
  6. You will find the Table or Query result is displayed in the Sub-Form control.

  7. Try to print the value of one of the field in display in the debug window.

    Tip: It will print the value of the active record in the sub-form, if selected, otherwise the first record field value.

Is this the command you have typed in the Debug Window?

? Forms!frmMain!frmSubForm.Table!LastName

Then you are wrong, it is not a Table Control, still it is a Form control only. When you set the Source Object Proerty Value with a Table's name the system already added the category name to the object's name (Table.TableName or Query.QueryName) to identify what type of object is loaded into the sub-form control.

So the correct command is:

? Forms!frmMain!frmSubForm.Form!LastName
'
'OR
'
? Forms!frmMain!frmSubForm!LastName
Share:

Dots and Exclamation Marks Usage with Objects in VBA2

Last week we have started with few examples of the usage of dots (.)  and exclamation marks (!) on memory loaded Form/Report Objects.  We will continue exploring this topic further.  If you have not gone through the earlier page,  please visit that page and then continue from here. Earlier article link: Dots and Exclamation Marks usage with Objects in VBA

After going through last week's article, I am sure you are little bit confused, which syntax is the easy one to use because we have tried different syntax for addressing form & control in VBA.

For the time being we will leave that there and try few things differently here so that you will be better informed about the dot separator usage on built-in objects.  The  exclamation (!) symbol is not at all valid for referencing these objects.  You will see the hierarchy set up of some of the library objects visually and how to address them to view their property values or call their methods directly from the debug window. 

Object Library View.

  1. Open your Access Database.
  2. Open VBA Editing Window (Alt+F11).
  3. Open Debug Window (Ctrl+G).
  4. Display Object Browser Window(F2).
    • Select Options from Tools Menu.
    • On the Editor Tab put a check-mark on the Auto List Members item, if it is not already selected.
  5. Select Access from the <All Libraries> Control's drop-down list.
  6. Move the scrollbar of Classes window down, find the item CurrentProject and select it.

    Check the right side window listing of  CurrentProject’s Properties and Methods.

  7. If the Object Browser Window is small then drag the right and bottom edges to make it large enough to display all the Properties and Methods of the CurrentProject Object in the right-side window.
  8. Click and hold on the Title area of the object browser window and drag it to the right, if it overlaps the Debug Window area.

When you select a Class Object or Public Constant definition from the Classes Window (left panel), the selected object members are displayed in the right-side window.

Let us display information stored in some of these objects and how we address those object properties/methods and in which order we specify them?

We will display the full Pathname (.FullName property value) of the active database with the following command, by typing it in the Debug Window and pressing Enter Key:

? Access.CurrentProject.FullName
'Result: G:\New folder\pwtracker.accdb

The .FullName Property Value of CurrentProject Object from Access Library of Objects is displayed.  When you open a database from a particular location the .FullName Property value, the full pathname of the Database,  is set by MS-Access System.  We have joined the object elements in correct sequence separated by dots to specify the .FullName property at the end. The .Name property value will display the database name part only.

Let us see how many forms you have in your database, by taking the .Count Property Value of AllForms Object.

? Access.CurrentProject.AllForms.Count
'Result: 75 – There are 75 user created Forms in my active database.
'
? Access.CurrentProject.AllForms.item(50).Name
'Result: frmEmployees 
'
'The 50th index numbered item (i.e. 51st item)in my database is frmEmployees. 
'This Form is not opened in memory, but it is taken from the Database’s .AllForms Collection. 
'
? Access.CurrentProject.BaseConnectionString
'
'Result: PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=G:\New folder\pwtracker.accdb;PERSIST SECURITY INFO=FALSE;Jet OLEDB:System database=G:\mdbs\BACAUDIT_1.MDW
'

In the above examples we have displayed CurrentProject Object's few Property Values assigned by the System. We have used Forms Collection Object to address the open forms in memory, in last week's examples.

Note: You may explore some of the other objects yourself.

From the above few examples you can see that we have used dot separator character only to join each object/property. You cannot use ! symbol to address predefined objects, methods or properties.

When we reference a User-Defined object (it should have a name) we can use ! symbol followed by the object/control Name to access that Control's Value or other controls/properties, eliminating the lengthy syntax. To make this point very clear we will try out one simple example below.

  1. Scroll down to the bottom of the Classes Window.
  2. Select the TempVars Class Object. You can see it's Methods (.Add(), .Remove() & .RemoveAll()) and Properties in the right side window.
  3. Above the TempVars Class you can see another object name TempVar Class, click on that and check the right-side window. You will find only two properties:Name & Value.
  4. Type the next line of Code in the Debug Window and press Enter Key.
TempVars.Add "website", "MsAccessTips.com"

We have called the .Add() method of the TempVars Collection Object to instantiate the TempVar Class, to assign the Name property with the text: website and the Value property with the text: MsAccessTips.com. The new Tempvar Variable website is added to the TempVars Collection with index number 0, because this is the first TempVar variable we have defined in memory so far.

The TempVar data type is Variant Type, i.e. whatever data type (Integer, Single, Double, Date, String etc.) you assigned to it, it will automatically adjust to that data type.

We can display the website variable contents in two different ways.  First method using dot separators, second with ! symbol.

'1st method
? TempVars.item(0).value
'Result: MsAccessTips.com
'OR
? TempVars.item("website").value
'Result: MsAccessTips.com
'
'OR
X="website"
? TempVars.item(X).value
'Result: MsAccessTips.com
'

'2nd method
'the above lengthy syntax can be shortened
'with ! symbol and the user-defined name:website
'point to note:after ! symbol Name property value should follow.
? TempVars!website
'Result: MsAccessTips.com
'
'next command removes the website variable from memory.
TempVars.Remove "website"
'
'the .RemoveAll method clears all the user-defined Temporary Variables from memory.
'TempVars.RemoveAll

The TempVars Variables are Global in nature, that means you can call this variable (Tempvars!website) in Queries, Textbox (=TempVars!website) on Forms or on Reports and in expressions like: ="Website Name is: " & TempVars!website. If the Value property is assigned with numerical values (like Exchange Rates or some common factor) it can be used in Calculations.

Tip: Try defining few more TempVar variables assigning different data types: Integer, Double, Date etc. with appropriate Name Property Values.

The Tempvar variable with the name website is our own creation and it is in memory.  Objects (Form/Report) should be loaded into memory with their controls (Textbox, Combobox, Labels, Sub-Form/Sub-Report etc,) to address them with the use of ! symbol followed by the name of the control.

We have used the keyword .item in the first three examples.  This is used immediately after the TempVars Collection Object.  When we address textboxes, labels and other controls on an open Form/Report we must use the key word .Controls.

You may explore other objects by selecting it in the left-side panel and inspecting their properties, methods and events etc. now or later when you are in doubt on something.

Tables & Queries are part of DAO Library. You may select DAO in the top control replacing Access and explore DAO related Classes, their Properties and Methods.

Hope you are now comfortable with the usage of (.) and (!) symbols in object references. We will look into few more things in the next session before we conclude the discussion on this topic.

Share:

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:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation 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 DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter 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 Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users 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

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts