Home > Articles > Data > Access

  • Print
  • + Share This

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

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.