Dots and Exclamation Marks Usage with Objects in VBA2

Continued from Last Week's Topic

Last week, we learned a few examples of using the dot (.) separator and the bang (!) symbol with loaded Form and Report objects in memory. In this article, we’ll continue exploring the topic further.

If you haven’t read the earlier article yet, I recommend visiting that page first before proceeding here: [Dots and Exclamation Marks Usage with Objects in VBA].

After working through the previous examples, you might be a little uncertain about which syntax is easiest to use, since we experimented with different ways of referencing forms and controls in VBA. For now, let’s set that aside and approach things from a different angle.

In this section, we’ll focus specifically on the dot (.) separator as it applies to built-in library objects. Unlike with forms and controls, the bang (!) symbol is not valid for referencing these objects. You’ll also see a visual hierarchy of some of the library objects, along with examples of how to access their properties or call their methods directly from the Debug Window.

Object Library View.

Right-click to open Large Image in New Tab/Window.

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 the Tools Menu.

   • On the Editor Tab, select 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 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 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 the 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 only.

Let us see how many Forms we have in our database by reading the Count Property Value of the 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 the 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 the (!) symbol to address predefined objects, methods, or properties.

When we reference a User-Defined object (it should have a name), we can use the (!) symbol followed by the object/control Name to access that Control's default Property values or other controls/properties, eliminating the lengthy syntax. To make this point very clear, we will try 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 its Methods (.Add(), .Remove() & .RemoveAll()) and Properties in the right side window.

3. Above the TempVars Class, you can see another object named 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.

TempVars.Add "website", "MsAccessTips.com"

We have called the Add() method of the TempVars Collection Object to instantiate the TempVar Class, and assigned 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 a Variant Type, i.e., whatever data type (Integer, Single, Double, Date, String, etc.) you assign 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 the (!) 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 numerical values (like the Exchange Rates), 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 named website is our own creation, and 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 the bang (!) 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 or 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.

Assume that you are now comfortable with the period (.) and bang (!) symbols in object references. We will explore a few more aspects 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