Given Access's graphical environment, your development efforts are often centered on forms, so you must understand all the Form and Control events and know which event you should code to perform each task. You should also know what types of forms are available and how you can get the look and behavior you want for them.
Often, you won't need to design your own form because you can make use of one of the built-in dialog boxes that are part of the VBA language or supplied as part of the Microsoft Office 97 Developer Edition tools. Whatever types of forms you create, you should take advantage of all the tricks and tips of the trade covered throughout this chapter, including adding menu bars and toolbars to your forms.
Microsoft Access traps for 29 Form events, each of which has a distinct purpose. Access also traps events for Form sections and controls. The following sections cover the Form events and when you should use them.
A form's Current event is one of the more commonly coded events. It happens each time focus moves from one record to another. The Current event is a great place to put code you want to execute whenever a record is displayed. For example, you might want the company name to appear with a special background if the client is an important one. The following code is placed in the Current event of the frmClients form that's part of the Time and Billing application:
Private Sub Form_Current()
If IsNull(Me![txtClientID]) Then
DoCmd.GoToControl "txtContactFirstName"
End If
End SubThis code moves focus to the txtContactFirstName control if the txtClientID of the record the user is moving to happens to be Null; this happens if the user is adding a new record.
The BeforeInsert event occurs when the first character is typed in a new record, but before the new record is actually created. If the user is typing in a text or combo box, the BeforeInsert event occurs even before the Change event of the text or combo box. The frmProjects form of the Time and Billing application has an example of a practical use of the BeforeInsert event:
Private Sub Form_BeforeInsert(Cancel As Integer)
Me![txtClientID] = Forms![frmClients]![txtClientID]
End SubThe frmProjects form is always called from the frmClients form. The BeforeInsert event of frmProjects sets the value of the txtClientID text box equal to the value of the txtClientID text box on frmClients.
The BeforeUpdate event runs before a record is updated. It occurs when the user tries to move to a different record or when the Records | Save Record command is executed. The BeforeUpdate event can be used to cancel the update process when you want to perform complex validations. When a user adds a record, the BeforeUpdate event occurs after the BeforeInsert event. The NorthWind database that comes with Access has an excellent example of using a BeforeUpdate event:
Private Sub Form_BeforeUpdate(Cancel As Integer)
' Display a message box that says that product name is required.
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
If IsNull(Me![ProductName]) Then
strMsg = "You must enter a product name before you leave the _
record." ' Define message.
intStyle = vbOKOnly + vbInformation ' Define buttons.
strTitle = "Product Name Required" ' Define title.
MsgBox strMsg, intStyle, strTitle
DoCmd.CancelEvent
End If
End SubThis code from the Products form determines whether the product name is Null. If it is, a message is displayed and the CancelEvent method is executed, canceling the update process.
The AfterUpdate event occurs after the changed data in a record is updated. You might use this event to requery combo boxes on related forms or perhaps log record changes. Here's an example:
Private Sub Form_AfterUpdate()
Me!cboSelectProduct.Requery
End SubThis code requeries the cboSelectProduct combo box after the current record is updated.
The AfterInsert event occurs after the record has actually been inserted. It can be used to requery a recordset when a new record is added.
Note
Here's the order of events when a user begins to type data into a new record:
BeforeInsert'BeforeUpdate'AfterUpdate'AfterInsert
The BeforeInsert event occurs when the user types the first character, the BeforeUpdate event happens when the user updates the record, the AfterUpdate event takes place when the record is updated, and the AfterInsert event occurs when the record that's being updated is a new record.
The Delete event occurs when a user tries to delete a record but before the record is actually removed from the table. This is a great way to place code that allows deleting a record only under certain circumstances. If the Delete event is canceled, the BeforeDelConfirm and AfterDelConfirm events never execute, and the record is never deleted.
The BeforeDelConfirm event takes place after the Delete event but before the Delete Confirm dialog box is displayed. If you cancel the BeforeDelConfirm event, the record being deleted is restored from the delete buffer, and the Delete Confirm dialog box is never displayed.
The AfterDelConfirm event occurs after the record is actually deleted, even if the deletion is canceled. If the BeforeDelConfirm is not canceled, the AfterDelConfirm event takes place after the Confirmation dialog box is displayed.
The Open event occurs when a form is opened but before the first record is displayed. With this event, you can control exactly what happens when the form first opens. The Open event of the Time and Billing application's frmProjects form looks like this:
Private Sub Form_Open(Cancel As Integer)
If Not IsLoaded("frmClients") Then
MsgBox "Open the Projects form using the Projects button on the _
Clients form."
Cancel = True
End If
End SubThis code checks to make sure the frmClients form is loaded. If it isn't, a message box is displayed, and the Cancel parameter is set to True, which prohibits the form from loading.
The Load event happens when a form is opened and the first record is displayed; it occurs after the Open event. A form's Open event can cancel the opening of a form, but the Load event can't. The following routine is placed in the Load event of the Time and Billing application's frmProjects form:
Private Sub Form_Load()
If Me.OpenArgs = "GotoNew" And Not IsNull([txtProjectID]) Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End SubThis routine looks at the string that's passed as an opening argument to the form. If the OpenArg string is "GotoNew" and the ProjectID isn't Null (the user is not already on a new record), a new record is inserted.
The Unload event happens when a form is closed, but before the form is actually removed from the screen. It's triggered when the user chooses Close from the File menu, quits the application by choosing End Task from the task list, quits Windows, or when your code closes the form. You can place code that makes sure it's okay to unload the form in the Unload event, and you can also use the Unload event to place any code you want executed whenever the form is unloaded. Here's an example:
Private Sub Form_Unload(Cancel As Integer)
' If EnterorEditProducts form is loaded,
' select it, requery CategoryID combo box,
' and set value of CategoryID combo box.
Dim ctl As Control
If IsLoaded("EnterOrEditProducts") Then
Set ctl = Forms!EnterOrEditProducts!cboCategoryID
DoCmd.SelectObject acForm, "EnterOrEditProducts"
ctl.Requery
ctl = Me!CategoryID
End If
End SubThis code is placed in the Load event of the AddCategory form from the Solutions database that's included with Access. It checks whether the EnterOrEditProducts form is loaded. If it is, the EnterOrEditProducts form is selected, the CategoryID combo box is requeried, and the combo box's value is set equal to the CategoryID of the AddCategory form.
The Close event occurs when a form is closed and removed from the screen, after the Unload event. Remember, you can cancel the Unload event but not the Close event.
The following code is placed in the Close event of the Suppliers form that's part of the NorthWind database:
Private Sub Form_Close()
' Close Product List form and Products form if they are open.
If IsLoaded("Product List") Then DoCmd.Close acForm, "Product List"
If IsLoaded("Products") Then DoCmd.Close acForm, "Products"
End SubWhen the Suppliers form is closed, the code tests whether the Product List and Products forms are open. If they are, it closes them.
The Activate event takes place when the form gets focus and becomes the active window. It's triggered when the form is opened, when a user clicks on the form or one of its controls, and when the SetFocus method is applied by using VBA code. The following code, found in the Activate event of the Time and Billing application's frmClients form, requeries the fsubClients subform whenever the frmClients main form is activated:
Private Sub Form_Activate()
Me![fsubClients].Requery
End SubThe Deactivate event occurs when the form loses focus, which happens when a table, query, form, report, macro, module, or the Database window becomes active. However, the Deactivate event isn't triggered when a dialog, pop-up form, or another application becomes active. The following example is code from the EnterOrEditProducts form that's part of the Solutions database:
Private Sub Form_Deactivate()
' Use AllowEdits property setting to determine which toolbar to hide.
' Show Form View toolbar.
If Me.AllowEdits = True Then
DoCmd.ShowToolbar "Enter Or Edit Products 2", acToolbarNo
Else
DoCmd.ShowToolbar "Enter Or Edit Products 1", acToolbarNo
End If
DoCmd.ShowToolbar "Form View", acToolbarWhereApprop
End SubThis code evaluates the AllowEdits property to determine which custom toolbar is currently active. It hides the appropriate toolbar and shows the standard Form View toolbar.
The GotFocus event happens when a form gets focus, but only if there are no visible, enabled controls on the form. This event is rarely used for a form.
The LostFocus event occurs when a form loses focus, but only if there are no visible, enabled controls on the form. This event, too, is rarely used for a form.
The Click event takes place when the user clicks on a blank area of the form, on a disabled control on the form, or on the form's record selector.
The DblClick event happens when the user double-clicks on a blank area of the form, on a disabled control on the form, or on the form's record selector.
The MouseDown event occurs when the user clicks on a blank area of the form, on a disabled control on the form, or on the form's record selector, but it happens before the Click event fires. It can be used to determine which mouse button was pressed.
The MouseMove event takes place when the user moves the mouse over a blank area of the form, over a disabled control on the form, or over the form's record selector. It's generated continuously as the mouse pointer moves over the form. The MouseMove event occurs before the Click event fires.
The MouseUp event occurs when the user releases the mouse button. Like the MouseDown event, it happens before the Click event fires and can be used to determine which mouse button was pressed.
The KeyDown event happens if there are no controls on the form or if the form's KeyPreview property is set to Yes; if the latter condition is true, all keyboard events are previewed by the form and occur for the control that has focus. If the user presses and holds down a key, the KeyDown event occurs repeatedly until the key is released.
Like the KeyDown event, the KeyUp event occurs if there are no controls on the form or if the form's KeyPreview property is set to Yes. The KeyUp event takes place only once, though, regardless of how long the key is pressed. The keystroke can be canceled by setting KeyCode to zero.
The KeyPress event occurs when the user presses and releases a key or key combination that corresponds to an ANSI code. It takes place if there are no controls on the form or if the form's KeyPreview property is set to Yes. The keystroke can be canceled by setting KeyCode to zero.
The Error event is triggered whenever an error happens while the user is in the form. Microsoft Jet Engine errors are trapped, but Visual Basic errors aren't. This event can be used to suppress the standard error messages. Visual Basic errors must be handled by using standard On Error techniques. Both the Error event and handling Visual Basic errors are covered in Chapter 17, “Handling Those Dreaded Runtime Errors.”
The Filter event takes place whenever the user selects the Filter By Form or Advanced Filter/Sort options. You can use this event to remove the previous filter, enter default settings for the filter, invoke your own custom filter window, or prevent certain controls from being available in the Filter By Form window.
The ApplyFilter event occurs when the user selects the Apply Filter/Sort, Filter By Selection, or Remove Filter/Sort options. It also takes place when the user closes the Advanced Filter/Sort window or the Filter By Form window. You can use this event to make sure the filter being applied is correct, to change the form's display before the filter is applied, or to undo any changes you made when the Filter event occurred.
The Timer event occurs at regular intervals, but only when the form's TimerInterval property is set. How often the Timer event triggers depends on the value set in the TimerInterval property.
One of the mysteries of events is the order in which they occur. One of the best ways to figure this out is to place Debug.Print statements in the events you want to learn about. This technique is covered in Chapter 16, “Debugging: Your Key to Successful Development.” Keep in mind that event order isn't an exact science; it's nearly impossible to guess when events will happen in all situations. It's helpful, though, to understand the basic order in which certain events do take place.
When the user opens a form, the following events occur:
Open Load Resize Activate Current
After these Form events take place, the Enter and GotFocus events of the first control occur. Remember that the Open event is the only place you can cancel opening the form.
When the user closes a form, the following events take place:
Unload Deactivate Close
Before these events occur, the Exit and LostFocus events of the active control are triggered.
When the user resizes a form, what happens depends on whether the form is minimized, restored, or maximized. When the form is minimized, here's what happens:
Resize Deactivate
When the user restores a minimized form, these events take place:
Activate Resize
When the user maximizes a form or restores a maximized form, then just the Resize event occurs.
When the user moves from one form to another, the Deactivate event occurs for the first form, then the Activate event occurs for the second form. Remember that the Deactivate event doesn't take place if focus moves to a dialog box, a pop-up form, or another application.
When the user types a character and the form's KeyPreview property is set to True, the following events occur:
KeyDown KeyPress Change KeyUp
If you trap the KeyDown event and set the KeyCode to zero, the remaining events never happen. The KeyPress and Change events capture only ANSI keystrokes. These events are the easiest to deal with; however, you must handle the Keydown and KeyUp events when you need to trap for non-ANSI characters, such as Shift, Alt, and Ctrl.
Sections have only five events: Click, DblClick, MouseDown, MouseMove, and MouseUp. These events rarely play a significant role in your application.
Each control type has its own set of events it responds to. Many events are common to most controls, but others are specific to certain controls. Furthermore, some controls respond to very few events. The following sections cover all the Control events. As each event is covered, you learn which controls the event applies to.
The BeforeUpdate event applies to text boxes, option groups, combo boxes, list boxes, and bound object frames. It occurs before changed data in the control is updated. The following code example is found in the BeforeUpdate event of the SelectProduct control on the EditProducts form from the Solutions database:
Private Sub SelectProduct_BeforeUpdate(Cancel As Integer)
If IsNull([SelectProduct]) Then
MsgBox "You must select a product."
DoCmd.CancelEvent
End If
End SubThis code tests whether the value of the SelectProduct control is Null. If it is, a message box is displayed, and the Update event is canceled.
The AfterUpdate event applies to text boxes, option groups, combo boxes, list boxes, and bound object frames. It occurs after changed data in the control is updated. The following code example is from the AfterUpdate event of the SelectProduct control on the EditProducts form found in the Solutions database:
Private Sub SelectProduct_AfterUpdate()
' Find record for product selected in SelectProduct combo box.
' Enable controls in detail section and disable ProductID text box.
' Go to SupplierID combo box.
Dim varTmp As Variant
DoCmd.ApplyFilter , "ProductID = Forms!EditProducts!SelectProduct"
varTmp = EnableControls("Detail", True)
Me!ProductID.Enabled = False
Me!SupplierID.SetFocus
End SubThis code begins by applying a filter to the form based on the product selected from the SelectProduct combo box. It then runs a function that enables all the controls in the form's Detail section. It disables the ProductID control and sets focus to the SupplierID control.
The Updated event applies to a bound object frame only. It occurs when the OLE (Object Linking and Embedding) object's data has been modified.
The Change event applies to text and combo boxes and takes place when data in the control changes. For a text box, this event occurs when a character is typed; for a combo box, it happens when a value is selected from the list. You use this event when you want to trap for something happening on a character-by-character basis.
The NotInList event applies only to a combo box and happens when the user enters a value in the text box portion of the combo box that's not in the combo box list. By using this event, you can allow the user to add a new value to the combo box list. For this event to be triggered, the LimitToList property must be set to Yes. Here's an example from the Time and Billing application's frmPayments form:
Private Sub cboPaymentMethodID_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End SubThis code is executed when the user enters a payment method that's not in the cboPaymentMethodID combo box. It instructs the user to double-click to add the entry as a payment method and suppresses the normal error message. The NotInList event is covered in more detail later in the “Handling the NotInList Event” section.
The Enter event applies to text boxes, option groups, combo boxes, list boxes, command buttons, object frames, and subforms. It occurs before a control actually gets focus from another control on the same form and takes place before the GotFocus event. Here's an example from the Time and Billing application's frmTimeCards form:
Private Sub fsubTimeCards_Enter()
If IsNull(Me![EmployeeID]) Then
MsgBox "Enter employee before entering time or expenses."
DoCmd.GoToControl "cboEmployeeID"
End If
End SubWhen the user moves into the fsubTimeCards subform control, its Enter event tests whether the EmployeeID has been entered on the main form. If it hasn't, a message box is displayed, and focus is moved to the cboEmployeeID control on the main form.
The Exit event applies to text boxes, option groups, combo boxes, list boxes, command buttons, object frames, and subforms. It occurs just before the LostFocus event.
The GotFocus event applies to text boxes, toggle buttons, options buttons, checkboxes, combo boxes, list boxes, and command buttons. It takes place when focus moves to a control in response to a user action or when the SetFocus, SelectObject, GoToRecord, GoToControl, or GoToPage methods are issued in code. Controls can get focus only if they're visible and enabled.
The LostFocus event applies to text boxes, toggle buttons, options buttons, checkboxes, combo boxes, list boxes, and command buttons. It occurs when focus moves away from a control in response to a user action or when the SetFocus, SelectObject, GoToRecord, GoToControl, or GoToPage methods are issued in code.
The Click event applies to labels, text boxes, option groups, combo boxes, list boxes, command buttons, and object frames. It occurs when the user presses, then releases a mouse button over a control. Here's an example from the Time and Billing application's frmProjects form:
Private Sub cmdToggleView_Click()
If Me![cmdToggleView].Caption = "&View Expenses" Then
Me![fsubProjects].Visible = False
Me![fsubProjectExpenses].Visible = True
Me![cmdToggleView].Caption = "&View Hours"
Else
Me![fsubProjectExpenses].Visible = False
Me![fsubProjects].Visible = True
Me![cmdToggleView].Caption = "&View Expenses"
End If
End SubThis code checks the caption of the cmdToggleView command button. If the caption reads "&View Expenses" (with the ampersand indicating a hotkey), the fsubProjects subform is hidden, the fsubProjectExpenses subform is made visible, and the caption of the cmdToggleView command button is modified to read "&View Hours". Otherwise, the fsubProjectsExpenses subform is hidden, the fsubProjects subform is made visible, and the caption of the cmdToggleView command button is modified to read "&View Expenses".
The DblClick event applies to labels, text boxes, option groups, combo boxes, list boxes, command buttons, and object frames. It occurs when the user presses, then releases the left mouse button twice over a control. Here's an example from the Time and Billing application's frmPayments form:
Private Sub cboPaymentMethodID_DblClick(Cancel As Integer)
Dim lngPaymentMethodID As Long
If IsNull(Me![cboPaymentMethodID]) Then
Me![cboPaymentMethodID].Text = ""
Else
lngPaymentMethodID = Me![cboPaymentMethodID]
Me![cboPaymentMethodID] = Null
End If
DoCmd.OpenForm "frmPaymentMethods", , , , , acDialog, "GotoNew"
Me![cboPaymentMethodID].Requery
If lngPaymentMethodID <> 0 Then Me![cboPaymentMethodID] = lngPaymentMethodID
End SubIn this example, the code evaluates the cboPaymentMethodID combo box control to see whether it's Null. If it is, the text of the combo box is set to a zero-length string. Otherwise, a long integer variable is set equal to the combo box value, and the combo box value is set to Null. The frmPaymentMethods form is opened modally. When it's closed, the cboPaymentMethods combo box is requeried. If the long integer variable doesn't contain a zero, the combo box value is set equal to the long integer value.
The MouseDown event applies to labels, text boxes, option groups, combo boxes, list boxes, command buttons, and object frames. It takes place when the mouse button is pressed over a control, before the Click event fires.
The MouseMove event applies to labels, text boxes, option groups, combo boxes, list boxes, command buttons, and object frames. It occurs as the mouse is moved over a control.
The MouseUp event applies to labels, text boxes, option groups, combo boxes, list boxes, command buttons, and object frames. It occurs when the mouse is released over a control, before the Click event fires.
The KeyDown event applies to text boxes, toggle buttons, option buttons, checkboxes, combo boxes, list boxes, and bound object frames. It happens when the user presses a key while within a control and occurs repeatedly until the key is released. It can be canceled by setting KeyCode equal to zero.
The KeyUp event applies to text boxes, toggle buttons, option buttons, checkboxes, combo boxes, list boxes, and bound object frames. It occurs when a key is released within a control. It occurs only once, no matter how long a key is pressed down.
The KeyPress event applies to text boxes, toggle buttons, option buttons, checkboxes, combo boxes, list boxes, and bound object frames. It occurs when the user presses and releases an ANSI key while the control has focus. It can be canceled by setting KeyCode equal to zero.
Just as Form events take place in a certain sequence when the form is opened, activated, and so on, Control events occur in a specific sequence. When writing the event code for a control, you need to understand the order in which Control events happen.
When focus is moved to a control, the following events occur:
Enter GotFocus
If focus is moving to a control as the form is opened, the Form and Control events take place in the following sequence:
Open(form) Activate(form) Current(form) Enter(control) GotFocus(control)
When focus leaves a control, the following events occur:
Exit LostFocus
When focus leaves the control because the form is closing, the following events happen:
Exit(control) LostFocus(control) Unload(form) Deactivate(form) Close(form)
When you change data in a control and then move focus to another control, the following events occur:
BeforeUpdate AfterUpdate Exit LostFocus
After every character that's typed in a text or combo box, the following events take place before focus is moved to another control:
KeyDown KeyPress Change KeyUp
For a combo box, if the NotInList event is triggered, it occurs after the KeyUp event.
The Me keyword is like an implicitly declared variable; it's available to every procedure in a Form or Report module. Using Me is a great way to write generic code in a form or report. You can change the name of the form or report, and the code will be unaffected. Here's an example:
Me.RecordSource = "qryProjects"
It's also useful to pass Me (the current form or report) to a generic procedure in a module, as shown in the following example:
Call ChangeCaption(Me)
The ChangeCaption procedure looks like this:
Sub ChangeCaption(frmAny As Form)
If IsNull(frmAny.Caption) Then
frmAny.Caption = "Form For - " & CurrentUser
Else
frmAny.Caption = frmAny.Caption & " - " & CurrentUser
End If
End SubThe ChangeCaption procedure in a Code module receives any form as a parameter. It evaluates the caption of the form that was passed to it. If the caption is Null, ChangeCaption sets the caption to "Form for -", concatenated with the user's name. Otherwise, it takes the existing caption of the form passed to it and appends the user's name.
You can design a variety of forms with Microsoft Access. By working with the properties available in Access's form designer, you can create forms with many different looks and types of functionality. This chapter covers all the major categories of forms, but remember that you can create your own forms. Of course, don't forget to maintain consistency with the standards for Windows applications.
One of the most common types of forms allows you to view one record at a time. The form shown in Figure 13.1, for example, lets the user view one customer record, then move to other records as needed.
Creating a Single form is easy—simply set the form's Default View property to Single Form. (See Figure 13.2.)
Often, the user wants to be able to view multiple records at a time. which requires creating a Continuous form, like the one shown in Figure 13.3. To do this, just set the Default View property to Continuous Forms.
A subform is a common use of a Continuous form; generally, you should show multiple records in a subform. The records displayed in the subform are all the records that relate to the record displayed in the main form. An example is pictured in Figure 13.4, which shows two subforms, each with its Default View property set to Continuous Forms. One subform shows all the orders relating to a specific customer, and the other shows all the order detail items for the selected order.
Scarcity of screen real-estate is a never-ending problem, but a multipage form can be a good solution. Figures 13.5 and 13.6 show the two pages of the multipage Employee form, which can be found in the Northwind.MDB database. When looking at the form in Design view, you can see a Page Break control placed just before the 3-inch mark on the form. (See Figure 13.7.) To insert a Page Break control, select it from the toolbox, then click and drag to place it on the form.
When creating a multipage form, remember a few important steps:
Set the Default View property of the form to
Single Form.Set the Scrollbars property of the form to
NeitherorHorizontal Only.Set the AutoResize property of the form to
No.Place the Page Break control exactly halfway down the form's Detail section if you want the form to have two pages. If you want more pages, divide the total height of the Detail section by the number of pages and place Page Break controls at the appropriate positions on the form.
A tabbed form is an alternative to a multipage form. Access 97 includes a built-in Tab control that allows you to easily group sets of controls together. A tabbed form could, for example, show customers on one tab, orders for the selected customer on another tab, and order detail items for the selected order on a third tab.
The form shown in Figure 13.8 uses a Tab control. This form, called frmTabbed, is included in the CHAP13EX.MDB database on your sample code CD-ROM. It shows clients on one tab and the selected client's projects on the second tab. No code is needed to build the example.
To add a Tab control to a form, simply select it from the toolbox and drag and drop it onto the form. By default, two tab pages appear. To add more tabs, right-click the control and select Insert Page. The new page is inserted before the selected page. To remove tabs, right-click the page you want to remove and select Delete Page. To change the order of pages, right-click any page and select Page Order.
You can add controls to each tab just as you would add them directly to the form. However, remember to select a tab by clicking it before you add the controls. If you don't select a specific tab, the controls you add will appear on every page.
The controls on each page have their own tab order. To modify their tab order, right-click the page and select Tab Order. You can then reorder the controls in whatever way you want on the page.
To change the properties of the Tab control, click to select it rather than a specific page. You can tell whether you've selected the Tab control because the words Tab Control appear in the upper-left corner of the Properties window. (See Figure 13.9.) A Tab control's properties include its name, the text font on the tabs, and more.
To change the properties of each page, select a specific page of the Tab control. You can tell whether you've selected a specific page because the word Page is displayed in the upper-left corner of the Properties window. (See Figure 13.10.) Here you can select a name for the page, the page's caption, a picture for the page's background, and more.
A Switchboard form is a great way to control your application. The Switchboard Manager, a tool designed to help you create switchboards, is covered in Chapter 37, “Distributing Your Application with ODE.” In this section, you learn how to create a custom Switchboard form. A Switchboard form is simply a form with command buttons that navigate you to other Switchboard forms or to the forms and reports that make up your system.
The form shown in Figure 13.11 is a Switchboard form. Each tab on the form lets the user work with different components of the database. What differentiates a Switchboard form from other forms is that its purpose is limited to navigating through the application. It usually has a border style of Dialog, and it has no scrollbars, record selectors, or navigation buttons. Other than these characteristics, a Switchboard form is a normal form. There are many styles of Navigation forms; which one you use depends on your users' needs.
Splash screens add professional polish to your applications and give your users something to look at while your programming code is setting up the application. Just follow these steps to create a Splash Screen form:
Create a new form.
Set the Scrollbars property to
Neither, the Record Selectors property toNo, the Navigation Buttons property toNo, the Auto Resize property toYes, the AutoCenter property toYes, and the Border Style toNone.Make the form Popup and Modal.
Add a picture to the form and set the picture's properties.
Add any text you want to put on the form.
Set the form's timer interval to the number of seconds you want the splash screen to be displayed.
Code the form's
Timerevent forDoCmd.Close.Code the form's
Unloadevent to open your main Switchboard form.
Because the Timer event of the Splash Screen form closes the form after the amount of time specified in the timer interval, the Splash Screen form unloads itself. While it's unloading, it loads the Switchboard form. The Splash Screen form included in CHAP13EX.MDB is called frmSplash. When it unloads, it opens the frmSwitchboard form.
You can implement your Splash Screen form in many other ways. For example, you can call the Splash Screen form from a Startup form; its Open event simply needs to open the Splash Screen form. The problem with this method is that if your application loads and unloads the switchboard while the application is running, the splash screen is displayed again.
Another popular method is to build a function that's called from an AutoExec macro. This Startup function can display the splash screen, execute all the tasks needed to set up your application, then unload the splash screen. Here's an example that opens the frmSplash form:
Function AutoExec() DoCmd.OpenForm "frmSplash" DoCmd.Hourglass True '*** Code to set up your application is placed here *** '*** End of Setup Code *** DoCmd.OpenForm "frmSwitchboard" DoCmd.Close acForm, "frmSplash" DoCmd.Hourglass False End Function
This code then displays an hourglass and continues with any setup processing. When it's done with all the setup processing, it opens the frmSwitchboard form, closes the splash screen, and gets rid of the hourglass.
Tip
You can also display a splash screen by including a bitmap file with the same name as your database (MDB) in the same directory as the database file. When the application is loaded, the splash screen is displayed for a couple of seconds. The only disadvantage to this method is that you have less control over when, and how long, the splash screen is displayed.
Dialog forms are typically used to gather information from the user. What makes them Dialog forms is that they're modal, meaning that the user can't go ahead with the application until the form is handled. Dialog forms are generally used when you must get specific information from your user before your application can continue processing. A custom Dialog form is simply a regular form that has a Dialog border style and has its Modal property set to Yes. Remember to give users a way to close the form; otherwise, they might close your modal form with the famous “Three-Finger Salute” (Ctrl+Alt+Del) or, even worse, by using the PC's Reset button. The frmArchivePayments form in CHAP13EX.MDB is a custom Dialog form.
Access comes with two built-in dialog boxes: the standard Windows message box and the input box. The ODE also includes the Common Dialog OLE control, which gives you access to other commonly used dialog boxes.
A message box is a predefined dialog box that you can incorporate into your applications; however, it can be customized by using parameters. The VBA language has a MsgBox statement—that just displays a message—and a MsgBox function, which can display a message and return a value based on the user's response.
The message box in the VBA language is the same message box that's standard in most Windows applications, so it's already familiar to most Windows users. Rather than create your own dialog boxes to get standard responses from your users, you can use an existing, standard interface.
The MsgBox statement receives five parameters: the message, the type of icon you want, the message box's title, and the Help file and context ID you want available if the user selects Help while the dialog box is displayed. The MsgBox statement looks like this:
MsgBox "This is a Message", vbInformation, "This is a Title"
This example displays the message "This is a Message" and the information icon. The title for the message box is "This is a Title". The message box also has an OK button that's used to close the dialog box.
The MsgBox statement is normally used to display just an OK button, but the MsgBox function lets you select from a variety of standard button combinations and returns a value indicating which button the user selected. The MsgBox function receives the same five parameters as the MsgBox statement. The first parameter is the message you want to display, and the second is a numeric value indicating which buttons and icon you want to display. Tables 13.1 and 13.2 list the values that can be numerically added to create the second parameter. The intrinsic constants in the table can be substituted for the numeric values, if you like.
Table 13.1. Values indicating the buttons you want to display.
| Buttons | Value | Intrinsic Constant |
|---|---|---|
| OK button only | 0 | vbOKOnly |
| OK and Cancel | 1 | vbOKCancel |
| Abort, Retry, and Ignore | 2 | vbAbortRetryIgnore |
| Yes, No, and Cancel | 3 | vbYesNoCancel |
| Yes and No | 4 | vbYesNo |
| Retry and Cancel | 5 | vbRetryCancel |
The values in Table 13.1 must be numerically added to one of the values in Table 13.2 if you want to include an icon other than the dialog box's default icon.
Table 13.2. Values indicating the icons you want to display.
| Icon | Value | Intrinsic Constant |
|---|---|---|
| Critical (Stop Sign) | 16 | vbCritical |
| Warning Query (Question) | 32 | vbQuestion |
| Warning Exclamation (!) | 48 | vbExclamation |
| Information (I) | 64 | vbInformation |
In the following example, the message box displays Yes, No, and Cancel buttons:
Sub MessageBoxFunction()
Dim intAnswer As Integer
intAnswer = MsgBox("Are You Sure?", vbYesNoCancel + vbQuestion, _
"Please Respond")
End SubThis message box also displays the Question icon. (See Figure 13.12.) The Function call returns a value stored in the Integer variable iAnswer.
After you have placed the return value in a variable, you can easily introduce logic into your program to respond to the user's selection, as shown in this example:
Sub MessageBoxAnswer()
Dim intAnswer As Integer
intAnswer = MsgBox("Are You Sure?", vbYesNoCancel + vbQuestion, _
"Please Respond")
Select Case intAnswer
Case vbYes
MsgBox "I'm Glad You are Sure!!"
Case vbNo
MsgBox "Why Aren't You Sure??"
Case vbCancel
MsgBox "You Coward! You Bailed Out!!"
End Select
End SubThis code evaluates the user's response and displays a message based on his or her answer. Of course, in a real-life situation, the code in the Case statements would be more practical. Table 13.3 lists the values returned from the MsgBox function, depending on which button the user selected.
The InputBox function displays a dialog box containing a simple text box. It returns the text the user typed in the text box and looks like this:
Sub InputBoxExample()
Dim strName As String
strName = InputBox("What is Your Name?", _
"This is the Title", "This is the Default")
MsgBox "You Entered " & strName
End SubThis subroutine displays the input box shown in Figure 13.13. Notice that the first parameter is the message, the second is the title, and the third is the default value. The second and third parameters are optional.
As mentioned, the Common Dialog control is an OCX control that's included as part of the ODE. You can use it to display Windows common dialog boxes, including File Save, File Open, File Print, File Print Setup, Fonts, and Colors. The Common Dialog control is covered in Chapter 26, “Using ActiveX Controls.”
You can create custom menus to display with your forms and reports; there's no limit on how many you can use. Each menu can be attached to one or more forms or reports.
Quite often, you want to restrict what users can do while they're working with a form or report. By creating a custom menu, you can restrict and customize what they're allowed to do.
In previous versions of Access, you created a custom menu bar by setting the MenuBar property to the name of a menu bar macro. This function is supported for backward compatibility only. In Access 97, custom menu bars, toolbars, and pop-up menus are all referred to as command bars. To create any of these three objects, choose View | Toolbars and then select Customize. Once a custom menu bar, toolbar, or pop-up menu has been created, you can easily associate it with forms and reports by using the Menubar, Toolbar, and Shortcut Menu Bar properties.
Follow these steps to create a custom menu bar:
Choose View | Toolbars and click Customize, or right-click any toolbar and select Customize.
When the Customize dialog box opens, click New. (See Figure 13.14.)
Assign a name to the new menu bar, toolbar, or pop-up menu. The new command bar then appears.
Click the Properties button on the Customize dialog box to view the properties for your newly created toolbar. In the Toolbar Properties dialog box, you name the toolbar, select the toolbar type, indicate the type of docking that's allowed, and set other options for the command bar. The Type drop-down list allows you to select Menu Bar, Toolbar, or Popup. The Docking options are Allow Any, Can't Change, No Vertical, and No Horizontal. You can also choose whether the user will be allowed to customize or move the menu or toolbar.
Select the options you want and click Close.
Now you're ready to add items to the toolbar, menu bar, or pop-up menu. The process differs slightly, depending on which type of command bar you selected. To add items to a command bar, click the Commands tab of the Customize dialog box, shown in Figure 13.15, and drag and drop command icons onto your new command bar.
Here are some tips to help you create custom menu bars, toolbars, and pop-up menus:
To add an entire built-in menu to the menu bar, select Built-in Menus from the Categories list box. Click and drag a menu pad from the Commands list box over to the menu bar to add the entire built-in menu pad to the custom menu.
To create a custom menu pad, select New Menu from the Categories list box. Click and drag the New Menu option to the menu bar. To modify the text on the menu pad, right-click the menu pad and type a new value in the Name text box.
To add a built-in command to the menu, select a category from the Categories list box, then click and drag the appropriate command to the menu pad. The new item will appear underneath the menu pad.
To add a separator bar to a menu, right-click on the menu item that will follow the separator bar and select Begin a Group. To remove the separator bar, select Begin a Group again.
Menu items can contain Text Only or Image and Text. To select one, right-click a menu item and select Default Style, Text Only (Always), Text Only (in Menus), or Image and Text. To customize an image, right-click a menu item and select Change Button Image. Choose one of the available images. To modify the button image, right-click a menu item and select Edit Button Image; this opens the Button Editor dialog box. (See Figure 13.16.) If you want to reset the button to its original image, right-click the menu item and select Reset Button Image.
If you want to modify several properties of a menu item at once, you can right-click the menu item and select Properties to open the Menu Bar Control Properties dialog box. (See Figure 13.17.) Here you can select attributes for the menu item, such as the Caption, Tooltip, Style, Help File, and Help Context ID. You can also associate an action with a custom menu item (covered in the next section).
In Access, it's easy to customize your menus with both built-in commands and custom-built functions. For built-in commands, you can simply drag and drop commands onto your command bars. To have a command bar item run a custom-built function, you need to create a custom item and set its properties, as explained in the following steps:
Select the File category from the Categories list box in the Customize dialog box.
Click and drag the Custom option from the Commands list box to the position you want for the menu.
Right-click the new menu item and select Properties.
Type the name of the function or subroutine you want to call in the On Action drop-down list. If the procedure you're calling is a function, you must precede the function name with an equal sign and include any parameters in parentheses following the function name.
Click Close to close the Control Properties dialog box.
Click Close to close the Customize dialog box.
Command bars can be added, modified, and removed by using VBA code, which allows you to build flexibility into your application. You can easily modify a command bar in response to different conditions in your application. You can even give your user a front-end to customize the command bars in your application, as shown in this example:
Sub CreateCustomCommandBar()
On Error Resume Next
Dim cbr As CommandBar
Dim btn As CommandBarButton
Set cbr = CommandBars("My Command Bar")
If Err <> 0 Then
Set cbr = CommandBars _
.Add(Name:="My Command Bar", Position:=msoBarTop)
End If
Set btn = cbr.Controls("Get Initials")
If Err <> 0 Then
' Custom button doesn't already exist, so create it.
Set btn = cbr.Controls.Add(msoControlButton, , , , True)
End If
With btn
.Caption = "Are You Sure?"
.BeginGroup = True
.OnAction = "MessageBoxAnswer"
.Style = msoButtonCaption
End With
End SubThis code illustrates that by using the VBA language, you have full control over command bar objects. It begins by creating CommandBar and CommandBarButton object variables, then sets the CommandBar object variable to a command bar called “My Command Bar.” If this causes an error, then you know that the “My Command Bar” command bar doesn't exist. The Add method is used to add the command bar, which will be placed at the top of the screen. The routine then tries to point at a command bar button called “Are You Sure?”. If this causes an error, the Add method of the Controls collection of the CommandBar object is used to add a command button to the collection. The button's caption is set to "Are You Sure?", a group is added, and the command button's action is to call the subroutine MessageBoxAnswer. The command button's style is set to display just a caption.
Access has several form-filtering features that are part of the user interface. You can opt to include these features in your application, omit them from your application entirely, or control their behavior. For your application to control their behavior, it needs to respond to the Filter event, which it does by detecting when a filter is placed on the data in the form. When it has detected the filter, the code in the Filter event executes.
Sometimes you might want to alter the standard behavior of a filter command. You might want to display a special message to the user, for example, or take a specific action in your code. You might also want your application to respond to a Filter event because you want to alter the form's display before the filter is applied. For example, if a certain filter is in place, you may want to hide or disable certain fields. When the filter is removed, you could then return the form's appearance to normal.
Fortunately, Access not only lets you know that the Filter event occurred, but it also lets you know how the filter was invoked. Armed with this information, you can intercept and change the filtering behavior as needed.
When a user chooses Filter By Form or Advanced Filter/Sort, the FilterType parameter is filled with a value that indicates how the filter was invoked. If the user invokes the filter by selecting Filter By Form, the FilterType parameter equals the constant acFilterByForm; however, if he or she selects Advanced Filter/Sort, the FilterType parameter equals the constant acFilterAdvanced. The following code demonstrates how to use these constants:
Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
Select Case FilterType
Case acFilterByForm
MsgBox "You Just Selected Filter By Form"
Case acFilterAdvanced
MsgBox "You Are Not Allowed to Select Advanced Filter/Sort"
Cancel = True
End Select
End SubThis code, placed in the form's Filter event, evaluates the filter type. If Filter By Form was selected, a message box is displayed and the filtering proceeds as usual, but if the user selects Advanced Filter/Sort, she's told she can't do this and the filter process is canceled.
Not only can you check how the filter was invoked, but you can also intercept the process when the filter is applied. You do this by placing code in the form's ApplyFilter event, as shown in this example:
Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
Dim intAnswer As Integer
If ApplyType = acApplyFilter Then
intAnswer = MsgBox("You just selected the criteria: & _
Chr(13) & Chr(10) & Me.Filter & _
Chr(13) & Chr(10) & Are You Sure You Wish to Proceed?", _
vbYesNo + vbQuestion)
If intAnswer = vbNo Then
Cancel = True
End If
End If
End SubThis code evaluates the value of the ApplyType parameter. If it's equal to the constant acApplyFilter, a message box is displayed, verifying that the user wants to apply the filter. If the user responds Yes, the filter is applied; otherwise, the filter is canceled.
Microsoft Access is an OLE client application, meaning it can contain objects from other applications. Access 97 is also an OLE server application. Using Access as an OLE server is covered in Chapter 25, “Automation: Communicating with Other Applications.” Access's ability to control other applications with programming code is also covered in Chapter 25. In the following sections, you learn how to link to and embed objects in your Access forms.
Bound OLE objects are tied to the data in an OLE field within a table in your database. An example is the Photo field that's part of the Employees table in the NorthWind database. The field type of the Employees table that supports multimedia data is OLE object. This means that each record in the table can contain a unique OLE object. The Employees form contains a bound OLE control whose control source is the Photo field from the Employees table.
If you double-click on the photo of an employee, the OLE object can be edited In-Place. The picture of the employee is actually embedded in the Employees table. This means that the data associated with the OLE object is actually stored as part of the Access database (MDB) file, within the Employees table. Embedded objects, if they support the OLE 2.0 standard, can be modified In-Place. This is called In-Place activation.
To insert a new object, take the following steps:
Move to the record that will contain the OLE object.
Right-click on the OLE Object control and select Insert Object to open the Insert Object dialog box.
Select an object type. Select Create New if you want to create an embedded object, or select Create from File if you want to link to or embed an existing file.
If you select Create from File, the Insert Object dialog box changes to look like the one shown in Figure 13.18.
Select Link if you want to link to the existing file. Don't click Link if you want to embed the existing file. If you link to the file, the Access table will have a reference to the file as well as presentation data (a bitmap) for the object. If you embed the file, Access copies the original file, placing the copy in the Access table.
Click Browse and select the file you want to link to or embed.
Click OK.
If you double-click on a linked object, you launch its source application; you don't get In-Place activation. (See Figure 13.19.)
The OpenArgs property gives you a way to pass information to a form as it's being opened. The OpenArgs argument of the OpenForm method is used to populate a form's OpenArgs property at runtime. It works like this:
DoCmd.OpenForm "frmPaymentMethods", , , , , acDialog, "GotoNew"
This code is found in the Time and Billing application's frmPayments form. It opens the frmPaymentMethods form when a new method of payment is added to the cboPaymentMethodID combo box. It sends the frmPaymentMethods form an OpenArg of "GotoNew". The Load event of the frmPaymentMethods form looks like this:
Private Sub Form_Load()
If Me.OpenArgs = "GotoNew" And Not IsNull(Me![PaymentMethodID]) Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End SubThis code evaluates the form's OpenArgs property, moving to a new record if the OpenArgs property contains the text string "GoToNew" and the PaymentMethodID of the current record isn't Null. The OpenArgs property can be evaluated and used anywhere in the form.
Many developers don't realize how easy it is to switch a form's record source at runtime. This is a great way to use the same form to display data from more than one table or query containing the same fields. It's also a great way to limit the data that's displayed in a form at a particular moment. Using the technique of altering a form's record source at runtime, you can dramatically improve performance, especially for a client/server application. Here's an example found in the ShowSales form of the Solutions database. (See Figure 13.20 and Listing 13.1.)
Example 13.1. Altering a form's record source at runtime.
Private Sub ShowSales_Click()
' Create an SQL statement using
' search criteria entered by user and
' set RecordSource property of ShowSalesSubform.
Dim strSQL As String, strRestrict As String
Dim lngX As Long
Dim varTmp As Variant
lngX = Forms!ShowSales!Sales.Value
strRestrict = ShowSalesValue(lngX)
' Create SELECT statement.
strSQL = "SELECT DISTINCTROW Customers.CompanyName, " & _
"OrderSubtotals.OrderID, "
strSQL = strSQL & "OrderSubtotals.Subtotal , " & _
"Orders.ShippedDate "
strSQL = strSQL & "FROM Customers INNER JOIN " & _
"(OrderSubtotals INNER JOIN Orders ON "
strSQL = strSQL & "OrderSubtotals.OrderID = Orders.OrderID) ON "
strSQL = strSQL & "Customers.CustomerID = Orders.CustomerID "
strSQL = strSQL & "WHERE (Orders.ShippedDate Between " & _
"Forms!ShowSales!BeginningDate "
strSQL = strSQL & "And Forms!ShowSales!EndingDate) "
strSQL = strSQL & "And " & strRestrict
strSQL = strSQL & " ORDER BY OrderSubtotals.Subtotal DESC;"
' Set RecordSource property of ShowSalesSubform.
Me!ShowSalesSubform.Form.RecordSource = strSQL
' If no records match criteria,
' reset subform's RecordSource property,
' display message, and move focus to BeginningDate text box.
If Me!ShowSalesSubform.Form.RecordsetClone.RecordCount = 0 Then
Me!ShowSalesSubform.Form.RecordSource = _
"SELECT CompanyName FROM Customers WHERE False;"
MsgBox "No records match the criteria you entered.", _
48, "No Records Found"
Me!BeginningDate.SetFocus
Else
' Enable control in detail section.
varTmp = EnableControls("Detail", True)
' Move insertion point to ShowSalesSubform.
Me!ShowSalesSubform.SetFocus
End If
End Sub
Private Function ShowSalesValue(lngOptionGroupValue As Long) As String
' Return value selected in Sales option group.
' Define constants for option group values.
Const conSalesUnder1000 = 1
Const conSalesOver1000 = 2
Const conAllSales = 3
' Create restriction based on value of option group.
Select Case lngOptionGroupValue
Case conSalesUnder1000:
ShowSalesValue = "OrderSubtotals.Subtotal < 1000"
Case conSalesOver1000:
ShowSalesValue = "OrderSubtotals.Subtotal >= 1000"
Case Else
ShowSalesValue = "OrderSubtotals.Subtotal = True"
End Select
End FunctionThis code begins by storing the value of the Sales option group on the ShowSales main form into a Long Integer variable. It then calls the ShowSalesValue function, which declares three constants, then evaluates the parameter that was passed to it (the Long Integer variable containing the option group value). Based on the value of the option group, it builds a selection string for the subtotal value. This selection string becomes part of the SQL statement used for the subform's record source and limits the range of sales values displayed on the subform.
The ShowSales routine then builds a string containing a SQL statement, which selects all required fields from the Customers table and OrderSubtotals query. It builds a WHERE clause that includes the BeginningDate and EndingDate from the main form as well as the string returned from the ShowSalesValue function.
When the SQL statement has been built, the RecordSource property of the ShowSalesSubform control is set equal to the SQL statement. The RecordCount property of the RecordsetClone (the form's underlying recordset) is evaluated to determine whether any records meet the criteria specified in the record source. If the record count is zero, no records are displayed in the subform and the user is warned that no records met the criteria. However, if records are found, the form's Detail section is enabled, and focus is moved to the subform.
Combo boxes and list boxes are very powerful. Being able to properly respond to a combo box's NotInList event, to populate a combo box by using code, and to select multiple entries in a list box are essential skills of an experienced Access programmer. They're covered in detail in the following sections.
As discussed, the NotInList event occurs when a user types a value in the text box portion of a combo box that's not found in the combo box list. This event takes place only if the LimitToList property of the combo box is set to True. It's up to you whether you want to respond to this event.
You might want to respond with something other than the default error message when the LimitToList property is set to True and the user tries to add an entry. For example, if a user is entering an order and she enters the name of a new customer, you could react by displaying a message box asking whether she really wants to add the new customer. If the user responds affirmatively, you can display a customer form.
After you have set the LimitToList property to True, any code you place in the NotInList event is executed whenever the user tries to type an entry that's not found in the combo box. The following is an example:
Private Sub cboCompany_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
intAnswer = MsgBox("Company Does Not Exist. Add (Yes/No)", _
vbYesNo + vbQuestion)
End SubThe problem with this code is that it warns the user of the problem but doesn't rectify it; therefore, this code runs and then the default error handling kicks in.
The NotInList event procedure accepts a response argument, which is where you can tell VBA what to do after your code executes. Any one of the following three constants can be placed in the response argument:
acDataErrAdded: This constant is used if your code adds the new value into the record source for the combo box. This code requeries the combo box, adding the new value to the list.AcDataErrDisplay: This constant is used if you want VBA to display the default error message.AcDataErrContinue: This constant is used if you want to suppress VBA's own error message, using your own instead. Access still requires that a valid entry be placed in the combo box.
The following code, when placed in the NotInList event procedure of your combo box, displays a message asking the user whether she wants to add the customer. If the user responds No, she is returned to the form without the standard error message being displayed, but she still must enter a valid value in the combo box. If the user responds Yes, she is placed in the customer form, ready to add the customer whose name she typed.
Private Sub cboCompany_NotInList(NewData As String, Response As Integer)
Dim iAnswer As Integer
iAnswer = MsgBox("Company Does Not Exist. Add (Yes/No)", _
vbYesNo + vbQuestion)
If iAnswer = vbYes Then
DoCmd.OpenForm "frmCustomer", acNormal, , , acAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End SubIt's easy to populate a combo box or list box by setting the control's properties. This method is enough for many situations; however, there are times when you might want to populate a combo or list box programmatically—with values from an array, for example. You might also want to populate the box with table or report names or some other database component.
To populate a combo box or list box using code, you create a Callback function, which tells Access how many rows and columns will be in the box and what data will be used to fill the box. This function becomes the Row Source type for your combo box or list box. Access calls the function, then uses its information to populate the box. The following example is found in the CustomersDialog form that's part of the Solutions database:
Example 13.2. Filling a list box by using a Callback function.
Function lstForms_Fill(ctl As Control, lngID As Long, lngRow As Long, _
lngCol As Long, intCode As Integer) As Variant
Dim frm As Form_Customers
'Error if CustomerPhoneList form isn't open.
Const conFormNotOpen = 2450
On Error GoTo Fill_Error
Select Case intCode
Case acLBInitialize 'Initialize the listbox.
Set colCustomerForms = Forms![CustomerPhoneList].ReturnCollection
lstForms_Fill = True
Case acLBOpen 'Open.
lstForms_Fill = Timer
Case acLBGetRowCount 'Get the number of rows.
lstForms_Fill = colCustomerForms.Count
Case acLBGetColumnCount 'Get the number of columns.
lstForms_Fill = 1
Case acLBGetColumnWidth 'Get the column width.
lstForms_Fill = -1 'Use the default width.
Case acLBGetValue 'Get the data.
Set frm = colCustomerForms(lngRow + 1)
lstForms_Fill = frm![CompanyName]
End Select
Exit Function
Fill_Error:
If Err = conFormNotOpen Then
Exit Function
Else
MsgBox Err.Description
Exit Function
End If
End FunctionThe function must contain five predetermined arguments. The first argument must be declared as a control, and the remaining arguments must be declared as variants. The function itself must return a variant. The parameters are listed in Table 13.4.
Table 13.4. Five predetermined arguments of a Callback function.
| Argument | Description |
|---|---|
fld | A control variable that refers to the combo or list box being filled |
id | A unique value that identifies the control being filled. It's useful when you're using the same function to populate more that one combo or list box |
row | The row being filled (zero-based) |
col | The column being filled (zero-based) |
code | A value specifying the information being requested |
The List function is called several times. Each time it's called, Access automatically supplies a different value for the code, indicating the information it's requesting. The code item can have the values shown in Table 13.5.
Table 13.5. Code item values.
| Code | Intrinsic Constant | Meaning | Returns |
|---|---|---|---|
0 | acLBInitialize | Initialize | Nonzero if the function can fill the list; False or Null if a problem occurs |
1 | acLBOpen | Open | Nonzero ID value if the function can fill the list; False or Null if a problem occurs |
3 | acLBGetRowCount | Number of rows | Number of rows in the list |
4 | acLBGetColumnCount | Number of columns | Number of columns in the list |
5 | acLBGetColumnWidth | Column width | Width of the column specified |
6 | acLBGetValue | List entry | List entry to be displayed in the column and row specified |
7 | acLBGetFormat | Format string | Format string used to format the list entry |
8 | acLBClose | Not used | |
9 | acLBEnd | End (last call) | Nothing |
The function is automatically called once for codes 0, 1, 3, and 4. These calls initiate the process and determine the number of rows and columns that the combo or list box contains. The function is called twice for code 5: once to determine the total width of the box, and again to set the column width. The number of times that codes 6 and 7 are executed varies depending on the number of rows contained in the box (code 3). Code 9 is called when the form is closed or the combo box or list box is queried.
Armed with this knowledge, take a good look at the lstForms_Fill function, the Callback function that's used to populate the list box. The purpose of this function is to populate the list box with a list of forms opened by the Customer Phone List form, which allows multiple instances of the Customer form to be opened and added to a collection. When the user closes the Customer Phone List form, the CustomersDialog form is opened, asking the user which instances of the Customers form he or she wants to leave open.
The Callback function begins by creating a form object variable based on the Customers form. Each element of the case structure seen in the routine is called as each code is sent by Access. Here's what happens:
When Access sends the code of
0, thecolCustomerFormsvariable is set equal to theReturnCollectionmethod of the CustomerPhoneList form. ThisReturnCollectionmethod contains a collection of open Customer forms. The function then returns aTrue.When Access sends the code of
1, the return value is a unique value equal to the return value of theTimerfunction.When Access sends the code of
3, the return value is set equal to the count of forms in the colCustomerForms collection.When Access sends the code of
4, the return value is set to1(one column).When Access sends the code of
5, the return value is set to-1, forcing a default width for the combo or list box.Access then automatically calls code
6by the number of times that was returned for the number of rows in the combo box or list box. Each time code6is called, the form object variable is set equal to a different element of the form collection. The CompanyName from each form is returned from the function. The CompanyName return value is the value that's added to the list box.
All this work might seem difficult at first. After you have populated a couple of combo boxes or list boxes, though, it's quite easy. In fact, all you need to do is copy the case structure you see in the lstForms_Fill function and use it as a template for all your Callback routines.
Access 97 list boxes have a Multi-select property. When set to True, this property lets the user select multiple elements from the list box. Your code can then evaluate which elements are selected and perform some action based on the selected elements. The example in the previous section (“Populating a Combo or List Box with a Callback Function”) demonstrates the use of a Multi-select list box. The form shows all the instances of the Customer form that are currently open. It allows users to select which instances of the Customer form they want to keep open when the CustomersPhoneList form is closed. The code under the Click event of the OK button looks like this:
Example 13.3. Evaluating which items are selected in the multi-select list box.
Private Sub cmdOk_Click()
Dim intIndex As Variant
Dim frm As Form_Customers
Dim varFormName As Variant
Const conObjectRequired = 424
On Error GoTo Close_Error
'Set the value of the user-defined KeepMeAlive property
'of the forms that should stay open.
intIndex = 1
'Determine which listbox items are selected.
For Each intIndex In lstForms.ItemsSelected
Set frm = colCustomerForms(intIndex + 1)
frm.KeepMealive frm
Next intIndex
DoCmd.Close acForm, "CustomersDialog"
Exit Sub
Close_Error:
If Err = conObjectRequired Then
DoCmd.Close acForm, "CustomersDialog"
Exit Sub
Else
MsgBox Err.Description
Exit Sub
End If
End SubThis code uses the For Each...Next construct, along with the ItemsSelected property of the list box to loop through each selected item in the list box. The routine sets a form object variable equal to a specific form in the collection. The routine uses intIndex + 1 as the index in the collection because the collection of forms is one-based. The KeepMeAlive custom property of each form is set to True, and the CustomersDialog form is closed. The KeepMeAlive property, when set to True, makes sure the particular instance of the form is not closed.
Many new Access developers don't know the ins and outs of creating and modifying a subform and referring to subform controls, so first look at some important points you should know when working with subforms:
The easiest way to add a subform to a main form is to open the main form, then drag and drop the subform onto the main form.
The easiest way to edit a subform after it has been added to the main form is to double-click on the subform control within the main form. If your double-click isn't successful, you need to click off the subform object, then double-click on it again.
The subform control's LinkChildFields and LinkMasterFields properties determine which fields in the main form link to which fields in the subform. A single field name, or a list of fields separated by semicolons, can be entered into these properties. When they are properly set, these properties make sure all records in the child form relate to the currently displayed record in the parent form.
Many developers don't know how to properly refer to subform controls. You must refer to any objects on the subform through the subform control on the main form, as shown in this example:
Forms!frmCustomer!fsubOrders
This example refers to the fsubOrders control on the frmCustomer form. If you want to refer to a specific control on the fsubOrders subform, you can then point at its controls collection. Here's an example:
Forms!frmCustomer!fsubOrders!txtOrderID
You can also refer to the control on the subform implicitly, as shown in this example:
Forms!frmCustomer!subOrders!txtOrderID
Both of these methods refer to the txtOrderID control on the form in the fsubOrder control on the frmCustomer form. To change a property of this control, you would extend the syntax to look like this:
Forms!frmCustomer!fsubOrders!txtOrderID.Enabled = False
This code sets the Enabled property of the txtOrderID control on the form in the fsubOrders control to False.
A form's RecordsetClone property is used to refer to its underlying recordset. You can manipulate this recordset independently of what's currently being displayed on the form. Here's an example:
Private Sub cboCompany_AfterUpdate()
Me.RecordsetClone.FindFirst "[ClientID] = " & cboCompany.Value
If Me.RecordsetClone.NoMatch Then
MsgBox "Client Not Found"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End SubThis example issues the FindFirst method on the form's RecordsetClone. It searches for a record in the form's underlying recordset whose ClientID is equal to the current combo box value. If a match is found, the form's bookmark is synchronized with the bookmark of the form's underlying recordset. This code can be rewritten, using an object variable to point at the RecordsetClone:
Private Sub cboCompany_AfterUpdate()
Dim rs As Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[ClientID] = " & cboCompany.Value
If rs.NoMatch Then
MsgBox "Client Not Found"
Else
Me.Bookmark = rs.Bookmark
End If
End SubThis code creates an object variable that points at the form's RecordsetClone. The recordset object variable can then be substituted for Me.RecordsetClone, because it references the form's underlying recordset.
Forms and reports are called Class modules, which means they act as templates for objects you create instances of at runtime. Public procedures of a form and report become Custom properties and methods of the form object at runtime. Using VBA code, you can set the values of a form's Custom properties and execute its methods.
Custom properties of a form or report can be created in one of two ways:
Create Public variables in the form or report.
Create
PropertyLetandPropertyGetroutines.
The following steps are used to create and access a Custom form or report property based on a Public variable. The example is included in CHAP13EX.MDB in the forms frmPublicProperties and frmChangePublicProperty:
Begin by creating the form that will contain the Custom property (Public variable).
Place a Public variable in the General Declarations section of the form or report. (See Figure 13.21.)
Place code in the form or report that accesses the Public variable. The code in Figure 13.21 creates a Public variable called
CustomCaption. The code behind theClickevent of the cmdChangeCaption command button sets the form's (frmPublicProperties) Caption property equal to the value of the Public variable.Create a form, report, or module that modifies the value of the
Customproperty. Figure 13.22 shows a form called frmChangePublicProperty.Add the code that modifies the value of the Custom property. The code behind the ChangeCaption button, as seen in Figure 13.21, modifies the value of the Custom property called CustomCaption that's found on the form called frmPublicProperties.
To test the Custom property created in the preceding example, run the form called frmPublicProperties, which is in the database called CHAP13EX.MDB, found on your sample code CD-ROM. Click the Change Form Caption command button. Nothing happens because the value of the Custom property hasn't been set. Open the form called frmChangePublicProperty and click the Change Form Property command button. Return to frmPublicProperties and again click the Change Form Caption command button. The form's caption should now change.
Close the frmPublicProperties form and try clicking the Change Form Property command button. A runtime error occurs, indicating that the form you're referring to is not open. You can eliminate the error by placing the following code in the Click event of cmdPublicFormProperty:
Private Sub cmdPublicFormProperty_Click()
Form_frmPublicProperties.CustomCaption = _
"This is a Custom Caption"
Forms!frmPublicProperties.Visible = True
End SubThis code modifies the value of the Public property by using the syntax Form_FormName.Property. If the form isn't loaded, this syntax loads the form but leaves it hidden. The next command sets the form's Visible property to True.
A PropertyLet routine is a special type of subroutine that automatically executes whenever the property's value is changed. A PropertyGet routine is another special subroutine that automatically executes whenever the value of the Custom property is retrieved. Instead of using a Public variable to create a property, you insert two special routines: PropertyLet and PropertyGet. This example is found in CHAP13EX.MDB in the frmPropertyGetLet and frmChangeWithLet forms. To insert the PropertyLet and PropertyGet routines, follow these steps:
Choose Insert | Procedure. The dialog box shown in Figure 13.23 appears.
Type the name of the procedure in the Name text box.
Select Property from the Type option buttons.
Select Public as the Scope so that the property is visible outside the form.
Click OK. The
PropertyGetandPropertyLetsubroutines are inserted in the module. (See Figure 13.24.)
Notice that the Click event code for the cmdChangeCaption command button hasn't changed. The PropertyLet routine, which automatically executes whenever the value of the CustomCaption property is changed, takes the uppercase value of what it's being sent and places it in a Private variable called mstrCustomCaption. The PropertyGet routine takes the value of the Private variable and returns it to whomever asked for the value of the property. The sequence of events is as follows—the following code is placed in the form called frmChangeWithLet:
Private Sub cmdPublicFormProperty_Click() Form_frmPropertyGetLet.CustomCaption = "This is a Custom Caption" Forms!frmPropertyGetLet.Visible = True End Sub
This routine tries to set the value of the Custom property called CustomCaption to the value “This is a Custom Caption”. Because the property's value is being changed, the PropertyLet routine in frmPropertyGetLet is automatically executed. It looks like this:
Public Property Let CustomCaption(ByVal CustomCaption As String) mstrCustomCaption = UCase$(CustomCaption) End Property
The PropertyLet routine receives the value "This is a Custom Caption" as a parameter. It uses the UCase function to manipulate the value it was passed and convert it to uppercase. It then places the manipulated value into a Private variable called mstrCustomCaption. The PropertyGet routine isn't executed until the user clicks the cmdChangeCaption button in the frmPropertyGetLet form. The Click event of cmdChangeCaption looks like this:
Private Sub cmdChangeCaption_Click() Me.Caption = CustomCaption End Sub
Because this routine needs to retrieve the value of the Custom property CustomCaption, the PropertyGet routine automatically executes:
Public Property Get CustomCaption() As String CustomCaption = mstrCustomCaption End Property
The PropertyGet routine takes the value of the Private variable, set by the PropertyLet routine, and returns it as the value of the property.
You might wonder why this method is preferable to declaring a Public variable. Using the UCase function within PropertyLet should illustrate why. Whenever you expose a Public variable, you can't do much to validate or manipulate the value you receive. The PropertyLet routine gives you the opportunity to validate and manipulate the value the property is being set to. By placing the manipulated value in a Private variable and then retrieving the Private variable's value when the property is returned, you gain full control over what happens internally to the property.
Custom methods are simply Public functions and subroutines placed in a form module or a report module. As you will see, they can be called by using the Object.Method syntax. Here are the steps involved in creating a Custom method; they are found in CHAP13EX.MDB in the forms frmMethods and frmExecuteMethod:
Open the form or report that will contain the Custom method.
Create a Public function or subroutine. (See Figure 13.25.)
Open the form module, report module, or code module that executes the Custom method.
Use the
Object.Methodsyntax to invoke the Custom method. (See Figure 13.26.)
Figure 13.25 shows the Custom method ChangeCaption found in the frmMethods form. The method changes the form's caption. Figure 13.26 shows the Click event of cmdExecuteMethod found in the frmExecuteMethod form. It issues the ChangeCaption method of the frmMethods form, then sets the form's Visible property to True.
Many of the examples in this chapter are taken directly from the Time and Billing application. To add polish to your application, build an AutoExec routine that will be called from the AutoExec macro. It displays a splash screen, perform some setup functions, then load the frmClients form (the starting point for the application). The CHAP13.MDB file contains all these changes.
Begin by modifying the AutoExec macro so that it hides the Database window and then calls an AutoExec function. In Chapter 37, you will remove the AutoExec macro and perform the tasks that the macro performs by using the database's StartUp properties. The AutoExec function is found in basAutoExec and looks like this:
Function AutoExec()
DoCmd.OpenForm "frmSplash"
DoEvents
DoCmd.Hourglass True
Call GetCompanyInfo
DoCmd.Hourglass False
DoCmd.OpenForm "frmClients"
If IsLoaded("frmSplash") Then
DoCmd.Close acForm, "frmSplash"
End If
End FunctionThe AutoExec routine opens the frmSplash form. It issues a DoEvents to give the form time to load before the routine continues processing. It then calls the GetCompanyInfo routine, developed in Chapter 9, “Advanced VBA Techniques,” to fill in the CompanyInfo type structure used throughout the application. It turns off the hourglass, opens the frmClients form, and unloads frmSplash if it's still loaded.
Remove the call to CompanyInfo from the frmClients form. This routine is now called from the AutoExec function.
Note
The AutoExec routine is one way to launch an application. Chapter 37 shows you how to use the Startup property to designate a starting point for your application, which is the method I prefer.
The splash screen, shown in Figure 13.27, is called frmSplash. Its timer interval is set to 3000 (3 seconds), and its Timer event looks like this:
Private Sub Form_Timer() DoCmd.Close acForm, Me.Name End Sub
The Timer event unloads the form. The frmSplash Popup property is set to Yes, and its border is set to None. Record selectors and navigation buttons have been removed.
Forms are the centerpiece of most Access applications, so it's vital that you're able to fully harness their power and flexibility. The techniques covered in this chapter have shown you how to work with Form and Control events. You have seen many examples of when and how to leverage the event routines associated with forms and specific controls. You have also learned about the types of forms available, their uses in your applications, and how you can build them. Finally, you have learned several power techniques that will help you develop complex forms.