Dots and Exclamation Marks Usage with Objects in VBA2

Continued from Last Week's Topic

Last week we started with a few examples of the usage of dots (.)  and exclamation marks (!) in memory and 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 slightly confused, about which Syntax of the Command is the easiest one to use because we have tried different ones for addressing form & control in VBA.

For the time being, we will leave it there and try a few things differently here so that you will be better informed about the dot separator usage of 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.

Tools -->Options-->Editor-->AutoList Members.

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 the Classes window down, find the item with the name 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 to 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 the CurrentProject Object, from the Access Library of Objects, is displayed.  When you open a database from a particular location the FullName Property value is assigned from the full pathname of the Database.  We have joined the object elements in the 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 we have in our database, by reading 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 the 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 the 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 default Property Value's contents or other controls/properties, eliminating the lengthy syntax. To make this point very clear we will try out one simple example below.

The TempVars Object.

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, which 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 a 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, and so on) to address them with the use of ! symbol followed by the name of the control.

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

You may explore other objects by selecting them in the left-side panel and inspecting their properties, Methods, and Events when you are in doubt about something.

The Data Access Object (DAO).

Tables & Queries are part of the 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 period (.) and (!) symbols in object references. We will look into a few more things in the next session before we conclude the discussion on this topic.

• Working with Chart-Object in VBA
• VBA Module Object and Methods
• Hyperlink Opens Objects from another Database
• Overlaying Sub-Forms in Real-time