Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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.

Share:

4 comments:

  1. "Public Constant definition from the Classes Window (left panel)"
    I don't see any Public Constant in the Classes window

    ReplyDelete
    Replies
    1. The reference is on built-in objects Forms/Reports.

      Delete
  2. I still don't see them; can you please give an example of a Public Constant in the Classes window?

    ReplyDelete
  3. If you are able to open the Object Browser window, select VBA from the drop-down list. You will find public constant declarations (like Color Constants, KeyCodeConstants, Constants etc. in the left panel, click on one of them to see individual constant declarations.

    You may select other Object like DAO on the top control and explore further.

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code