Access Objects, Properties and Methods

Examples of Access's objects, properties and methods

Access database is made up of different kinds of Objects, such as tables, queries, forms, reports, modules, macros, data access pages, as well as components of those objects (such as Fields and Index objects in a Tables object). Another example is like the controls (list box, combo box, textbox, etc) of a Form object. Some objects are used to display the data, while others are used to store and manage the data itself, or to assist you in Visual Basic programming.

The things that an object know what to do are called Methods. An object can have 3 types of Properties - read/write, read-only, and write-only. Most properties can be set at design time and modified at runtime; but however, there are some which can't be modified at runtime, and others can't be accessed at design time. How can you determine this? To see this clearly, select an object and view its property sheet [see example here]. One quick way to understand all the properties and methods associated with a particular object is to invoke Help or press F1. In the Help topics dialog box, type the object name or simply click to select the particular object [see example]. Another way is go to the Object Browser, click the particular object in the classes and you will see all its associated members (properties, methods ands) in the right pane [see example].

On top of Access Object Model is the Application object, which refers to the active Access application, and it contains Access's other objects and collections. The Reference object refers to a reference set to another application's or project's type library. The Modules object gives you control over the VBA modules in your database. The CurrentProject object lets you access and manipulate the application components of your database, whereas the CurrentData object let you reference the data elements of the underlying database engine. The DoCmd object provide you access to all actions in Access, and it consists only of methods. The Forms object refers to a particular Access form, each Forms object has a Controls collection, which contains all controls on the form. A Report object refers to a particular Microsoft Access report. The Screen object to refer to the active object on the screen.

Figure 1.0 Access Object Model [Access 2003]

Application

CurrentData

AllDatabaseDiagrams―AccessObject

AllFunctions―AccessObject

AllQueries―AccessObject

AllStoredProcedures―AccessObject

AllTables―AccessObject

AllViews―AccessObject

CurrentProject

AccessObjectProperties―AccessObjectProperty

AllDataAccessPages―AccessObject

AllForms―AccessObject

AllMacros―AccessObject

AllModules―AccessObject

AllReports―AccessObject

DoCmd

Forms

Form

Control

Controls

Module

Properties

Section

Modules

Module

References

Reports

Report

Control

Controls

GroupLevel

Module

Properties

Section

Screen

Control

Form

Pages

Properties

Report

The following provides the description of the objects.

Objects	Description
Application	The active Access application.
Reference	Reference to an application’s object library.
CurrentData	The objects stored in the current database by the source application (Jet or SQL).
CurrentProject	The project for the current Access project (.adp) or Access database (.mdb).
DoCmd	Macro actions used in VBA code.
Forms	Open form, including subforms.
Modules	Open standard module or class module.
Reports	Open report, including subreports.
Screen	Screen that currently has the focus.

Create VBA objects in code

Although you can create some objects through Access's user interface, but most VBA objects can only be created in code. The following code creates an instance of the Orders form in the Northwind database, and display it with a message. See diagram. By default, new forms are hidden when they are created. The Visible property is set to True, so that the form is visible onscreen. After you click OK, the form will be hidden.

Sub CreateObject()
' declare the object variable 'frm', of the Form_Orders type.
Dim frm As Form_Orders
' to instantiate the class (i.e. to create a new instance of the Form_Orders class, and use the variable frm to refer to that object).
Set frm = New Form_Orders
frm.Visible = True
MsgBox "Click OK"
End Sub

' or alternatively, you can write with just one line: Dim variablename as New classname which both declares and
instantiate the variable.

Dim frm As New Form_Orders

Note:
The class itself is not an object. Making an object from a class is called instantiating the class, and one class can instantiate many objects. Also, when you declare and instantiate an object variable in one statement, the object is not created until the first time that you use that object variable in code.

Setting Values to Properties

A property is an attribute of an object. An object can have none or more than one properties. Some objects share the same properties but at other times, an object's properties are specific to that particular object only. When instantiating an object from a class, each of these properties has a value. You can use VBA code to read or assign new values to these properties. The example below turns on the form's Visible property and RecordSelectors property. You can either print, display or assign the values of the properties to variables.

Dim frm As New Form_Orders
frm.Visible = True
frm.RecordSelectors = True

Invoking Methods

Methods are actions that an object takes on itself. A method is specific to the object it applies to. For example, a form has a Repaint method (completes any pending screen updates/recalculations for a specified form) that does not apply to a combo box. Below is an example of SetFocus method that applies to combo box.

Private Sub CmdButton_Click()
' Check to see if a value is entered into the CustomerID combo box.
If IsNull(Me.cboCustomerID) Or Me.cboCustomerID = "" Then
   MsgBox "You must enter a Customer ID", vbOKOnly, "Required Data"
   Me.cboCustomerID.SetFocus
   Exit Sub
End If
End Sub

Understanding Collections

Collections are sets of objects of the same type; they contain all their respective objects in the entire database. Think of object as a member of a collection. For example, frmOrders is a form that is a member of the Forms collection; cmdPrintInvoice, a command button on frmOrders, is a member of the Controls collection of the form frmOrders. Do not confuse Forms collection with the list of all the forms in your database; that list belongs to part of the CurrentProject object.

Below is an example that has each iteration of the loop, retrieve the name of the currently open objects in the Forms collection.

Sub ListObjects1()
' to instantiate by setting object variable to each member of the Forms collection.
Dim frmOrders As New Form_Orders
Dim frmProducts As New Form_Products
' to open the forms.
frmOrders.Visible = True
frmProducts.Visible = True

' iterate through the Forms collection to list out the name of the forms that are currently open.
Dim frm As Form
For Each frm In Forms
Debug.Print frm.Name
Next frm
End Sub

This second example illustrates the two methods of looping through other members of the collections, then print every control name of the open forms, name of all the open Modules and the open Reports.

Sub ListObjects2()
' declare the object variables.
Dim ctl As Control, frm As Form, rpt As Report, mdl As Modules
Dim i As Integer
Set mdl = Application.Modules
For Each frm In Forms
Debug.Print frm.Name
For Each ctl In frm.Controls
Debug.Print " " & ctl.Name
Next ctl
Next frm
Debug.Print "Reports"

For Each rpt In Reports
Debug.Print " " & rpt.Name
Next i
Debug.Print "Modules"

For i = 0 To mdl.Count - 1
Debug.Print " " & mdl(i).Name
Next i
End Sub

CurrentProject object

It returns a reference to the current database. It contains properties such as Name, Path, and Connection. It also contains collections (AllForms, AllMacros, AllModules, AllReports, and AllDataAccessPages). These collections are used to iterate through all the classes of objects in the database. They differs from Forms, Macros, Modules, etc, in that they refer to all objects stored in the current project, rather than just to open them. For instance, the AllForms collection collection contains all the saved forms that make up your database; whereas the Forms collection comprises only the forms currently running in memory or open. These collections contain AccessObject objects as shown in figure 1.0. For example, the AllForms collection contains one AccessObject for each form in the current database.

Example: to check whether a form exists or not in the current database. To evaluate the function, for example, enter this in the immediate window: ?DoesFormExist ("Orders")

Function DoesFormExist(frmName As String) As Boolean
   On Error GoTo HandleErr    ' if attempt fails, it goes to error-handler.
   Dim acObj As AccessObject
   Set acObj = CurrentProject.AllForms(frmName)
   DoesFormExist = True   ' if the form exists, VBA returns True.
ExitHere:
   Exit Function
HandleErr:
   DoesFormExist = False   ' if the form doesn't exists, VBA returns True.
   Exit Function
End Function

Example: to list out all the forms and macros that make up your database.

Sub CurrentProjectObject()
With CurrentProject
   ' print out name and path of the current project.
   Debug.Print .Name
   Debug.Print .Path

   ' loop through every form and macro in the current project, and print out their names.
   Dim f As Variant, m As Variant
   For Each f In .AllForms
      Debug.Print " " & f.Name
   Next f

   For Each m In .AllMacros
      Debug.Print " " & m.Name
   Next m
End With
End Sub