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.
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.
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.
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.
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
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
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
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
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
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
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
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.
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.
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:
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
Controls, like forms, have many events, but only a few are commonly used:
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.)
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
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
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
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
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.
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
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.
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.
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.
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
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