C H A P T E R    3 Microsoft Office 97/Visual Basic Programmer's Guide

Microsoft Access Objects

Contents

A Microsoft Access database is made up of different types of objects. Some types are used to display the data in your database, while others are used to store and manage the data itself, or to assist you in programming in Visual Basic. You can use Visual Basic to create, control, and manage all of the different types of objects in a Microsoft Access database.

Some of the objects that are available to you from Visual Basic in Microsoft Access are supplied by Microsoft Access; others are provided by different components. The objects provided by Microsoft Access represent the forms, reports, controls, and modules in your application. This chapter explains how to program with Microsoft Access objects in Visual Basic.

Objects Available in Microsoft Access

When you program in Visual Basic, you work with objects that correspond to different parts of your Microsoft Access database. Collections are sets of objects of the same type. Programming with objects and collections gives you added flexibility in that you can design your Microsoft Access application to respond to user actions and input in a customized way.

Microsoft Access includes several components, each of which supplies its own set of objects. The component's object library contains information about the component's objects and their properties and methods. A component's objects are available to Microsoft Access only if a reference exists to the component's object library. A reference notifies Microsoft Access that the objects in a particular object library are available from Visual Basic. To view existing references, open a module and click References on the Tools menu. To set a reference, select the check box next to the object library you want to reference.

Microsoft Access automatically sets references to the following object libraries:

  • The Microsoft Access 8.0 object library. This object library provides objects that you use to display your data, contain your code, and work with the Microsoft Access application. For example, the Form, Module, and Application objects are provided by the Microsoft Access 8.0 object library. These objects are discussed in this chapter.

  • The Microsoft DAO 3.5 object library. This object library provides Data Access Objects (DAO), such as the TableDef and QueryDef objects, which determine the structure of your database and which you can use to manipulate data in Visual Basic. These objects are discussed in Chapter 11, "Data Access Objects."

  • The Visual Basic for Applications object library. Visual Basic provides three objects that give you more flexibility in programming: the Debug, Err, and Collection objects. For more information on these objects, search Microsoft Access Help for the name of the object.

Microsoft Access also includes the Microsoft Office 8.0 object library. However, Microsoft Access doesn't automatically set a reference to the Microsoft Office 8.0 object library. If you want to work with objects provided by Microsoft Office, such as the CommandBar, FileSearch, and Assistant objects, from within Microsoft Access, you must first set a reference to the Microsoft Office 8.0 object library. In other Office applications, this reference is set automatically. The objects provided by the Microsoft Office 8.0 object library are discussed in Chapter 8, "Menus and Toolbars," and Chapter 9, "Microsoft Office Assistant."

You can also set references to object libraries supplied by other applications or components when you want to use objects in those libraries for Automation. For example, if you want to perform Automation operations with Microsoft Excel objects from Microsoft Access, you can set a reference to the Microsoft Excel object library.

If you want to work with Microsoft Access objects from another application that supports Automation, set a reference to the Microsoft Access 8.0 object library from that application. You can then work with the objects in the Microsoft Access object hierarchy from within that application. For more information, see "Using the Application Object for Automation Operations" later in this chapter.

The Microsoft Access Objects

The following table describes the objects and collections provided by the Microsoft Access 8.0 object library. Each of these objects and collections is discussed in more detail later in this chapter.

Object or collection Description
Application object Represents the Microsoft Access application.
Form object Represents an open form.
Forms collection Contains all currently open forms.
Report object Represents an open report.
Reports collection Contains all currently open reports.
Control object Represents a control on a form, report, or section, or within another control.
Controls collection Contains all controls on a form or report.
Module object Represents a standard module or a class module.
Modules collection Contains all currently open modules.
Reference object Represents a reference to an object library.
References collection Contains all references that are currently set.
DoCmd object Runs a macro action in Visual Basic.
Screen object Represents the current arrangement of objects on the screen.

Microsoft Access objects are organized in a hierarchical relationship. Objects contain collections, and collections contain other objects. The following illustration shows the hierarchy of Microsoft Access objects.

Each Microsoft Access object has properties, methods, and events associated with it. You can view these properties, methods, and events in the Object Browser. To open the Object Browser, open a module, and then click Object Browser on the View menu. You can also open the Object Browser by pressing F2 when a module is open.

The Application Object

The Application object represents the Microsoft Access application and is the top­level object in the Microsoft Access object hierarchy. It contains all the other Microsoft Access objects and collections. It's also the object you must first reference in order to use Microsoft Access objects through Automation.

The Application object is the default object in the object hierarchy. When you're working within Microsoft Access, you don't need to explicitly refer to the Application object when you use one of its methods or properties, or when you refer to an object or collection that the Application object contains. The only time you need to explicitly refer to the Application object is when you're working with Microsoft Access objects from another application through Automation. However, you can refer to the Application object explicitly from within Microsoft Access if you want to.

Using the Application Object for Automation Operations

If you want to work with Microsoft Access objects from another application that supports Automation, such as Microsoft Excel or Microsoft Visual Basic, you should begin by setting a reference to the Microsoft Access 8.0 object library from that application. Once you've set a reference to the Microsoft Access object library, you can work with the Microsoft Access objects, beginning with the Application object. The Application object is the top­level object in the Microsoft Access object hierarchy, so you must first refer to it in code before you can work with the other objects in the object hierarchy.

To work with Microsoft Access objects from another application, you must perform the following steps from within that application:

  1. Set a reference to the Microsoft Access object library from the application in which you are working.

  2. Declare an object variable to represent the Microsoft Access Application object.

  3. Return a reference to the Application object and assign that reference to the object variable.

To set a reference to the Microsoft Access object library, open a module, click References on the Tools menu, and then select the Microsoft Access 8.0 Object Library check box in the Available References box.

After you've set a reference to the Microsoft Access object library, you can declare a variable of type Application to represent the Microsoft Access Application object. Because other applications have their own Application objects, you must qualify an object variable of type Application when you declare it so that Visual Basic creates the Microsoft Access Application object.

You qualify an object variable with the Visual Basic name of the object library that supplies it. Once you've set a reference to an object library, its name is available in the Project/Library box in the Object Browser. The Visual Basic name of the Microsoft Access object library is Access. The following example declares a variable to represent the Application object. 

Dim appAccess As Access.Application

You can also declare an object variable to represent the Application object as type Object. However, your code will run faster if you declare the object variable as type Application.

Note   Whenever you're working with multiple components through Automation, it's a good idea to qualify objects with the name of the object library that supplied them. If you qualify all objects, you can always be sure that you're referring to the correct object.

After you've declared an object variable to represent the Application object, you must return a reference to the Application object and assign that reference to the object variable. You can return a reference to the Application object by using either the CreateObject or the GetObject function, and you can assign that reference to the object variable with the Set statement. Use the CreateObject function to open Microsoft Access and return a reference to the Application object if Microsoft Access is not already running. Use the GetObject function to return a reference to the Application object when Microsoft Access is already running. The following example uses the CreateObject function to open Microsoft Access 97 and return a reference to the Application object, and then assigns it to an object variable of type Application. 

Dim appAccess As Access.Application
Set appAccess = CreateObject("Access.Application.8")

Note   If your code may run on a computer that has more than one version of Microsoft Access, you can include the version number you want to use in the argument for the CreateObject or GetObject function. The preceding example opens Microsoft Access 97, which is version 8.0. Microsoft Access 95 is version 7.0.

If the application in which you are working supports the New keyword, you can use the New keyword to declare an object variable, return a reference to the Application object, and assign it to the object variable all in one step, as shown in the following example. 

Dim appAccess As New Access.Application

When a procedure that contains this code runs, Visual Basic returns a reference to the Application object and assigns it to the object variable. However, Visual Basic doesn't actually open Microsoft Access until you begin working with the object variable in code. In the following example, the declaration that contains the New keyword assigns a reference to the Application object to the object variable, but Microsoft Access doesn't open until the NewCurrentDatabase method runs. 

Dim appAccess As New Access.Application 
appAccess.NewCurrentDatabase "NewDb.mdb"

After you've created an object variable that represents the Application object, you can use it to work with any object in the Microsoft Access object hierarchy. For example, you can open the Northwind sample database, use the DoCmd object to open the Employees form, and then work with the Form object that represents the Employees form. To do this, add the following procedure to a Visual Basic module in Microsoft Excel and then run it. 

Sub OpenNorthwindEmployees()
	Dim appAccess As New Access.Application

	Const conPath As String = "C:\Program Files\Microsoft Office\Office" _
		& "\Samples\Northwind.mdb"

	With appAccess
		' Open the Northwind sample database.
		.OpenCurrentDatabase conPath
		' Open the Employees form.
		.DoCmd.OpenForm "Employees"
		' Set the form's caption.
		.Forms!Employees.Caption = "Northwind Employees"
	End With
End Sub

To work with CommandBar objects, you must first set a reference to the Microsoft Office 8.0 object library. You can set the reference from within Microsoft Access in the References dialog box (Tools menu). If you're working with Microsoft Access through Automation, you can set a reference to the Office object library from the other application. You can then use the CommandBars property of the Microsoft Access Application object to return a reference to the Office CommandBars collection.

You can also work with Data Access Objects (DAO) by first setting a reference to the Microsoft DAO 3.5 object library, then by using the DBEngine property of the Microsoft Access Application object to return a reference to the DAO DBEngine object. You can access all objects in the DAO object hierarchy through the DBEngine object, which is the top­level object in the hierarchy.

For more information about using Office CommandBar objects and DAO objects, see Chapter 8, "Menus and Toolbars," and Chapter 11, "Data Access Objects."

The Form Object and the Forms Collection

The Form object represents a Microsoft Access form that is open in Design view, Form view, or Datasheet view. Form objects are grouped in the Forms collection, which is a member of the Microsoft Access Application object. The Forms collection contains only the forms that are currently open in the database.

The following table shows the relationship between the Form object and the Forms collection and other objects and collections in the Microsoft Access object hierarchy.

Object or collection Is contained by Contains
Form object Forms collection Controls collection

Properties collection

Module object

Forms collection Application object Form objects

Referring to Form Objects

To work with a Form object in Visual Basic, you need to refer to the Form object in the Forms collection. To refer to a form, you must make sure that the form is open. To open a form with Visual Basic, use the OpenForm method of the DoCmd object.

If you refer to an individual Form object repeatedly within a procedure, you should declare an object variable to represent the Form object. If you know the name of the form, you can use the ! operator syntax to refer to the Form object in the Forms collection by name. For example, the following code returns a reference to the Employees form and assigns it to a variable of type Form. 

Dim frm As Form
Set frm = Forms!Employees

If you need to return a reference to a Form object and you won't know its name until run time, you can use the parentheses syntax to refer to the Form object within the Forms collection. This is useful if you want to pass the name of the form to a procedure as a variable, as shown in the following example. 

Function SetFormCaption(strFormName As String)
	Dim frm As Form

	' Open the form.
	DoCmd.OpenForm strFormName
	' Return a reference to the Form object.
	Set frm = Forms(strFormName)
	' Change the form's caption.
	frm.Caption = Date
End Function

You can also refer to an individual Form object by its index number, which indicates its position within the Forms collection. The Forms collection is indexed beginning with zero. That is, the index number for the first Form object in the Forms collection is 0, the second is 1, and so on.

Finally, if you need to set a Form object's property or call a method, but you don't need to use the Form object repeatedly throughout the procedure, you can refer to the form's class module directly in order to set the property or call the method. For example, the following code makes the form visible on the screen. 

Form_Employees.Visible = True

For more information about class modules, see "Standard Modules vs. Class Modules" later in this chapter.

Properties of the Form Object

The properties of the Form object are too numerous to include in this chapter, so this section discusses only a few that deserve special consideration. To see all of the available properties of the Form object, search Microsoft Access Help for "Form object," or view the members of the Form object in the Object Browser.

The Me Property

The Me property returns a reference to the form in which code is currently running. You can use the Me property in procedures within a form module as shorthand for the full form reference. You can also use it to pass a Form object to a procedure without knowing the name of the form. If you use the Me property in code behind a form, you can rename the form without having to update your code.

The following example shows how you can use the Me property within an event procedure in a form module. This procedure sets the BackColor property of the form's detail section to a random color. 

' Add this procedure to form module.
Private Sub Form_Load()
	' Initializes random number generator.
	Randomize
	' Sets BackColor property of form section.
	Me.Section(acDetail).BackColor = RGB(Rnd * 256, Rnd * 256, Rnd * 256)
End Sub

The following example also sets the detail section's BackColor property, but the Load event procedure passes a reference to the Form object to a procedure in a standard module. This strategy is preferable, because you can call the procedure in the standard module from any form, not just the one that contains the Load event. 

' Add this procedure to form module.
Private Sub Form_Load()
	' Passes reference to current form to ChangeBackColor procedure.
	ChangeBackColor Me
End Sub

' Add this procedure to standard module.
Public Sub ChangeBackColor(frm As Form)
	Randomize
	frm.Section(acDetail).BackColor = RGB(Rnd * 256, Rnd * 256, Rnd * 256)
End Sub

Note that when you're working with a Microsoft Access form from another application through Automation, you can't use the Me property to refer to the form from that application. You can only use the Me property to refer to a form in code within that form's module. The same is true for reports.

The Section Property

A form is divided into five sections: detail, header, footer, page header, and page footer. The Section property returns a reference to a particular section of a form. Once you've returned a reference to a form section, you can set properties for that section.

A number of properties apply to a form section rather than a Form object. For example, the BackColor property applies to a form section, not to a form, as shown in the preceding example. A section also has a Controls property, which returns a reference to the Controls collection for that section. The following example prints the names of all controls in the detail section of a form to the Debug window. 

Sub ControlsBySection(frm As Form)
	Dim ctl As Control

	' Enumerate the controls in the detail section.
	For Each ctl In frm.Section(acDetail).Controls
		Debug.Print ctl.Name
	Next ctl
End Sub

The Properties Property

The Properties property returns a reference to the Properties collection of a Form object. The Properties collection contains all of the properties of the form. You can enumerate the Properties collection with the For Each...Next statement. Note that you can't add a new property to the Properties collection. The following example prints all the properties of a Form object to the Debug window. 

Sub EnumerateFormProperties(frm As Form)
	Dim prp As Property

	' Enumerate the properties of a form.
	For Each prp In frm.Properties
		Debug.Print prp.Name, prp.Value
	Next prp
End Sub

The Module Property

The Module property returns a reference to the Module object associated with a form. You can assign this reference to a variable of type Module.

The module associated with a form doesn't automatically exist when the form is created. When you refer to the Module property, the module is created if it doesn't already exist. For more information, see the following section,"Form Modules."

The RecordSource Property

The RecordSource property binds a table or query to a form. After you've set the RecordSource property to the name of a table or query or to an SQL statement, you can display data from that table, query, or SQL statement on the form.

Form Modules

A Form object can have an associated module, which is represented by a Module object. However, the module does not exist when you first create the form. There are three ways to specify that Microsoft Access should create a module for a form:

  • Click Code on the View menu when the form is open in Design view. The module opens and is subsequently saved with the form, even if you don't add any code to it.

  • Set the form's HasModule property to True. You can set this property in the Microsoft Access property sheet or in Visual Basic. Note that setting this property to False removes the module and all code within it.

  • Refer to the form's Module property in Visual Basic. The Module property returns a reference to the Module object associated with the form, creating it first if it does not already exist.

If you don't need to add code to a particular form, then you don't need to create a module for it. Forms without modules open more quickly. Also, eliminating unnecessary modules reduces the size of your database.

A form module contains any event procedures that you define for the form. You can also add other procedures to the form module. However, you should include only procedures that are specific for that form. If you want a procedure to be available to other procedures throughout the database, place that procedure in a standard module.

Creating Forms at Run Time

If you want to create a new form at run time, you can use the CreateForm function. This can be useful if you are creating an add­in for Microsoft Access. For example, you may want to create an add­in that adds a custom address book form to a database based on information provided by the user at run time. You can use the CreateForm function to generate the form in Visual Basic. You can also use the CreateControl function and the DeleteControl statement to add controls to or delete controls from the new form.

You can also add code to the form module at run time by using the methods and properties of the Module object. For example, the CreateEventProc method of the Module object creates an event procedure for a specified object — a form, report, section, or control. The InsertLines method inserts lines of code at a specified position in the module. The following example creates a new form and adds an event procedure to its module. 

Function CreateFormWithCode () As Boolean
	Dim frm As Form, mdl As Module
	Dim lngLine As Long, strLine As String

	' Enable error handling.
	On Error GoTo Error_CreateFormWithCode
	' Create new form and return reference to Form object.
	Set frm = CreateForm
	' Return reference to form module.
	Set mdl = frm.Module
	' Create Load event procedure in form module.
	lngLine = mdl.CreateEventProc("Load", "Form")
	strLine = vbTab & "Me.Caption = " & Date
	' Set form's caption in Load event.
	mdl.InsertLines lngLine + 1, strLine
	' Return True if function is successful.
	CreateFormWithCode = True

Exit_CreateFormWithCode:
	Exit Function

Error_CreateFormWithCode:
	MsgBox Err & ": " & Err.Description
	CreateFormWithCode = False
	Resume Exit_CreateFormWithCode
End Function

For more information about writing and manipulating code with methods and properties of the Module object, see "The Module Object and the Modules Collection" later in this chapter, or search Microsoft Access Help for "Module object."

The Report Object and the Reports Collection

The Report object represents a Microsoft Access report that is open in Design view, Print Preview, or Layout Preview. Report objects are grouped in the Reports collection, which is a member of the Microsoft Access Application object. The Reports collection contains only the reports that are currently open in the database.

The following table shows the relationship between the Report object and the Reports collection and other objects and collections in the Microsoft Access object hierarchy.

Object or collection Is contained by Contains
Report object Reports collection Controls collection
Properties collection
Module object
Reports collection Application object Report objects

Report objects and Form objects have similar characteristics. This section only summarizes the characteristics of the Report object, because the same characteristics have been described in detail in the previous section, "The Form Object and the Forms Collection." For a list of the properties, methods, and events of the Report object, search Microsoft Access Help for "Report object," or view the members of the Report object in the Object Browser.

Referring to Report Objects

To work with a Report object in Visual Basic, you need to refer to the Report object in the Reports collection. To refer to a report, you must make sure that the report is open. To open a report with Visual Basic, use the OpenReport method of the DoCmd object.

You can refer to a Report object and assign it to an object variable in one of the following ways: 

Dim rpt As Report
Set rpt = Reports!Invoice	' Returns a reference to the Invoice report.
Set rpt = Reports("Invoice")	' Returns a reference to the Invoice report.
Set rpt = Reports(0)		' Returns a reference to the first report in
				' the collection.

Report Modules

Like a Form object, a Report object can have an associated module that is a class module. This module doesn't exist until you create it. You can create a report module by clicking Code on the View menu while the report is open in Design view, by setting the report's HasModule property to True, or by referring to the report's Module property in Visual Basic.

Creating Reports at Run Time

To create a new report at run time, use the CreateReport function. To add controls to or delete controls from a report at run time, use the CreateReportControl function or the DeleteReportControl statement.

The following example uses Automation from Microsoft Excel to create a linked table in a Microsoft Access database, and then creates a Microsoft Access report based on the data in the linked table. To use this example, you need to create a Microsoft Excel workbook named Revenue.xls, add some data to a worksheet in that workbook, and create a named range called DataRange that includes this data. Then, enter the following code in a module in the Microsoft Excel workbook. Before you run this example, you must set a reference to the Microsoft Access 8.0 object library and the DAO 3.5 object library from Microsoft Excel.

Important   Before you run this code, make sure that the Microsoft Excel ISAM driver (Msexcl35.dll) is installed on your system. If it's not, you need to run Setup again to install it. The Microsoft Excel ISAM driver enables Microsoft Excel 97 files to work with the Microsoft Jet database engine. For more information on working with the Microsoft Excel ISAM driver, search Microsoft Access Help for "Microsoft Excel driver." 

' Enter in Declarations section of a module.
Dim appAccess As New Access.Application

Sub PrintReport()

	Dim rpt As Access.Report, ctl As Access.TextBox
	Dim dbs As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
	Dim strDB As String, intLeft As Integer

	' Set this constant to the path to your Northwind sample database.
	Const conPath As String = "C:\Program Files\Microsoft Office\Office\Samples\"

	' Open database in Microsoft Access, specifying full path name.
	appAccess.OpenCurrentDatabase conPath & "Northwind.mdb"
	' Return reference to current database.
	Set dbs = appAccess.CurrentDb
	' Create new TableDef object.
	Set tdf = dbs.CreateTableDef("XLData")
	' Specify connection string for Microsoft Excel ISAM driver.
	tdf.Connect = "EXCEL 8.0; Database=C:\My Documents\Revenue.xls"
	' Specify source table as a named range in a worksheet.
	tdf.SourceTableName = "DataRange"
	' Append new linked table to database.
	dbs.TableDefs.Append tdf
	' Create new report in Microsoft Access.
	Set rpt = appAccess.CreateReport
	' Specify linked table as report's record source.
	rpt.RecordSource = tdf.Name
	
	' Create control on report for each field in linked table.
	For Each fld In tdf.fields
		Set ctl = appAccess.CreateReportControl(rpt.Name, acTextBox, , , _
			fld.Name, intLeft)
		intLeft = intLeft + ctl.Width
	Next fld

	' Open report in Print Preview.
	appAccess.DoCmd.OpenReport rpt.Name, acViewPreview
	' Restore report.
	appAccess.DoCmd.Restore
	' Display Microsoft Access as active application.
	AppActivate "Microsoft Access"
End Sub
The Control Object and the Controls Collection

The Control object represents a control on a Microsoft Access form or report. Control objects are grouped in Controls collections. The following table shows the relationship between the Control object and the Controls collection and other objects and collections in the Microsoft Access object hierarchy.

Object or collection Is contained by Contains
Control object Controls collection Controls collection, if the control is either an option group or a tab control

Properties collection

Hyperlink object

Control objects

Controls collection Form objects

Report objects

Control objects, if the control is an option group, tab control, text box, option button, toggle button, check box, combo box, list box, command button, bound object frame, or unbound object frame


Two types of controls are available to you in Microsoft Access. The Microsoft Access 8.0 object library provides built­in controls, which are available in the toolbox. In addition to the built­in controls that appear in the toolbox, Microsoft Access supports ActiveX controls, formerly called OLE controls or custom controls.

The Microsoft Access Controls

The following table describes the built­in controls available in Microsoft Access. The controls are listed by their class names, as they appear in the Object Browser.

ControlDescription
BoundObjectFrameDisplays a picture, chart, or OLE object stored in a Microsoft Access table.
CheckBoxIndicates whether an option is selected.
ComboBoxCombines a list box and a text box.
CommandButtonStarts an operation when the user clicks it.
ImageDisplays a picture.
LabelDisplays descriptive text.
LineDisplays a horizontal, vertical, or diagonal line.
ListBoxDisplays a list of values.
ObjectFrameDisplays a picture, chart, or OLE object that is not stored in a table.
OptionButtonIndicates whether an option is selected.
OptionGroupDisplays a set of options together.
PageDisplays controls on a page of a tab control.
PageBreakMarks the start of a new screen or printed page.
RectangleDisplays a rectangle.
SubForm/SubReportDisplays a form within another form or a report within another report.
TabControlDisplays multiple pages, each of which can contain controls.
TextBoxDisplays text data.
ToggleButtonIndicates whether an option is on or off.

For a list of the properties, methods, and events supported by each control, search Microsoft Access Help for the name of that control, or view the control's members in the Object Browser.

ActiveX Controls

An ActiveX control, like a built­in control, is an object that you place on a form to display data or perform an action. However, unlike a built­in control, the code that supports the ActiveX control is stored in a separate file or files which you must install in order to use the control.

The following ActiveX controls are available for you to use with Microsoft Access:

  • The Calendar control, which makes it easy to display and update a monthly calendar on a form. You can choose to install this control when you install Microsoft Access.

  • The WebBrowser control, which you can use to display Web pages and other documents in a Microsoft Access form. The WebBrowser control is supplied by Microsoft Internet Explorer version 3.0, which is available in the ValuPack folder on the Microsoft Office 97 or Microsoft Access 97 CD­ROM. Alternatively, if you have access to the World Wide Web, you can download Microsoft Internet Explorer version 3.0 from the Microsoft home page, at http://www.microsoft.com/. When you install Microsoft Internet Explorer, the WebBrowser control is automatically available for you to use in Microsoft Access.

  • For examples of the Calendar control and the WebBrowser control, see the Developer Solutions sample application that's included with Microsoft Access.

If you have Microsoft Office 97, Developer Edition, you have additional ActiveX controls, as described in the following table.

ControlDescription
AnimationDisplays animations stored in .avi files.
TabStripDisplays multiple pages, each of which can contain multiple controls.
ListView Displays data items in one of four list views.
TreeView Displays data in an expandable tree format.
ImageList Contains a set of images for use with other ActiveX controls.
ToolBar Displays a custom toolbar with buttons.
StatusBarDisplays status information associated with a form.
ProgressBar Shows the progress of a lengthy operation by filling a rectangle with blocks from left to right.
Slider Reflects a value or a range of values with a movable slider.
RichTextBox Displays text with rich text formatting features.
CommonDialog Displays one of a standard set of dialog boxes for operations such as opening, saving, and printing files or selecting colors and fonts.
UpDown Increments or decrements numbers, or scrolls through a range of values or a list of items.
Winsock Provides easy access to Transfer Control Protocol (TCP) and User Datagram Protocol (UDP) network services.

For more information about using ActiveX controls, see Chapter 12, "ActiveX Controls and Dialog Boxes," or search Microsoft Access Help for the name of the control. For information on the properties, methods, and events supported by an ActiveX control, see the documentation for that control, or set a reference to the control's object library and view its members in the Object Browser.

Referring to Control Objects

If you refer to a particular Control object repeatedly throughout a procedure, you may want to declare a variable to represent the Control object. If the control is a Microsoft Access control and you know what type of control it is, you can declare a variable of a specific control type. The following example declares a variable of type TextBox. 

Dim txt As TextBox

If you don't know what type of control your code may refer to when it runs, or if it will refer to an ActiveX control, you must declare a variable of the more generic type Control to represent the control. For example, if you define a procedure to which you can pass different types of controls, then you should declare an argument of type Control, as shown in the following code. You can pass any control to this function, but the function will return True only for controls that contain a valid hyperlink. 

Function FollowControlHyperlink(ctl As Control) As Boolean
	Const conNoHyperlink As Integer = 7976

	' Enable error handling.	
	On Error GoTo Error_FollowControlHyperlink
	' Follow control's hyperlink.
	ctl.Hyperlink.Follow
	' Return True if successful.
	FollowControlHyperlink = True

Exit_FollowControlHyperlink:
	Exit Function

Error_FollowControlHyperlink:
	If Err = conNoHyperlink Then
		FollowControlHyperlink = False
	End If
End Function

To refer to an individual Control object in a Controls collection when you know the control's name, use the ! operator syntax, as shown in the following example. Note that you use the Set statement when you're returning a reference to an object and assigning it to an object variable. 

Set txt = Forms!Employees!LastName	' Returns reference to LastName
					' control on Employees form.

If you're referring to a control on the form in which code is currently running, you can use the Me keyword to represent the form, as shown in the following example. 

Set txt = Me!LastName		' Returns reference to LastName control on
				' form in which code is running.

If you need to return a reference to a Control object and you don't know its name when you're writing the procedure, you can use the parentheses syntax to refer to the Control object within the Controls collection. This is useful if you want to pass the name of the control to a procedure as a variable. You can also refer to an individual Control object by its index number, which indicates its position within the Controls collection. The Controls collection is indexed beginning with 0 (zero). That is, the index number for the first Control object in the Controls collection is 0, the second is 1, and so on.

Properties of the Control Object

The properties that apply to controls are too numerous to include in this chapter, so this section discusses only two that deserve special consideration. To see all of the available properties of the Control object, search Microsoft Access Help for "Control object," or view the members of the Control object in the Object Browser.

The Hyperlink Property

The Hyperlink property returns a reference to a Hyperlink object. A Hyperlink object represents a text or graphic that contains a jump to a file, a location in a file, an HTML page on the World Wide Web, or an HTML page on an intranet.

The controls that support the Hyperlink property include the combo box, command button, image, label, and text box controls. Each of these controls can display a hyperlink that the user can click to follow. When you have a reference to a Hyperlink object in a control, you can use the Follow method of the Hyperlink object to follow the hyperlink, as shown in the example in the previous section.

For more information about hyperlinks, see Chapter 15, "Developing Applications for the Internet and World Wide Web."

The ControlType Property

The ControlType property indicates what type of control a particular Control object is. For example, the following procedure checks the ControlType property for each control on a form and sets the Locked property of text boxes and combo boxes to True. 

Function LockTextControls(frm As Form) As Boolean
	Dim ctl As Control

	' Enable error handling.	
	On Error GoTo Error_LockTextControls

	' Enumerate controls on form.
	For Each ctl In frm.Controls
		' If control is text box or combo box, set Locked property to True.
		If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
			ctl.Locked = True
		End If
	Next ctl
	' Return True if successful.
	LockTextControls = True

Exit_LockTextControls:
	Exit Function

Error_LockTextControls:
	MsgBox Err & ": " & Err.Description
	LockTextControls = False
	Resume Exit_LockTextControls
End Function

Data­Bound Controls

Some controls in Microsoft Access can be data­bound, which means they display data that is stored in a table, query, or SQL statement. The Microsoft Access data­bound controls include the bound object frame, check box, combo box, list box, option button, option group, text box, subform, and subreport controls. Some ActiveX controls, such as the Calendar control, can also be data­bound. Data­bound controls have a ControlSource property, which you can set to the name of a field in a table, query, or SQL statement to specify that the control should display data from that field. Note that before you can set the ControlSource property of a control, you must set the RecordSource property of the form or report to specify which table, query, or SQL statement supplies the data to the form or report.

The following example sets the RecordSource property of a form and the ControlSource property of a text box control in the form's Load event. 

Private Sub Form_Load()
	' Sets form's record source to Employees table.
	Me.Recordsource = "Employees"
	' Sets ControlSource property of text box to LastName field.
	Me!Text0.ControlSource = "LastName"
End Sub

Controls That Have a Controls Collection

Several controls have a Controls collection that can contain other controls. The option group control and the tab control can both contain multiple controls. The option group control has a Controls collection, which can contain option button, toggle button, check box, and label controls. The tab control has a Pages collection, and each Page object in the Pages collection has a Controls collection. The Controls collection for a Page object contains the Control objects on that page.

The following example displays the name of the first control on the first page of a tab control on an Employees form. 

Dim tbc As TabControl, pge As Page
Dim txt As TextBox

' Return reference to tab control.
Set tbc = Forms!Employees!TabCtl0
' Return reference to first page.
Set pge = tbc.Pages(0)
' Return reference to text box on page.
Set txt = pge.Controls(0)
MsgBox txt.Name

Other controls have a Controls collection that can contain a single control: an attached label. These controls include the text box, option group, option button, toggle button, check box, combo box, list box, command button, bound object frame, and unbound object frame controls.

The Module Object and the Modules Collection

The Module object represents a module in Microsoft Access. Module objects are contained in the Modules collection, which is a member of the Microsoft Access Application object. A Form or Report object can also contain a single Module object.

The set of all modules in a Microsoft Access database make up the Visual Basic project for that database. The Modules collection contains all the currently open modules in the project. Modules that are not open for editing are not included in the Modules collection. To open a module in Visual Basic, use the OpenModule method of the DoCmd object.

The following table shows the relationship between the Module object and the Modules collection and other objects in the Microsoft Access object hierarchy.

Object or collection Is contained by Contains
Module object Modules collection

Form objects

Report objects

None
Modules collection Application object Module objects

Referring to Module Objects

To work with a Module object in Visual Basic, you need to refer to the Module object in the Modules collection. To refer to a module, you must make sure that the module is open. You can refer to a standard or class Module object and assign it to an object variable in any of the following ways: 

Dim mdl As Module
Set mdl = Modules![Utility Functions]	' Returns a reference to the
					' Utility Functions module.
Set mdl = Modules("Utility Functions")	' Returns a reference to the
					' Utility Functions module.
Set mdl = Modules(0)			' Returns a reference to the first
					' module in the collection.

A form or report class module that's open is included in the Modules collection. To refer to a form or report class module that's not open, use the Module property of the form or report to return a reference to the associated Module object, as discussed earlier in this chapter.

Standard Modules vs. Class Modules

Microsoft Access contains two types of modules: standard modules and class modules. Both types of modules are available in the Modules tab of the Database window. A form or report can also have an associated class module.

When you write code that you want to be available to any procedure in the project, you should put that code in a standard module. Standard modules are public by default, which means that any procedure in the project can call a procedure or use a module­level variable defined in a standard module. Also, if you set a reference to a project in a Microsoft Access database from another Microsoft Access project, you can call code in a standard module in the project to which you've set the reference.

Class modules, on the other hand, are always private. You can use class modules to create custom objects to use within the current project. However, you can't share those objects with other projects. The Sub and Function procedures that you define within a class module become methods of the custom object defined by the class module, and any Property Let, Property Get, and Property Set procedures become its properties.

You use the class module associated with a form or report to define event procedures for the form or report and its controls. You can also add any procedures that you want to be available only to that particular form or report.

For more information about standard modules and class modules, search Microsoft Access Help for "standard modules" or "class modules."

Properties of the Module Object

The following table describes the properties of the Module object.

PropertyDescription
Application Returns a reference to the Application object.
CountOfDeclarationLines Returns the number of lines of code in the Declarations section of a module.
CountOfLines Returns the number of lines of code in a module.
Lines Returns the text of a specified line or lines of code.
Name Returns the name of a module.
Parent Returns a reference to the object or collection that contains the module.
ProcBodyLine Returns the number of the line on which the procedure definition begins.
ProcCountLines Returns the number of lines in a procedure.
ProcOfLine Returns the name of the procedure that contains a particular line.
ProcStartLine Returns the number of the line on which a procedure begins.
Type Indicates whether a module is a class module or a standard module.

Determining the Number of Lines in a Module

The lines in a module are numbered beginning with 1. The number of the last line in a module is equal to the value of the CountOfLines property. The number of the last line in the Declarations section of a module is equal to the value of the CountOfDeclarationLines property.

Note   Line numbers don't actually appear in a module; they're used only for reference.

Working with Procedures

You can use the Lines, ProcBodyLine, ProcCountLines, ProcOfLine, and ProcStartLine properties to get information about a procedure in a module. Procedures can be one of four types: a Sub or Function procedure, a Property Get procedure, a Property Let procedure, or a Property Set procedure. Sub and Function procedures are considered the same type. Most of your procedures will be of this type. You don't need to be concerned with the last three unless you're creating properties within class modules.

The ProcBodyLine property returns the number of the line on which the procedure definition begins; that is, the line that includes a Sub, Function, Property Get, Property Let, or Property Set statement. The ProcStartLine property returns the number of the line immediately following the procedure separator, if you have the Full Module View and Procedure Separator options set on the Module tab of the Options dialog box (Tools menu). This line number may or may not be the same as the one returned by the ProcBodyLine property. Any comments, module­level declarations, or empty lines that precede the procedure definition are considered part of the procedure. The ProcStartLine property returns the number of the first line of the full procedure.

The following example uses the ProcCountLines, ProcStartLine, ProcBodyLine, and Lines properties to print a procedure in a module to the Debug window. 

Function ProcLineInfo(strModuleName As String, strProcName As String) As Boolean
	Dim mdl As Module
	Dim lngStartLine As Long, lngBodyLine As Long
	Dim lngCount As Long, lngEndProc As Long

	On Error GoTo Error_ProcLineInfo
	' Open specified Module object.
	DoCmd.OpenModule strModuleName
	' Return reference to Module object.
	Set mdl = Modules(strModuleName)

	' Count lines in procedure.
	lngCount = mdl.ProcCountLines(strProcName, vbext_pk_Proc)
	' Determine start line.
	lngStartLine = mdl.ProcStartLine(strProcName, vbext_pk_Proc)

	' Determine body line.
	lngBodyLine = mdl.ProcBodyLine(strProcName, vbext_pk_Proc)
	Debug.Print

	' Print all lines in procedure preceding body line.
	Debug.Print "Lines preceding procedure " & strProcName & ": "
	Debug.Print mdl.Lines(lngStartLine, lngBodyLine - lngStartLine)

	' Determine line number of last line in procedure.
	lngEndProc = (lngBodyLine + lngCount - 1) - Abs(lngBodyLine - lngStartLine)

	' Print all lines in body of procedure.
	Debug.Print "Body lines: "
	Debug.Print mdl.Lines(lngBodyLine, (lngEndProc - lngBodyLine) + 1)
	ProcLineInfo = True

Exit_ProcLineInfo:
	Exit Function	

Error_ProcLineInfo:
	MsgBox Err & " :" & Err.Description
	ProcLineInfo = False
	Resume Exit_ProcLineInfo
End Function

You can call this function from the Northwind sample database with a procedure such as the following. 

Sub GetProcInfo()
	ProcLineInfo "Utility Functions", "IsLoaded"
End Sub

Methods of the Module Object

The following table describes the methods of the Module object.

MethodDescription
AddFromFile Adds the contents of a text file to a module.
AddFromString Adds the contents of a string to a module.
CreateEventProc Creates an event procedure within a class module.
DeleteLines Deletes specified lines from a module.
Find Finds specified text in a module.
InsertLines Inserts a line or group of lines of code at a specified point in a module.
ReplaceLine Replaces a line in a module with specified text.

Adding Text to a Module

If you want to add a string of text to a module, use the InsertLines method. With this method, you can specify at which line in the procedure you want the text to be added.

The following example creates a new form, adds a command button, creates a Click event procedure for the command button, and inserts a line of code with the InsertLines method. 

Function ClickEventProc() As Boolean
	Dim frm As Form, ctl As Control, mdl As Module
	Dim lngReturn As Long

	On Error GoTo Error_ClickEventProc
	' Create new form.
	Set frm = CreateForm
	' Create command button on form.
	Set ctl = CreateControl(frm.Name, acCommandButton, , , , 1000, 1000)
	ctl.Caption = "Click here"
	' Return reference to form module.
	Set mdl = frm.Module
	' Add event procedure.
	lngReturn = mdl.CreateEventProc("Click", ctl.Name)
	' Insert text into body of procedure.
	mdl.InsertLines lngReturn + 1, vbTab & "MsgBox ""Way cool!"""
	ClickEventProc = True

Exit_ClickEventProc:
	Exit Function

Error_ClickEventProc:
	MsgBox Err & " :" & Err.Description
	ClickEventProc = False
	Resume Exit_ClickEventProc
End Function

Creating a New Module

You can create a new module with the RunCommand method of the Application object. The following example creates a new module and opens it in Design view. Note that this code may not run in every view. 

RunCommand acCmdNewObjectModule

You may want to add a new module with Visual Basic in order to add text from a file. The following example uses the AddFromFile method to add the contents of a text file to a new module. The procedure saves the new module with the same name as the text file. 

Function AddFromTextFile(strFileName) As Boolean
	Dim strModuleName As String, intPosition As Integer
	Dim intLength As Integer
	Dim mdl As Module

	' Store file name in variable.
	strModuleName = strFileName

	' Remove directory path from string.
	Do
		' Find \ character in string.
		intPosition = InStr(strModuleName, "\")
		If intPosition = 0 Then
			Exit Do
		Else
			intLength = Len(strModuleName)
			' Remove path from string.
			strModuleName = Right(strModuleName, Abs(intLength - intPosition))
		End If
	Loop

	' Remove file extension from string.
	intPosition = InStr(strModuleName, ".")
	If intPosition > 0 Then
		intLength = Len(strModuleName)
		strModuleName = Left(strModuleName, intPosition - 1)
	End If

	' Create new module.
	RunCommand acCmdNewObjectModule
	' Save module with name of text file, excluding path and extension.
	DoCmd.Save , strModuleName
	' Return reference to Module object.
	Set mdl = Modules(strModuleName)
	' Add contents of text file.
	mdl.AddFromFile strFileName
	' Save module with new text.
	DoCmd.Save
End Function

When you run this procedure, avoid stepping through the line that first saves the module. If you enter break mode by stepping through this line, the module in which the code is running gets the focus, rather than the module that the code has just created. Visual Basic then tries to save the module in which the code is running rather than the new module.

Note that to create a new module with the RunCommand method, the Module command on the Insert menu must be available.

Class Module Events

Class modules that aren't associated with a form or report have two events: the Initialize event and the Terminate event. The Initialize event occurs when you create a custom object in memory from its class definition. The Terminate event occurs when you remove a custom object from memory.

To create event procedures for the Initialize and Terminate events, open the class module and click Class in the Object box. Then click Initialize or Terminate in the Procedure box.

You can use these events to run code when you create a custom object in memory or remove it from memory. For example, you may want to initialize a module­level variable defined in the class module when you create a custom object. The following example declares a module­level variable. When the Initialize event procedure runs, Visual Basic assigns the variable a value. 

' Declare module-level variable.
Public intX As Integer

Private Sub Class_Initalize()
	intX = 10
End Sub
The Reference Object and the References Collection

The Reference object represents a reference from Microsoft Access to another project or object library. Reference objects are contained in the References collection. Each Reference object in the References collection corresponds to a reference that is set in the References dialog box (Tools menu).

You can use the Reference object and References collection to add references with Visual Basic, to check existing references, or to remove references that are no longer needed.

The following table shows the relationship between the Reference object and the References collection and other objects in the Microsoft Access object hierarchy.

Object or collection Is contained by Contains
Reference object References collection None
References collection Application object Reference object

Referring to Reference Objects

To work with a Reference object in Visual Basic, you need to refer to the Reference object in the References collection. You can refer to a Reference object and assign it to an object variable in any of the following ways: 

Dim ref As Reference
Set ref = References!VBA	' Assigns Reference object to a variable.
Set ref = References("VBA")	' Assigns Reference object to a variable.
Set ref = References(1)		' Returns a reference to the first Reference
				' in the collection.

Properties of the Reference Object

The following table describes the properties of the Reference object.

PropertyDescription
BuiltIn Indicates whether a Reference object points to a default reference that's necessary for Microsoft Access to function properly.
Collection Returns a reference to the References collection.
FullPath Returns the path and file name of the referenced project or object library.
GUID Returns the globally unique identifier (GUID) for a referenced project or object library. A GUID is stored in the Windows registry.
IsBroken Indicates whether a Reference object points to a valid reference.
Kind Indicates whether a Reference object points to a Visual Basic project or to an object library.
Major Returns the value to the left of the decimal point in the version number of a file to which a reference has been set.
Minor Returns the value to the right of the decimal point in the version number of a file to which a reference has been set.
Name Returns the name of the project or object library to which a reference has been set.

For more information about each of these properties, search Microsoft Access Help for the name of the property.

Methods of the References Collection

The following table describes the methods of the References collection.

MethodDescription
AddFromFile Creates a reference to a file that contains a project or object library.
AddFromGUID Creates a reference to a project or object library based on its GUID, which is stored in the Windows registry.
Item Returns a particular member of the References collection.
Remove Removes a Reference object from the References collection.

For more information about each of these methods, search Microsoft Access Help for "References collection."

Setting a Reference in Visual Basic

You can use the AddFromFile or AddFromGUID method to set a reference in Visual Basic. The following example creates a reference at run time. 

Function AddReference(strFilePath As String) As Boolean
	Dim ref As Reference

	Const conReferenceExists As Long = 32813
	
	On Error GoTo Error_AddReference
	' Add reference to project or object library.
	Set ref = References.AddFromFile(strFilePath)
	AddReference = True

Exit_AddReference:
	Exit Function

Error_AddReference:
	If Err <> conReferenceExists Then
		MsgBox Err & ": " & Err.Description
	End If
	AddReference = False
	Resume Exit_AddReference
End Function

You can call this function to set a reference to the Developer Solutions sample application, as shown in the following example. 

Sub SetSolutionsReference()
	Const strRefPath As String = "C:\Program Files\Microsoft Office" _
		& "\Office\Samples\Solutions.mdb"

	If AddReference(strRefPath) = True Then
		MsgBox "Reference set successfully."
	Else
		MsgBox "Reference not set successfully."
	End If
End Sub

The DoCmd Object

You can use the DoCmd object to carry out macro actions in Visual Basic. Macro actions perform common operations that aren't supported by other objects. For example, you can use methods of the DoCmd object to open, save, or close tables, forms, queries, reports, macros, and modules in Visual Basic. You can also use methods of the DoCmd object to maximize, minimize, or restore a window. Several of the examples in this chapter demonstrate the uses of the DoCmd object — for example, the ProcLineInfo procedure in "Properties of the Module Object" earlier in this chapter.

To see a list of the methods of the DoCmd object, search for "DoCmd" in the Object Browser. You can also see a list of the methods of the DoCmd object, as well as get more information on each method, by searching Microsoft Access Help for "DoCmd object."

The Screen Object

The Screen object refers to the form, report, datasheet, or control that has the focus. You use the Screen object to work with a particular object on the current screen. For example, you can use the ActiveForm property of the Screen object to return a reference to the form in the active window without knowing the form's name. This is useful when you need to work with the active form but don't necessarily know which form that will be.

Properties of the Screen Object

The following table describes the properties of the Screen object.

PropertyDescription
ActiveControl Returns a reference to the control that has the focus.
ActiveDatasheet Returns a reference to the datasheet that has the focus.
ActiveForm Returns a reference to the form that has the focus.
ActiveReport Returns a reference to the report that has the focus.
Application Returns a reference to the Application object.
MousePointer Sets or returns a value that specifies the type of mouse pointer currently displayed.
Parent Returns a reference to the object that contains the Screen object.
PreviousControl Returns a reference to the control that last had the focus.

When you use the Screen object, you may want to implement error handling because an object other than the one you expect may have the focus when your code runs. It may be preferable to first use the SetFocus method of a form, report, or control to set the focus to the object you want, so that you can always be certain that the correct object has the focus. Also, avoid using the Screen object with the OutputTo method of the DoCmd object.

Note that the ActiveForm property and the Me property do not necessarily return a reference to the same form. The Me property represents the form in which code is currently running. The ActiveForm property returns a reference to the form that is active on the screen, which may not be the form in which code is currently running. For example, a Timer event may occur on a form that is not the active form. You can use the Me property to refer to the form on which the Timer event is occurring, and the ActiveForm property to refer to the form that is active on the screen while the Timer event is occurring.

The following example uses a Timer event to requery the active form at regular intervals. The form on which the Timer event occurs may or may not be the active form. 

Private Sub Form_Load()
	Me.TimerInterval = 30000
End Sub

Private Sub Form_Timer()
	Const conFormNotActive As Integer = 2475
	Const conFormInDesignView As Integer = 2478

	On Error GoTo Error_Timer
	' Requery record source for active form.
	Screen.ActiveForm.Requery

Exit_Timer:
	Exit Sub

Error_Timer:
	If Err = conFormNotActive Or Err = conFormInDesignView Then
		Resume Exit_Timer
	Else
		MsgBox Err & ": " & Err.Description
	End If
End Sub