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.

Object Library

  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.