- Understanding Macros in Access
- Responding to Form and Report Events
- Writing VBA Code Within Event Procedures
- Using Custom Procedures
- Responding to Runtime Errors
- Optimizing an Access Application
- Analyzing an Application
- Summary
Writing VBA Code Within Event Procedures
An event procedure can contain any VBA statements, some examples of which are included in this section. These examples show statements that change the values of properties, run DoCmd methods, and use the Access built-in functions.
Assigning Object Property Values
As you've learned, objects on a form or report have many properties, most of which you define in a property sheet when you design a form or report. Most properties defined at design time (when the form is displayed in Design view) can be changed by VBA code that runs while a form is being used. Some properties can't be defined at design time but are accessible only at run time (when the form is displayed in Form view).
You can set or change the value of an object's properties by writing assignment statements within event procedures. For example, you can use the assignment statements
cmdSave.Enabled = True cmdSave.Enabled = False
to enable or disable a command button named cmdSave.
Executing DoCmd Methods
Access contains a set of methods known as DoCmd methods, many of which have similar functionality to macro actions, but with the advantage of giving you the opportunity to have more control over what happens. The syntax for using one of these methods is:
DoCmd.methodname(arguments)
The following is an example of how you can use one of the DoCmd methods. This event procedure uses the DoCmd.TransferSpreadsheet method to transfer the contents of the tblPublisher table in the Book database to an Excel worksheet. A button named cmdSpreadsheet in the frmPublisher form has the following event procedure for its Click event:
Private Sub cmdSpreadsheet_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"tblPublisher", "C:\My Documents\Publisher.xls"
End Sub
After you add the cmdSpreadsheet button to the frmPublisher form and create the event procedure, clicking the button results in the contents of the tblPublisher table being copied into an Excel 2000 worksheet.
This example should help you understand the power that exists in some of the DoCmd methods.
Table 2 lists the DoCmd methods that Microsoft recommends for use in Access 2000 applications. Refer to the Help topic for each method for detailed information about its use, including the arguments required by some methods.
Table 2 DoCmd Methods
|
Method |
Purpose |
|
AddMenu |
Replaces a built-in menu bar for a form or report with a custom menu bar. Replaces a built-in shortcut menu for a form, report, or control on a form with a custom shortcut menu. |
|
ApplyFilter |
Filters data available to a form or report. |
|
Beep |
Sounds a beep through the computer's speaker. |
|
CancelEvent |
Cancels the normal response to an event. |
|
Close |
Closes the active window or a specified window. |
|
CopyObject |
Copies a database object within the current database or to another database. |
|
DeleteObject |
Deletes a specified object. |
|
FindNext |
Finds the next record according to the criterion set in the most recent FindRecord or Find method. |
|
FindRecord |
Finds the first record in a table, record set, or form object that satisfies specified criteria. |
|
GoToControl |
Moves the focus to a specified field or control in the current record of the open form, form datasheet, table datasheet, or query datasheet. |
|
GoToRecord |
Makes the specified record the current record in a table, record set, or form. |
|
Hourglass |
Changes the mouse pointer from the normal icon to an hourglass icon, or restores the mouse pointer to the normal icon. |
|
Maximize |
Enlarges the active window to fill the Access window. |
|
Minimize |
Reduces the active window to a title bar in the Windows taskbar. |
|
MoveSize |
Moves or resizes the active window. |
|
OpenDataAccessPage |
Opens a data access page in Page or Design view. |
|
OpenDiagram |
Opens a database diagram in Design view. |
|
OpenForm |
Opens a form in Form view, Design view, Print Preview, or Datasheet view. |
|
OpenModule |
Opens a specified Visual Basic module at a specified procedure. |
|
OpenQuery |
Opens a select or crosstab query in Datasheet view, Design view, or Print Preview. |
|
OpenReport |
Opens a report in Design view or Print Preview, or prints a report. |
|
OpenStoredProcedure |
Opens a stored procedure in Datasheet view, Design view, or Print Preview. |
|
OpenTable |
Opens a table in Datasheet view, Design view, or Print Preview. |
|
OpenView |
Opens a view in Datasheet view, Design view, or Print Preview. |
|
OutputTo |
Outputs data from a datasheet, form, report, module, or data access page to a file in Excel, text, rich text, HTML, or Internet Information Server formats. |
|
PrintOut |
Prints datasheets, forms, reports, data access pages, and modules in the open database. |
|
Rename |
Renames a specified database object. |
|
RepaintObject |
Completes any pending screen updates for a specified database object. |
|
Requery |
Updates data in a specified control from the data source. |
|
Restore |
Restores a maximized or minimized window to its original size. |
|
RunCommand |
Runs a command in a built-in menu or toolbar (not in a custom menu or toolbar). |
|
Save |
Saves a specified Access object. |
|
SelectObject |
Selects a specified database object. |
|
SendObject |
Includes a datasheet, form, report, module, or data access page in an email message. |
|
SetMenuItem |
Sets the state of menu items on the custom or global menu bar for the active window. |
|
SetWarnings |
Turns system messages on or off. |
|
ShowAllRecords |
Removes any applied filter from the active table, record set, or form. |
|
ShowToolbar |
Displays or hides a built-in or custom toolbar. |
|
TransferDatabase |
Imports or exports data between the current Access database or Access project and another database. |
|
TransferSpreadsheet |
Imports or exports data between the current Access database or Access project and an Excel file. |
|
TransferText |
Imports or exports text between the current Access database or Access project and a text file, and also a table or list in an HTML file. |
Using Built-in Functions
VBA contains a large number of built-in functions that you can use in any of your procedures. Because all the functions are conveniently listed and explained in Help, there's no point in explaining them all in this book.
NOTE
VBA Help contains an alphabetical list of all functions. To see this list, display the Visual Basic Editor window, and then choose Help, Microsoft Visual Basic Help. Display the Contents tab, open the Visual Basic Language Reference book, and then expand Functions.
Table 3 lists some of the most useful functions in several categories. In most cases, function names give you a strong clue about what functions do.
Table 3 Examples of VBA Functions
|
Category |
Examples of Functions |
|
Calculation |
Abs, Cos, Sin, Exp |
|
Data |
Array, Format, IIf |
|
Data Types |
CCur, CDate, CInt, CStr |
|
Dates and Times |
Date, DateAdd, Day, Hour, Month, Now, Timer |
|
Errors |
Error |
|
Financial |
DDB, FV, IRR, PV |
|
Folders and Files |
CurDir, Dir, EOF, FileLen, Input |
|
Messages |
InputBox, MsgBox |
|
Object |
CreateObject, GetObject |
|
Program Flow |
DoEvents, Shell |
|
String |
Asc, InStr, Left, Len, LTrim, Right, RTrim |
|
Testing |
IsDate, IsEmpty, IsNull, IsNumeric |