July 2016 ~ LEARN MS-ACCESS TIPS AND TRICKS

Dots and Exclamation Marks Usage with Objects in VBA3

a.p.r. pillaiJuly 29, 2016Accesstips, How Tos, Object Reference, VBA No comments

The Final Post and Continued from Last Week's Post.

In Microsoft Access, application objects such as Tables, Queries, Forms, Text Boxes, Labels, and others should be given meaningful names when created. If we don’t, Access assigns default names like Form1, Form2, Text1, Label1, and so on. These defaults provide no real indication of what the object represents. Assigning descriptive names related to their purpose makes it much easier to recognize and remember them later—especially when they are used in calculations, VBA code, or other references.

For example, in VBA, we can directly reference a control like this:

Forms!Employees!Salary

instead of the longer form:

Forms("Employees").Controls("Salary").Value

Last week, we began with a simple example that showed how the exclamation mark (!) symbol can shorten object references compared to using multiple dot separators. Once the form name and control name are known, the expression becomes more concise with the '!' symbol.

This shorthand also applies to Recordset fields. For instance:

rset!LastName

is equivalent to:

rset.Fields("LastName").Value

and both return the contents of the field’s default Value property.

If you are a new visitor to this page and topic, then please visit the earlier two pages and continue from here. The links are given below:

Referencing Form's Controls.

I will repeat the first example here, introduced on the first page of this three-page series, to go further in 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: When it frmSubForm is placed as a subform within frmMain It becomes a control on the main form. Like any other control, it has its own set of properties and contains its own controls. If you generate a list of the controls on the main form, the subform will appear in that list just like a textbox, button, or label.

To see this in action:

Open a form that contains a subform.
In the Debug Window, type the following command on a single line (adjusting the form name as needed), then press Enter to display the list of control names of the main form:


For Each ctl In Forms!frmMain.Controls: Debug.Print ctl.Name: Next

This will print the names of all controls on the main form, including the subform control itself.

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

The 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 value from the innermost form in the debug window. Look at the command given below:

? Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text7

The actual address of Text7 Textbox, all elements are joined with the symbol!, except the '.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 do we need it in the address, and what does it mean? It works without an explicit reference because the system knows that it is a Sub-Form control by default.

When you drag and drop a Sub-Form onto 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 its Property Sheet(F4) and check the Source Object Property setting. You can see that the subform's name is inserted there. This is the control where we set the Link Master Fields and Link Child Fields properties to set the relationship between data on the master form and sub-form.

You can rewrite 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 does the '.Form' part in the above address mean? It means that the Sub-Form Control created by Access is a container control for loading a Form into it, and it will always be a form control, whether you explicitly add the '.Form' part in the address or not.

Loading Table or Query in a Sub-Form Control.

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

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

Click on the outer edge of the Sub-Form to select the Sub-Form control.
Display the Property Sheet (F4) and select the Source Object Property.
Click on the drop-down control to the right of the property to display the available forms, Tables, and Queries.
At the top of the list, you will see all Forms. These are followed by the list of Tables, and then the list of Queries. Tables are displayed in the format:
Table.TableName
and queries appear in the format:
Query.QueryName
This notation indicates the category of object that can be assigned to the Source Object property of the subform control.
Select a Table or Query to insert into the Source Object Property of the Sub-Form control.
Save the Form and open it in Form View.

You will find the Table or Query result displayed in the Sub-Form control.

Try to print the value of one of the fields displayed 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 Property Value with a Table's name, the system already adds 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

Dots and Exclamation Marks Usage with Objects in VBA2

a.p.r. pillaiJuly 21, 2016Accesstips 4 comments

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.

Open your Access Database.
Open VBA Editing Window (Alt+F11).
Open Debug Window (Ctrl+G).
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.
Select Access from the <All Libraries> Control's drop-down list.
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.
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.
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.

Scroll down to the bottom of the Classes Window.
Select the TempVars Class Object. You can see its Methods (.Add(), .Remove() & .RemoveAll()) and Properties in the right side window.
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.
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.

Dots and Exclamation Marks Usage with Objects in VBA

a.p.r. pillaiJuly 15, 2016Forms, How Tos, Objects No comments

Introduction.

Beginner VBA learners are often confused about how to correctly reference controls on a Form or on Subforms. The challenge becomes even greater when a Subform contains another Subform. In such cases, specifying the full address of the control on the innermost Subform correctly is essential if you want to read the control’s contents or set its value directly through VBA.

How do we build the complete object address? Should each element be joined with a dot (.), with an exclamation mark (!), or with a combination of both?

To answer these questions, let’s start with some simple examples of using dots and exclamation marks (or bang ! symbol) in object address specifications.

For demonstration purposes, I’ve created a sample Main Form with two Subforms. The second subform is nested inside the first subform. Below, you can see both the Design View and the Form View images for reference.

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

Sample Form in Normal View

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

Dots and Exclamation Symbols.

General Usages of the dot (.) and bang 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 them in the normal view. Open the 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 the 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

There was an expression ="Innermost Form" inside the Text7 Text box, which 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 the opened Form after executing the above statement in the Debug Window.

Let us examine each part of the above command joined by an exclamation mark or a dot. I call this a command because we directly execute this program statement in the Debug Window, with the? (Print VBA command).

The Forms collection contains all forms currently loaded in memory. Each form opened is indexed in the order it was loaded into memory—the first form opened has index 0, the second has index 1, and so on.

If your main form contains subforms, those subforms are treated as controls of the main form, just like combo boxes, labels, or text boxes. They do not appear as separate entries in the Forms collection.

To display the name of the first form loaded in memory, use the following command in the Immediate (Debug) Window:


? Forms(0).Name

This prints the value of the form’s Name property for the form at index 0 in the collection.

? Forms(0).Name

Result: frmMain - Form's Name property value is printed.

? Forms(0).Width

Result: 21225 (You may get a similar number.)
This value represents the width of the form measured in Twips.

📏 Conversion note:

1 Inch = 1440 Twips.

When you set the Width property of a form (whether in Inches, Centimeters, or any other regional measurement unit), VBA automatically converts that value 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 parentheses.

? 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 bang (!) symbol is given in parentheses 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 names of all the properties by typing the following Code, on one line, in the Debug Window and pressing the Enter Key:

For j=0 to Forms(0).Properties.count-1:? j,Forms(0).Properties(j).Name:Next

In the above statement, the Count() Method of the Forms Property is called, takes a Count of the first Form Properties, and prints the Name of each one.

Take a Listing of the Property Names.

For j=0 to Forms("frmMain").Properties.count-1:? j,Forms("frmMain").Properties(j).Name:Next

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. If the frmMain form is not open at the time of executing this statement, then it ends up in 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.