Writing Form and Report Event Procedures | Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))

3 4

To write code for a form, report, form or report section, or form control, open the object’s properties sheet, and click the Event tab. All the available events for the selected object will be listed; select [Event Procedure] next to an event, as shown in Figure 20-14, to open an existing event procedure or to create a new one.

figure 20-14. the event tab of the form properties sheet lists the form’s events.

Figure 20-14. The Event tab of the Form properties sheet lists the form’s events.

To create a code module for an event, click the Build button next to the event, as shown in Figure 20-15. (The Build button is visible only when you place the focus on that event box.)

figure 20-15. click the build button to create an event procedure stub.

Figure 20-15. Click the Build button to create an event procedure stub.

The Choose Builder dialog box will open; select Code Builder from the list of available builders (as shown in Figure 20-16).

figure 20-16. the choose builder dialog box appears when you create an event procedure.

Figure 20-16. The Choose Builder dialog box appears when you create an event procedure.

You can bypass the Choose Builder dialog box and go directly to the code module from the Build button if you select the Always Use Event Procedures check box on the Forms/Reports tab in the Access Options dialog box (as shown in Figure 20-17).

figure 20-17. select the always use event procedures check box to bypass the choose builder dialog box when you create event procedures.

Figure 20-17. Select the Always Use Event Procedures check box to bypass the Choose Builder dialog box when you create event procedures.

Form Events

Forms and form controls have an enormous number of events, but only a few of them are widely used. This section describes the most commonly used form events. For information about other form events, select the form, click the event you want to know more about in the properties sheet, and press F1 to open the event’s Help topic.

note

Anumber of events are listed with the On prefix on the Event tab in the properties sheet. This prefix isn’t part of the event name as it appears in Help or in the Object Browser, and it’s not used in the code stub for the event procedure.

Current. Occurs when the focus moves to a new record. This event is useful for making controls visible or invisible, depending on a condition. For datasheet forms, columns can be hidden from this event. The Current procedure shown here clears two combo boxes when the user goes to a new record:

 Private Sub Form_Current()  On Error GoTo ErrorHandler      Me![cboTitleSearchList] = Null     Me![cboAuthorSearchList] = Null    ErrorHandlerExit:      Exit Sub  ErrorHandler:     MsgBox "Error No: " & Err.Number & "; Description: " & _   Err.Description     Resume ErrorHandlerExit End Sub

Before Insert. Occurs when the first data is entered into a new record. This event is useful for automatically filling in certain fields with values, as in the following procedure , which saves the next higher number in a series to be used for an ID field:

 Private Sub Form_BeforeInsert(Cancel As Integer) On Error GoTo Form_BeforeInsertError    Dim lngID As Long    Me![subGaplessID].Form.Requery    lngID = Me![subGaplessID].Form![txtMaxID] + 1    Me![ID] = lngID     Me![txtID].Requery  Form_BeforeInsertExit:    Exit Sub Form_BeforeInsertError:    MsgBox "Error No: " & Err.Number & "; Description: " & _       Err.Description    Resume Form_BeforeInsertExit End Sub

After Insert. Occurs after a new record is added. Can be used to trigger requerying of another control or a pop-up form. The following event procedure requeries two record search combo boxes:

 Private Sub Form_AfterInsert()  On Error GoTo ErrorHandler    Me![cboTitleSearchList].Requery    Me![cboAuthorSearchList].Requery  ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & _       Err.Description    Resume ErrorHandlerExit      End Sub

Dirty. Occurs when the data in a form changes. Useful for making controls visible such as command buttons to save a new record or to undo changes. Setting the Dirty event’s Cancel argument to True wipes out the changes made to the form. Modifying form data from VBA code doesn’t trigger this event. The following event procedure gives the user a chance to back out of a change to a form:

 Private Sub Form_Dirty(Cancel As Integer) On Error GoTo ErrorHandler    Dim intResult As Integer    intResult = MsgBox("Did you mean to make this change?", _        vbYesNo)     If intResult = vbNo Then        Cancel = True    End If     ErrorHandlerExit:    Exit Sub ErrorHandler:     MsgBox "Error No: " & Err.Number & "; Description: " & _    Err.Description     Resume ErrorHandlerExit End Sub

Open. Occurs when a form is opened but before the first record is displayed. The Open event procedure listed here opens two other forms inhidden mode so that they can be made visible quickly when various command buttons on the form are clicked:

 Private Sub Form_Open(Cancel As Integer)  On Error GoTo ErrorHandler     DoCmd.OpenForm "frmCompanyInformation", , , , , acHidden    DoCmd.OpenForm "frmPersonalInformation", , , , , acHidden      ErrorHandlerExit:    Exit Sub      ErrorHandler:     MsgBox "Error No: " & Err.Number & "; Description: " & _       Err.Description     Resume ErrorHandlerExit     End Sub

Load. Occurs when a form is opened and its records are displayed. Can be used to size the form to the correct size or set the focus to a specific control, as in the following code sample:

 Private Sub Form_Load()  On Error GoTo ErrorHandler    DoCmd.RunCommand acCmdSizeToFitForm    Me![cboTitleSearchList].SetFocus  ErrorHandlerExit:    Exit Sub      ErrorHandler:     MsgBox "Error No: " & Err.Number & "; Description: " & _       Err.Description     Resume ErrorHandlerExit      End Sub

Close. Occurs when the form is closed. One typical use is to reopen a main menu form, as in the following procedure:

 Private Sub cmdClose_Click()  On Error GoTo ErrorHandler    Dim prj As Object     Set prj = Application.CurrentProject        If prj.AllForms("fmnuMain").IsLoaded Then       Forms![fmnuMain].Visible = True    Else       DoCmd.OpenForm "fmnuMain"    End If        DoCmd.Close acForm, Me.Name     ErrorHandlerExit:    Exit Sub     ErrorHandler:     MsgBox "Error No: " & Err.Number & "; Description: " & _       Err.Description    Resume ErrorHandlerExit     End Sub

The order of events when a form is opened is shown in Figure 20-18.

figure 20-18. events occur in a certain order when a form is opened.

Figure 20-18. Events occur in a certain order when a form is opened.

The order of events when a form is closed is shown in Figure 20-19.

figure 20-19. events occur in a certain order when a form is closed.

Figure 20-19. Events occur in a certain order when a form is closed.

Report Events

Reports have only a few events. The events of report sections are often used to apply conditional formatting to a group of controls or to display or hide report sections; these events are discussed in section in Chapter 7, "Using Reports to Print Data."

The most commonly used report events are as follows:

NoData. Occurs after a report is formatted but before it’s printed, when there’s no data to print on the report. This event has a Cancel argument that can be used to cancel report printing, as in the following procedure:

 Private Sub Report_NoData(Cancel As Integer) On Error GoTo Report_NoDataError    MsgBox "No records meet the report criteria; _        canceling report printing"    Cancel = True     Report_NoDataExit:    Exit Sub      Report_NoDataError:    MsgBox Err.Description    Resume Report_NoDataExit     End Sub

Open. Occurs when the report is opened but before it’s previewed or printed.

Form Control Events

Controls, like forms, have many events, but only a few are commonly used:

Click. Occurs when you click a control once with the left mouse button. Although this event applies to many controls, it’s generally used only with command buttons, which are designed to be clicked. The Click event of command buttons is used to run code to perform all sorts of actions, such as in the following procedure on the fpriBooksAndVideos form, which opens a pop-up form listing other books by the same author:
```
 Private Sub cmdBooksBySameAuthor_Click() On Error GoTo ErrorHandler    DoCmd.OpenForm "frmBooksBySelectedAuthor", acFormDS, _       acReadOnly     Forms![frmBooksBySelectedAuthor].Caption = "Books by " & _       Me![subBookAuthors]![LastNameFirst] ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & _       Err.Description    Resume ErrorHandlerExit     End Sub 
```
InsideOut

The Click event for Tab controls doesn’t work in Access; use the Change event of this control instead, with an If…Then or Select Case statement to respond to clicks on the tab control’s tabs. See the Change event code sample later in this section for an example of such a procedure.)

DblClick. Occurs when you double-click a control with the left mouse button. This event is useful for triggering events from datasheet subforms because command buttons aren’t displayed on a datasheet form or subform. The following DblClick event procedure opens a form with related information when a cell in a datasheet subform is double-clicked:

 Private Sub txtLoanNumber_DblClick(Cancel As Integer) On Error GoTo ErrorHandler       DoCmd.RunCommand acCmdSaveRecord    DoCmd.OpenForm "frmStudentLoanDetailsSingle", acNormal     ErrorHandlerExit:    Exit Sub     ErrorHandler:     MsgBox "Error No: " & Err.Number & "; Description: " & _       Err.Description     Resume ErrorHandlerExit     End Sub

AfterUpdate. Used to perform an action when the data in a control that contains text (such as a text box, list box, or combo box) is changed. The following procedure runs when a new value is selected in a combo box in a form header. It synchronizes the form with the selected record.

 Private Sub cboAuthorSearchList_AfterUpdate() On Error GoTo ErrorHandler     Dim strSearch As String         strSearch = "[AuthorID] = " & Me![cboAuthorSearchList]         'Find the record that matches the control.    Me.Requery     Me.RecordsetClone.FindFirst strSearch     Me.Bookmark = Me.RecordsetClone.Bookmark     ErrorHandlerExit:    Exit Sub     ErrorHandler:     MsgBox "Error No: " & Err.Number & "; Description: " & _       Err.Description    Resume ErrorHandlerExit     End Sub

BeforeUpdate. Occurs before changed data in a control is updated. The following event procedure assigns values to several variables and then calls a procedure that checks that the entry in the txtStateProvince text box is only two characters long if the country is U.S.A.:

 Private Sub txtStateProvince_BeforeUpdate(Cancel As Integer) On Error GoTo ErrorHandler    strStateProvince = Nz(Me![txtStateProvince].Value)    strCountry = Nz(Me![txtCountry].Value)    Set txt = Me![txtStateProvince]    Call CheckStateProvince(strStateProvince, strCountry, txt)     ErrorHandlerExit:    Exit Sub      ErrorHandler:     MsgBox "Error No: " & Err.Number & "; Description: " & _       Err.Description     Resume ErrorHandlerExit  End Sub

Change. Occurs when the contents of a text box or the text portion of a combo box changes but before the BeforeUpdate event. The Change event also occurs when you move to another page in a tab control. The Change event doesn’t occur when the value in a calculated control changes or when an item is selected in a combo box. The following procedure requeries the appropriate subform when a new page on a tab control is selected:

 Private Sub tabAddNew_Change() On Error GoTo ErrorHandler    Dim pg As Access.Page    Dim tbc As Access.TabControl    Dim strPage As String        Set tbc = Me![tabAddNew]    Set pg = tbc.Pages(tbc.Value)    strPage = pg.Name        Select Case strPage           Case "pgeAuthors"          Me![subAuthorsAddNew].Requery                 Case "pgePublishers"          Me![subPublishersAddNew].Requery                 Case "pgeSpecs"          Me![subSpecsAddNew].Requery              End Select     ErrorHandlerExit:    Exit Sub     ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & _        Err.Description    Resume ErrorHandlerExit End Sub

Adding Error Handling to Your Code

If your procedures lack error handling, when an error occurs the code will stop executing, an error message will appear, and the user might be left wondering what to do next. Figure 20-20 shows the message that appears when the user tries to save a record with nothing entered in its key field.

figure 20-20. a confusing error message appears when the user tries to save a record without a value in its key field.

Figure 20-20. A confusing error message appears when the user tries to save a record without a value in its key field.

After clicking OK, the user will be back in the form, at the same spot as before, with no specific information on how to fix the problem. To handle this error in a more informative manner, you can put an error handler into the form’s Close event. I use the following error handler in my procedures (with the current date):

 'Created by Helen Feddema 3-25-2001 'Last modified 3-25-2001 On Error GoTo ErrorHandler  ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & _        Err.Description    Resume ErrorHandlerExit

The error handler displays a message box with the error number and description and then exits the procedure. This is cleaner than leaving the user in a halted procedure. For many errors, this is sufficient, but if you want special handling for specific errors, you can use an If…Then or Select Case statement in the error handler to take a specific action when a specific error occurs.

The LNCBuilder function in the LNC Rename add-in (described in Chapter 21, "Creating Your Own Add-Ins") uses a Select Case statement in its error handler to handle two specific errors. It also runs two clean-up functions from the LNCBuilderExit label before exiting the function, as shown here:

 LNCBuilderExit:    'Delete old tables in calling database.    ClearCurrentSystemTable    ClearCurrentObjectsTable    Exit Function LNCBuilderError:    'If an option button or a check box is unbound, set    'fUnbound to True so that the code uses the NA function     'instead of CS.    Select Case Err.Number       Case 2455          fUnbound = True          Resume Next       Case 2465          MsgBox "You can’t rename a single control from the " _             & "Detail section"          Resume LNCBuilderExit       Case Else          MsgBox "Error No: " & Err.Number & "; Description: " _             & Err.Description          Resume LNCBuilderExit    End Select

You can also use error handlers to branch to a different section of code. I often use the following error handler in procedures that work with Word objects using Automation code. The first line of code attempts to assign the global gappWord variable to the current instance of Word (to avoid creating multiple Word instances). If Word isn’t running, Error 429 occurs and the error handler runs an alternative line of code, setting the gappWord variable to a new instance of Word using CreateObject.

    Set gappWord = GetObject(, "Word.Application")  ErrorHandlerExit:    Set gappWord = Nothing    Exit Sub ErrorHandler:    'Word is not running; open Word with CreateObject.    If Err.Number = 429 Then       Set gappWord = CreateObject("Word.Application")       Resume Next    Else       MsgBox "Error No: " & Err.Number & "; Description: "       Resume ErrorHandlerExit    End If

Using the References Collection to Set References Programmatically

If you write code that works with a specific object library, using early binding of variables, and someone else imports that code into a database that lacks a reference to that object library and runs the code, an error will occur. Typically, the error message will be uninformative—for example, the message shown in Figure 20-21 appeared when I tried to close a form after clearing the DAO object library option in the References dialog box (simulating the situation in which code that uses the DAO object model is imported into an Access 2000 or Access 2002 database with the ADO object model checked rather than the DAO object model).

figure 20-21. an uninformative error message results from a missing object library reference.

Figure 20-21. An uninformative error message results from a missing object library reference.

Another typical result of a missing object library reference is code that won’t compile; when you debug it, a perfectly standard function such as InStr or Mid (usually one of the string manipulation functions) will be highlighted as the cause of the error. Or you might get the error shown in Figure 20-22 with a DAO object declaration highlighted. This message is reasonably informative, but only to a programmer, not to an end user.

figure 20-22. a missing object library reference will cause a compile error message.

Figure 20-22. A missing object library reference will cause a Compile Error message.

In the Access interface, you can fix problems with missing object library references (after you’ve identified the cause of the problem) by opening the References dialog box in the Visual Basic Editor window (choose Tools, References) and checking the missing object library. Figure 20-23 shows the DAO object library being checking so that code using its components will compile and run correctly.

figure 20-23. you can check the dao object library reference in the references dialog box to prevent confusion when you reference its components in code.

Figure 20-23. You can check the DAO object library reference in the References dialog box to prevent confusion when you reference its components in code.

You can also set references in code, which can be particularly useful for code that will be run on other people’s computers, where you can’t know which object libraries will be checked.

tip

To find the full path and file name of the object library, select its line in the References dialog box and look at the Location line at the bottom of the dialog box.

The following code segment sets a reference to the Microsoft Office XP Web Components object library, which is needed to support PivotTables and PivotCharts. You can run this code from a function that’s run when a main menu form is opened or that runs from an AutoExec macro in a database.

    Dim refs As Access.References    'This line should be one long line in code.    Set refs = Application.References    refs.AddFromFile "C:\Program Files\Common Files     \Microsoft Shared\Web Components\10\OWC10.DLL"

caution

Unfortunately, if the missing reference you need to supply is the one for the DAO object library, you might not be able to set the reference in code because typically a missing DAO object library reference will prevent code from compiling and running. When I attempted to set a reference to the DAO object library in code after removing it in the interface or using the Remove method, I got a User-defined type not defined error and the code to add the reference wouldn’t run.