Transcript Chapter 8

1
Chapter 8
Object-Based
Programming in
VBA
8
Chapter Objectives
• Declare and use object variables
• Create procedures that use built-in form
methods
• Find and manipulate data with recordset
objects
• Execute parameter queries in VBA
• Communicate with other applications with
Automation objects
8
Chapter Objectives
• Find methods through the Object Browser
• Create class modules
• Open multiple form instances with userdefined objects
• Describe the features of a well-designed,
object-based application
8
The World According to
Methods
• Access uses objects and properties because
people understand and use them in their
daily lives and thus can easily apply the
concepts to programming
• Method
 Procedure or action that belongs to an object
8
Overview of Access Methods
• Access methods
perform actions
Figure 8-1
NewDegree
Type
procedure
8
Overview of Access Methods
Figure 8-1
NewDegree
Type
procedure
(continued)
8
Overview of Access Methods
• Facts about methods:
 RunCommand method
 Similar to RunCommand macro action
 DoCmd.RunCommand acCmdUndo erases the
user’s most recent entry from the form
 Open method
 When applied to a recordset object, the recordset
can access data in the table or query that is specified
in the Source argument
8
Overview of Access Methods
• Facts about methods (continued):
 AddNew method
 Attached to a recordset object
 Causes a recordset to create space for a new record
 Update method
 Causes the recordset to write the new record to the
database
 Close method
 Removes the reference to the recordset
8
Object Variables
• To invoke a method, the associated object
must be referenced
• Dot and bang notations
 Allow the identification of specific forms,
reports, controls, tables, or fields
 Can be used when invoking a method
8
Object Variables
• Dot and bang notation disadvantages:
 Can be long
 Not very efficient when used repeatedly
• Object variables
 Variables used in VBA procedures that contain a pointer
to the item of interest
 Efficient reference to Access application objects
 Must be used to reference DAO and ADO objects
8
Declaring and Setting Object
Variables
• Declaration statements declare variables as
String, Long, Integer, and so on
• VBA statements are similar to those found
in many other procedural languages
• In VBA, data types used in declaration
statements can also include objects
8
Another Technique for Setting
Object Variables
• Declare a control object variable and then
set the object variable to the control that
currently has the focus
 Control is identified by the ActiveControl
property of the Screen object
 Technique is used when a procedure can work
with whatever control has the focus
8
Built-in Methods Related to
Microsoft Access Objects
• Microsoft Access objects, including forms,
reports, and applications, support many
built-in methods
• Some objects, including forms, reports, and
independent class modules, support the
creation of custom methods
8
DoCmd Methods
• DoCmd objects
 Never manipulated by its methods, but those
methods frequently manipulate another object
• DoCmd methods
 Exist for almost every action that can be
selected from a macro’s action column
 Name matches a macro action
 Arguments usually correspond to the macro’s
action arguments
8
DoCmd Methods
Table 8-1
Commonly
used
DoCmd
methods
8
DoCmd Methods
Table 8-1
Commonly
used
DoCmd
methods
(continued)
8
DoCmd Methods
Table 8-1
Commonly
used
DoCmd
methods
(continued)
8
The RunCommand Method
and Its Intrinsic Constants
• Action performed by the RunCommand
method depends on the value of its
command argument
 Intrinsic constants is used to specify that value
 Exist for almost all of the controls listed in the
Access built-in menu bars
8
The RunCommand Method
and Its Intrinsic Constants
Figure 8-2 Deleting a record from frmStudentQDrops
8
Form and Control Methods
• Data shown on a form might not be current
because:
 The recordset generated for the form may not
reflect recent changes to the database
 Refreshing a recordset refers to the process of
updating the fields of all records currently in the
recordset
 Requerying a form completely regenerates the
recordset
8
Form and Control Methods
• Data on a form may not be current because
(continued):
 A calculated control may not recalculate its
expression after the value of a control used in
expression changes
 Calculated control should be recalculated
 The form may know the correct values to display,
but because Access is executing other procedures,
it does not have time to display correct values
 Form needs repainting
8
Form and Control Methods
Figure 8-3 frmCurrentStudents and txtQDrops
8
Methods that Update
Displayed Data
• Access automatically refreshes,
recalculates, and repaints forms at Accessdefined intervals
 Refresh interval
 Specified in the database’s Option window
8
Methods that Update
Displayed Data
• Refresh method
 To refresh the recordset
• Recalc method
 To cause a recalculation of the form’s calculated
controls
• Repaint method
 To force the system to repaint the screen
• Requery method
 To force Access to regenerate a recordset
8
Methods that Update
Displayed Data
Figure 8-4
Refreshing
and
requerying a
form
8
Methods that Update
Displayed Data
Table 8-2
Form and
control
methods
8
Methods that Update
Displayed Data
Table 8-2
Form and
control
methods
(continued)
8
Report, Module, and
Application Methods
• Application object
 Frequently used in Access applications
• Methods of the Application object:
 Echo Method
 Used in conjunction with DoCmd’s HourGlass
method
 Turns screen updating off while several changes
occur to the form
8
Report, Module, and
Application Methods
Table 8-3
Commonly
used
Application
object
methods
8
ADO and DAO Methods
• Methods related to ActiveX Data Objects (ADO)
and Data Access Objects (DAO) perform many
operations including:
 Connecting to the database
 Executing queries
 Updating data not currently displayed in form
• ADO is replacing DAO as the standard method for
manipulating data in Microsoft products
8
Methods of the Connection
Object
• Almost all procedures that use ADO objects
require a Connection object
 Identifies and opens the database that contains
the tables and queries that are used within the
procedure
 Establishes a connection to a database that will
be manipulated in VBA code
8
Methods of the Connection
Object
• Methods of a Connection Object:
 Close method
 Shuts down the pathway to the database
 Execute method
 Can be used to execute queries
8
Executing Queries in VBA
Through the Command Object
• The Execute method associated with the
Command object in VBA causes a query to run
Figure 8-6
qryDeleteDeniedProspects
8
Query Parameters
• Frequently, action queries triggered in VBA
code use VBA parameters
• Advantage of parameters is that the query
will retrieve or update different data
depending on the parameters’ values
• To execute a parameter query in VBA, the
values of the parameters must be supplied
8
Opening Recordsets
• Programmers can use recordsets to locate
and update data
 Must declare and set a recordset object variable
 Must open a pathway to the table or query that
contains the data through the Open method
• Recordset objects can be created from other
recordset objects through the Clone method
8
Finding Records in a
Recordset
• Bookmark property of a recordset
 A string expression that uniquely identifies the current
record
• AbsolutePosition property
 A long integer that contains the ordinal position of the
current record
• MoveFirst, MoveLast, MovePrevious, and
MoveNext methods
 Used for simple movements to new records
8
Finding Records in a
Recordset
• Move method
 Used in conjunction with a long integer to
move the specified number of records forward
• Seek method
 Used to search a recordset through the primary
key or indexes
• Filter property
 Similar to a form’s filter property
8
Editing, Deleting, and
Updating Data in a Recordset
• Programmers frequently use recordsets to
edit, delete, and update records
• Recordset methods specify how the data are
to be updated
• Recordsets must be opened with a
LockType that permit updating of the
database
8
Using DAO Recordsets
• Methods of DAO recordset
 FindFirst, FindNext, FindPrevious, and
FindLast
 Similar to ADO’s Find method
 AddNew, Update, and Delete
 Almost identical to their ADO counterparts
 Edit
 Must be executed before values in an existing record
are changed
8
Using DAO Recordsets
• RecordsetClone property of a form
 Returns a pointer to the recordset
• NoMatch property
 Used to indicate whether the previous Find
method failed to find a match
8
ADO and DAO Methods –
Some Final Thoughts
• CompactDatabase method
 Associated with the JetEngine object
 Makes the database smaller
• Append method
 Places newly created objects in a collection
• Delete method of a collection object
 Removes an object from the collection
8
The Object Browser
• The Object Browser is available in the
Visual Basic Editor (VBE)
Figure 8-8
Object
Browser for
VBA
Collection
Add method
8
Automation Object Variables
and Methods
• GetObject function
 Returns a pointer to objects in another Windows
application called the source application
• Automation object variable
 Any variable that references an object contained in
another application
• CreateObject function
 Used to create new applications in Access
8
Object-Based Approach to
Application Development
• The object-based philosophy of Access
provides some guidance as to how an
application should be organized
• Many objects make up an Access
application
 Most possess the ability to test or manipulate
their own features
8
Object-Based Approach to
Application Development
• Other objects such as collections and forms
possess abilities that perform certain
operations on themselves
 Procedures outside the object need to know
only which methods are available and what the
methods do
• A benefit of locating methods with objects
that they manipulate is that many objects
can use the same method
8
Object-Based Approach to
Application Development
• “Rules of thumb” for designing Access
applications:
 Organize procedures based on the object being
manipulated
 Try to build objects that can be reused
 Build Objects that are not highly coupled with
other objects
8
Opening Multiple Instances
of a Form
• User-defined object
 Class object that can be used to create other
objects
• Class object
 A model of an object
• Instances of the class object
 Objects that are based on a class
8
Creating and Using
Independent Class Modules
• Independent class modules
 Contain sub procedures and functions
 Can also contain Let and Get property
procedures
 Treated as an object
• Class modules can also invoke events
• Because a class module is an object,
instances of the object can be created
8
Chapter Summary
• In VBA objects and properties are manipulated
and created by methods
 Access supplies built-in methods
 You can create your own methods
• Objects can be identified through the dot and bang
notations
 Object variables shortens notation
• Object variables can be declared as any recognized
ADO, DAO, or Microsoft Access object type
8
Chapter Summary
• Form objects are associated with several
built-in methods
 Requery tells form to regenerate its recordset
 Recalc, Refresh, and Repaint ensure that Access
is displaying the correct data on a form
• DoCmd object offers many methods for
manipulating data on a form
8
Chapter Summary
• Recordsets can be created with several methods
and properties
 Recordset methods locate or change data in a table
• Filling parameters and executing an action query
in VBA code updates data
• Automation object variables allow VBA in Access
to use and manipulate objects in other languages
such as Word and Excel
8
Chapter Summary
• Access generates user-defined objects
whenever forms and reports are created
• The combination of the New keyword with
a user-defined object can be used to open
multiple form instances
• Assigning an object to a persistent
collection prolongs the object’s life