C H A P T E R 3 | Microsoft Office 97/Visual Basic Programmer's Guide |
Microsoft Access Objects |
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:
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 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 represents the Microsoft Access application and is the toplevel 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 toplevel 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:
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 toplevel 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 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.
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()
' Initializes random number generator.
Randomize
' Sets BackColor property of form section.
Me.Section(acDetail).BackColor = RGB(Rnd * 256, Rnd * 256, Rnd * 256)
End Sub
' 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:
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 addin for Microsoft Access. For example, you may want
to create an addin 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.
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 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.
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:
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."
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.
Properties collection
Hyperlink object
Control 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 builtin
controls, which are available in the toolbox. In addition to the
builtin 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 builtin controls available
in Microsoft Access. The controls are listed by their class names,
as they appear in the Object Browser.
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 builtin control, is an object
that you place on a form to display data or perform an action.
However, unlike a builtin 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:
If you have Microsoft Office 97, Developer Edition, you have
additional ActiveX controls, as described in the following table.
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.
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.
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.
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.
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.
DataBound Controls
Some controls in Microsoft Access can be databound,
which means they display data that is stored in a table, query,
or SQL statement. The Microsoft Access databound 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 databound. Databound 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.
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.
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 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.
Form objects
Report 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:
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 modulelevel
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.
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, modulelevel 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.
You can call this function from the Northwind sample database
with a procedure such as the following.
Methods of the Module Object
The following table describes the methods of the Module
object.
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.
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.
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.
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 modulelevel variable defined in the
class module when you create a custom object. The following example
declares a modulelevel variable. When the Initialize event
procedure runs, Visual Basic assigns the variable a value.
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.
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:
Properties of the Reference Object
The following table describes the properties of the Reference
object.
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.
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.
You can call this function to set a reference to the Developer
Solutions sample application, as shown in the following example.
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 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.
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.
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
Object or collection
Is contained by
Contains Report object
Reports collection
Controls collection
Properties collection
Module object
Reports collection
Application object
Report objects
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.
' 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
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
Controls collection
Form objects
Control Description
BoundObjectFrame Displays a picture, chart, or OLE object stored in a Microsoft Access table.
CheckBox Indicates whether an option is selected.
ComboBox Combines a list box and a text box.
CommandButton Starts an operation when the user clicks it.
Image Displays a picture.
Label Displays descriptive text.
Line Displays a horizontal, vertical, or diagonal line.
ListBox Displays a list of values.
ObjectFrame Displays a picture, chart, or OLE object that is not stored in a table.
OptionButton Indicates whether an option is selected.
OptionGroup Displays a set of options together.
Page Displays controls on a page of a tab control.
PageBreak Marks the start of a new screen or printed page.
Rectangle Displays a rectangle.
SubForm/SubReport Displays a form within another form or a report within another report.
TabControl Displays multiple pages, each of which can contain controls.
TextBox Displays text data.
ToggleButton Indicates whether an option is on or off.
Control Description
Animation Displays animations stored in .avi files.
TabStrip Displays 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.
StatusBar Displays 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.
Dim txt As TextBox
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
Set txt = Forms!Employees!LastName ' Returns reference to LastName
' control on Employees form.
Set txt = Me!LastName ' Returns reference to LastName control on
' form in which code is running.
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
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
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
Object or collection
Is contained by
Contains Module object
Modules collection
None Modules collection
Application object
Module objects
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.
Property Description
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.
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
Sub GetProcInfo()
ProcLineInfo "Utility Functions", "IsLoaded"
End Sub
Method Description
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.
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
RunCommand acCmdNewObjectModule
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
' Declare module-level variable.
Public intX As Integer
Private Sub Class_Initalize()
intX = 10
End Sub
Object or collection
Is contained by
Contains Reference object
References collection
None References collection
Application object
Reference object
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.
Property Description
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.
Method Description
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.
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
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
Property Description
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.
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