| home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
You will probably find that given Access's graphical environment, the majority of your development efforts will be centered around forms. It is imperative that you understand all the Form and Control events in addition to knowing which event you should code to accomplish what task. It is also important that you are aware of the types of forms that are available and how you can successfully achieve the look and feel you want for each form.
Many times you won't need to design your own form. Instead, you can take advantage of one of the built-in dialogs that are part of the VBA language or supplied as part of the Access Developer's Toolkit. Of course, you should add menu bars and toolbars to your forms. Whatever types of forms you create, you should take advantage of all the tricks and tips of the trade, which are covered throughout this chapter.
Microsoft Access traps for 29 Form events. Each one of these events serves a distinct purpose. Events are also trapped for Form sections and controls. This section covers the events available for forms and when you should use them.
The Current event of a form is one of the more commonly coded events. It occurs each time focus moves from one record to another. The Current event is a great place to put code that 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 you are about to display is a top client. The following code is placed in the Current event of the frmClients form that is part of the time and billing application:
Private Sub Form_Current()
If IsNull(Me![ClientID]) Then
DoCmd.GoToControl "ContactFirstName"
End If
End Sub
This code moves the focus to the ContactFirstName control if the ClientID of the record that the user is moving to happens to be Null. This situation occurs 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 contains an example of a practical use of the BeforeInsert event:
Private Sub Form_BeforeInsert(Cancel As Integer)
Me![ClientID] = Forms![frmClients]![ClientID]
End Sub
The frmProjects form is always called from the frmClients form. The BeforeInsert event of the frmProjects form sets the value of the ClientID text box equal to the value of the ClientID text box on the frmClients form.
The BeforeUpdate event runs before a record is updated. It occurs when the user tries to move to a different record or when the File|Save Record command is executed. The BeforeUpdate event can be used to programmatically cancel the update process. It is used for this purpose 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 ships with Access contains an excellent example of the use of 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 Sub
This code, found within 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. Here's an example:
Private Sub Form_AfterUpdate()
Me!cboSelectProduct.Requery
End Sub
This 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.
The order of events when a user begins to type data into a new record is
The Delete event occurs when a user attempts to delete a record but before the record is actually removed from the table. This is a great way to place code that allows the deletion of a record only under certain circumstances. If the Delete event is canceled, the BeforeDelConfirm and AfterDelConfirm events will never execute, and the record will never be deleted.
When the user deletes multiple records, the Delete event occurs after each record is deleted. This enables you to evaluate a condition for each record and determine whether each record should be deleted.
The BeforeDelConfirm event occurs after the Delete event but before the delete confirm dialog is displayed. If you cancel the BeforeDelConfirm event, the record being deleted is restored from the delete buffer, and the delete confirm dialog is never displayed.
The AfterDelConfirm event occurs after the record is actually deleted. It occurs even if the deletion is canceled. If the BeforeDelConfirm is not canceled, the AfterDelConfirm event occurs after the confirmation dialog is displayed.
The Open event occurs when a form is opened but before the first record is displayed. Using this event, you can determine exactly what happens when the form first opens. The Open event of the frmProjects form of the time and billing application 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 Sub
This code checks to ensure that the frmClients form is loaded. If the frmClients form is not loaded, a message box is displayed, and the Cancel parameter is set to True. When the Cancel parameter of the Open event is set to True, the form is prohibited from loading.
The Load event occurs when a form is opened and the first record is displayed. It occurs after the Open event. The Open event of a form can cancel the opening of a form, but the Load event cannot. The following routine is placed in the Load event of the frmProjects form in the time and billing application:
Private Sub Form_Load()
If Me.OpenArgs = "GotoNew" And Not IsNull([ProjectID]) Then
DoCmd.DoMenuItem acFormBar, 3, 0, , acMenuVer70
End If
End Sub
This routine looks at the string passed as an opening argument to the form. If the OpenArg string is "GotoNew" and the ProjectID is not Null (the user is not already on a new record), a new record is inserted.
The Resize event occurs when a form is opened and whenever the size of the form changes.
The Unload event occurs when a form is closed but before the form is actually removed from the screen. It is triggered when the user clicks Close on 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 ensures that it is okay to unload the form in the Unload event. You can also use the Unload event to place any code that you want to have execute 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 Sub
This code is placed within the Load event of the AddCategory form of the Solutions database that ships with Access. It checks to see 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. It occurs after the Unload event. It is important to understand that you can cancel the Unload event but not the Close event.
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 Sub
This code is placed within the Close event of the Suppliers form that is part of the NorthWind database. When the Suppliers form is closed, it tests to see whether the Product List and Products forms are open. If they are, it closes them.
The Activate event occurs when the form receives focus and becomes the active window. It is 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 using VBA code. The following code, found in the Activate event of the frmClients form of the time and billing application, requeries the fsubClients subform whenever the frmClients main form is activated:
Private Sub Form_Activate()
Me![fsubClients].Requery
End Sub
The Deactivate event occurs when the form loses focus. This occurs when a table, query, form, report, macro, module, or the Database window becomes active. The Deactivate event is not triggered when a dialog, popup form, or another application becomes active. Here's an example:
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 Sub
This code is found in the EnterOrEditProducts form that is part of the Solutions database. It 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 occurs whenever a form receives the focus. The GotFocus event occurs for a form only if there are no visible, enabled controls on the form. This event is rarely used for a form.
The LostFocus event occurs whenever a form loses the focus. The LostFocus event occurs for a form only if there are no visible, enabled controls on the form. This event is rarely used for a form.
The Click 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.
The DblClick event occurs 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. It occurs before the Click event fires. It can be used to determine which mouse button was pressed.
The MouseMove event occurs 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 is 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 occurs before the Click event fires and can be used to determine which mouse button was pressed.
The KeyDown event occurs if there are no controls on the form or if the KeyPreview property of the form is set to Yes. When the KeyPreview property is set to Yes, all keyboard events are previewed by the form and occur for the control that has the 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 KeyPreview property of the form is set to Yes. The KeyUp event occurs 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 occurs if there are no controls on the form or if the KeyPreview property of the form is set to Yes. The keystroke can be canceled by setting KeyCode to zero.
The Error event is triggered whenever an error occurs while the user is in the form. Microsoft Jet database engine errors are trapped, but Visual Basic errors are not. This event can be used to suppress the standard error messages that would display. Visual Basic errors must be handled using standard On Error techniques. Both the Error event and the handling of Visual Basic errors are covered in Chapter 17, "Handling Those Dreaded Runtime Errors."
The Filter event occurs 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 occurs when the user closes the Advanced Filter/Sort window or the Filter By Form window. You can use this event to make sure that the filter being applied is correct, to change the display of the form before the filter is applied, or to undo any changes you made when the Filter event occurred.
The Timer event occurs at regular intervals. It occurs only when the form's TimerInterval property is set. How often the Timer event triggers depends on the value set within the TimerInterval property.
One of the mysteries of events is the order in which they occur. One of the best ways to learn about the order in which events occur is to place Debug.Print statements within the events that you want to learn about. This technique is covered in Chapter 16, "Debugging: Your Key to Successful Development." Keep in mind that event order is not an exact science; it is nearly impossible to guess the order in which events occur in all situations. It is helpful, though, to understand the basic order in which certain events do occur.
When the user opens a form, the following events occur:
Open®Load®Resize®Activate®Current
After these Form events occur, the Enter and GotFocus events of the first control occur. Remember that the only place you can cancel the opening of the form is in the Open event.
When a user closes a form, the following events occur:
Unload®Deactivate®Close
Before these events occur, the Exit and LostFocus events of the active control are triggered.
When a user resizes a form, what happens depends on whether the form is minimized, restored, or maximized. When the form is minimized, the following Form events occur:
Resize®Deactivate
When the user restores a minimized form, the following events occur:
Activate®Resize
When the user maximizes a form or restores a maximized form, only the Resize event occurs.
When the user moves from one form to another, the Deactivate event occurs for the first form and then the Activate event occurs for the second form. Remember that the Deactivate event does not occur if focus moves to a dialog, a popup form, or another application.
When the user types a character and the KeyPreview property of the form 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 occur. The KeyPress and Change events capture only ANSI keystrokes. These events are the easiest to deal with; however, you need to handle the Keydown and KeyUp events when you need to trap for non-ANSI characters such as Shift, Alt, and Control.
When the user clicks the mouse button, the following events occur:
MouseDown®MouseUp®Click
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 to which it responds. Many events are common to most controls. Other events 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 about the controls to which the event applies.
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. Here's an example:
Private Sub SelectProduct_BeforeUpdate(Cancel As Integer)
If IsNull([SelectProduct]) Then
MsgBox "You must select a product."
DoCmd.CancelEvent
End If
End Sub
This code is found in the BeforeUpdate event of the SelectProduct control on the EditProducts form found in the Solutions database. It tests to see 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. Here's an example:
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 Sub
This code is found in the AfterUpdate event of the SelectProduct control on the EditProducts form found in the Solutions database. It begins by applying a filter to the form based on the product that was selected from the SelectProduct combo box. It then runs a function that enables all the controls in the Detail Section of the form. It disables the ProductID control and sets the 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. It occurs when data in the control changes. In the case of a text box, this event occurs when a character is typed. In the case of a combo box, it occurs 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. It occurs when the user enters a value in the text box portion of the combo box that is not in the combo box list. 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:
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 Sub
This code, found in the frmPayments form of the time and billing application, is executed when the user enters a payment method 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 this chapter.
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 receives the focus from another control on the same form. It occurs before the GotFocus event. Here's an example:
Private Sub fsubTimeCards_Enter()
If IsNull(Me![EmployeeID]) Then
MsgBox "Enter employee before entering time or expenses."
DoCmd.GoToControl "EmployeeID"
End If
End Sub
This example is found on the frmTimeCards form that is part of the time and billing application. When the user moves into the fsubTimeCards subform control, the Enter event of the subform control tests to see whether the EmployeeID has been entered on the main form. If it has not, a message box is displayed, and focus is moved to the EmployeeID 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, check boxes, combo boxes, list boxes, and command buttons. It occurs 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 receive focus only if they are visible and enabled.
The LostFocus event applies to text boxes, toggle buttons, options buttons, check boxes, 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 difference between GotFocus/LostFocus and Enter/Exit lies in when they occur. If focus is moved to another form or is returned to the current form, the GotFocus and LostFocus events of the control are triggered. The Enter and Exit events do not occur when the form loses or regains focus.
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 and then releases a mouse button over a control. Here's an example:
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 Sub
This code, found in the frmProjects form of the time and billing application, checks the caption of the cmdToggleView command button. If the caption reads "&View Expenses", 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 Click event is triggered when the user clicks the mouse over an object, as well as in other situations:
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 and then releases the left mouse button twice over a control. Here's an example:
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 Sub
In this example, found within the frmPayments form of the time and billing application, the cboPaymentMethodID combo box control is evaluated to see if it is 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 is closed, the cboPaymentMethods combo box is requeried. If the long integer variable does not 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 occurs when the mouse button is pressed over a control and 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, check boxes, combo boxes, list boxes, and bound object frames. It occurs 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, check boxes, 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 depressed.
The KeyPress event applies to text boxes, toggle buttons, option buttons, check boxes, combo boxes, list boxes, and bound object frames. It occurs when the user presses and releases an ANSI key while the control has the focus. It can be canceled by setting KeyCode equal to zero.
Just as Form events occur 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, it is important to understand the order in which events for the control occur.
When the focus is moved to a control, the following events occur:
Enter®GotFocus
If focus is moving to a control as the form it is located on is opened, the Form and Control events occur in the following sequence:
Open(form)® Activate(form)® Current(form)® Enter(control)® GotFocus(control)
When the focus leaves a control, the following events occur:
Exit®LostFocus
When the focus leaves the control because the form it is located on is closing, the following events occur:
Exit(control)® LostFocus(control)® Unload(form)® Deactivate(form)® Close(form)
When you change data in a control and then move the focus to another control, the following events occur:
BeforeUpdate®AfterUpdate®Exit®LostFocus
After every character that is typed in a text or combo box, the following events occur before focus is moved to another control:
KeyDown®KeyPress®Change®KeyUp
In the case of 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 is available to every procedure within a Form or Report module. Using Me is a great way to write generic code within 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 is also useful to pass Me (the current form or report) to a generic procedure within a module. This is demonstrated 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 Sub
The ChangeCaption procedure, contained within a Code module, receives any form as a parameter. It evaluates the caption of the form that it was passed. If the caption is Null, it sets the caption to "Form for -", concatenated with the user's name. Otherwise, it takes that existing caption of the form it was passed and appends the user name.
You can design a variety of forms using Microsoft Access. By working with the properties available within the Access form designer, you can create forms with many different looks and dramatically different types of functionality. This chapter covers all the major categories of forms, but remember that you can build your own creations. Of course, don't lose track of maintaining consistency with the standards for Windows applications.
One of the most common types of forms is a form that allows you to view one record at a time. Such a form is pictured in Figure 13.1. This form allows the user to view one customer record and then move to other records as needed.
Creating a Single form is easy. It involves setting the Default View property of the form to Single Form (see Figure 13.2).
Figure 13.2. The Default View property of a form.
Often, the user wants to be able to view multiple records at a time. This requires that you create a Continuous form, such as that shown in Figure 13.3. A Continuous form is built by setting the Default View property to Continuous Forms.
Figure 13.3. A Continuous form.
A common use of a Continuous form is a subform. You generally should show multiple records within a subform. The records that appear within the subform are all the records that relate to the record displayed in the main form. An example appears in Figure 13.4, which shows two subforms, each with their 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 relating to the selected order.
Figure 13.4. A form containing two Continuous subforms.
Scarcity of screen real estate is a never-ending problem. One of the solutions is a multipage form. Figures 13.5 and 13.6 show the two pages of the multipage employee form. Viewing 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 and then click and drag to place it on the form.
Figure 13.5. The first page of a multipage form.
Figure 13.6. The second page of a multipage form.
Figure 13.7. A multipage form in Design view.
When creating a multipage form, remember a few important steps:
An alternative to a multipage form is a Tabbed form. Access does not directly support tabbed forms, although the Access Developer's Toolkit ships with the TabStrip OCX control that is part of Windows 95. This control is covered in Chapter 25, "Using OLE Controls." Here you see how a multipage form can be designed to look like a Tabbed form.
The form shown in Figure 13.8 simulates the look and feel of a tabbed dialog control. This form, included in the CHAP13EX.MDB database on your sample code CD, uses a multipage form to simulate the tabbed dialog control.
Figure 13.8. A multipage tabbed form.
Very little code is involved in making this form work:
Private Sub optSelection_AfterUpdate()
If optSelection.Value = 4 Then
DoCmd.Close
Else
Me.GoToPage optSelection.Value
End If
End Sub
A set of toggle buttons placed in an option group is used to create the form "tabs." The set is placed in the Header section of the form. Each tab is a separate page of the multipage form. The code that makes the tabs work is placed in the AfterUpdate event of the optSelection option group. The code checks to see whether the value of the option group is 4. This means that the Exit button was pressed and the form should close. If any of the first three buttons are selected, the GoToPage method is used to go to the page number associated with the value of the optSelection option group.
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 35, "Distributing Your Application." In this section, you learn how to create a custom Switchboard form. A Switchboard form is simply a form containing command buttons that navigate you to other Switchboard forms or to the forms and reports that compose your system.
The form pictured in Figure 13.8 is a Switchboard form. Each "tab" on the form allows the user to access different components of the database. What differentiates a Switchboard form from other forms is that its purpose is limited to navigation 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. The style you use depends on the needs of your users.
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. It is easy to create a Splash Screen 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 is unloading, it loads the Switchboard form. The Splash Screen form included in CHAP13EX.MDB is called frmSplash. When it unloads, it opens the form called frmSwitchboard.
You can implement your Splash Screen form in many other ways. For example, you can call the Splash Screen form from a Startup form. The Open event of the Startup form 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 displays again.
Another popular method is to build a function that is called from an AutoExec macro. This Startup function can display the splash screen, execute all the tasks required to set up your application, and then unload the splash screen. Here's an example:
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 opens the frmSplash form. It then displays an hourglass and continues with any setup processing. When it is done with all the setup processing, it opens the frmSwitchboard form, closes the splash screen, and gets rid of the hourglass.
Another way that you can display a splash screen is 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 displays 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 displays.
Dialog forms are generally used to gather information from the user. What makes them Dialog forms is that they are modal, meaning that the user cannot proceed with the application until the form is handled. Dialog forms are generally used when you must obtain specific information from your user before the processing of your application can continue. A custom Dialog form is simply a regular form that has a border style of Dialog 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 found in CHAP13EX.MDB is a custom Dialog form.
Access ships with two built-in dialogs: the standard Windows message box and input box. The Access Developer's Toolkit also ships with the Common Dialog OLE control, which gives you access to other commonly used dialogs.
A message box is a predefined dialog that you can incorporate into your applications. Although it is predefined, it can be customized using parameters. The VBA language has a MsgBox statement and a MsgBox function. The MsgBox statement can only display a message, whereas the MsgBox function can display a message and return a value based on the user's response.
The message box contained within the VBA language is the same message box that is standard in most Windows applications, so it is already familiar to most Windows users. Rather than creating your own dialogs to obtain 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 to appear, the title for the message box, and the help file and context ID that you want to be available if the user selects Help while the dialog is displayed. The statement looks like this:
MsgBox "This is a Message", vbInformation, "This is a Title"
This example displays the message "This is a Message". The information icon is displayed. The title for the message box is "This is a Title". The message box contains an OK button that is used by the user to close the dialog.
Whereas the MsgBox statement is normally used to display an OK button only, the MsgBox function enables you to select from various standard combinations of buttons to be included in the dialog. It 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 that you want to display. The second parameter is a numeric value indicating what buttons you want to display as well as the 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 included in the table can be substituted for the numeric values, if desired.
Buttons |
Value |
Intrinsic Constant |
|
Okay button only |
0 |
vbOKOnly |
|
Okay 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 default icon in the dialog.
Icon |
Value |
Intrinsic Constant |
|
Critical (Stop Sign) |
16 |
vbCritical |
|
Warning Query (Question) |
32 |
vbQuestion |
|
Warning Exclamation |
48 |
vbExclamation |
|
Information (I) |
64 |
vbInformation |
Here's an example:
Sub MessageBoxFunction()
Dim intAnswer As Integer
intAnswer = MsgBox("Are You Sure?", vbYesNoCancel + vbQuestion, _
"Please Respond")
End Sub
This message box displays Yes, No, and Cancel buttons. It also displays the Question icon. It is pictured in Figure 13.9. The Function call returns a value stored into the Integer variable iAnswer.
Figure 13.9. The MsgBox function.
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. Here is an 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 Sub
This code evaluates the user's response and displays a message to the user based on his or her answer. Of course, in a real-life situation, the code placed in the case statements would be more practical. Table 13.3 lists the values that are returned from the message box function, depending on which button the user selected.
Response |
Value |
Intrinsic Constant |
|
OK |
1 |
vbOK |
|
Cancel |
2 |
vbCancel |
|
Abort |
3 |
vbAbort |
|
Retry |
4 |
vbRetry |
|
Ignore |
5 |
vbIgnore |
|
Yes |
6 |
vbYes |
|
No |
7 |
vbNo |
The InputBox function displays a dialog that contains a simple text box. It returns the text that 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 Sub
This subroutine displays the input box that appears in Figure 13.10. 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.
Figure 13.10. The InputBox function.
As mentioned, the Common Dialog control is an OCX control that is included as part of the Access Developer's Toolkit. It enables you to display Windows common dialogs, including File Save, File Open, File Print, File Print Setup, Fonts, and Colors. The Common Dialog control is covered in Chapter 26, "Using OLE Controls."
Custom menus are menus that you create to display with your forms and reports. You can create as many custom menus as you would like. Each menu can be attached to one or more forms or reports.
You will often want to restrict what users can do while they are in a form or report. By creating a custom menu, you can restrict and customize what users can do from within that form or report.
To create a custom menu for a form or report, open the form or report in Design view. With the form selected, open the Properties sheet and select the MenuBar property from the list of Other properties. Click on the Build button. The Menu Builder dialog appears (see Figure 13.11).
Figure 13.11. The Menu Builder dialog.
Here you can select to attach and modify one of the existing menus, or you can select Empty Menu Bar and build the menu from scratch. In either case, you are presented with the Menu Builder - [New Menu Bar] dialog shown in Figure 13.12.
Figure 13.12. The Menu Builder with a new menu.
If you create a menu item on a menu pad, you are prompted for the action associated with that menu item as well as the arguments associated with the menu item. After selecting a DoMenuItem action, you can click on the ellipse to enter the arguments. The DoMenuItem Arguments dialog appears (see Figure 13.13).
Figure 13.13. Entering DoMenuItem arguments.
The DoMenuItem Arguments dialog enables you to easily select a menu bar, menu name, command, and subcommand, if required. Instead of selecting DoMenuItem, you can have the menu call a macro or a user-defined routine.
When you have completed the menu and clicked OK, you are prompted to assign a name to the menu. This is the name that you select when attaching the menu to any of your forms or reports. After attaching a menu to a form, that menu appears anytime you run the form. All the menu code is stored as macros.
To modify an existing menu, click on the ellipse next to the Menu Bar property of the menu you want to modify. The Menu Builder dialog appears. You can select a menu item and click Delete; click Insert to add menu items; or edit a menu item to modify its caption, action, or arguments.
Keep in mind a few tips when designing menus:
Custom toolbars are toolbars that you design yourself. They can be made visible at any time. You can build as many custom toolbars as you want, hiding them and displaying them as appropriate throughout your application. A custom toolbar is part of a database and is stored with that database. A custom toolbar and its controls therefore cannot be accessed from another database.
Users have come to expect the accessibility that toolbars offer. You should probably remove the standard toolbars and replace them with custom toolbars, especially if your form or report appears with a nonstandard menu. Custom toolbars can add an exciting, professional touch to your applications.
To create a custom toolbar to be used within a specific database, start by right-clicking anywhere on an existing toolbar. Next, select Toolbars and then New. Assign the toolbar a name. Notice that a small floating toolbar appears. Figure 13.14 shows the Toolbars dialog along with the floating toolbar.
Figure 13.14. The Toolbars dialog with floating toolbar.
Select Customize from the Toolbars dialog. When you select Customize, the Customize Toolbars dialog appears (See Figure 13.15). You can easily drag and drop tools from this dialog onto your new toolbar. When you create a toolbar, you want it to appear with a particular form and possibly disappear when that form is closed. You can accomplish this as follows:
Private Sub Form_Activate()
DoCmd.ShowToolbar tbOrders, acToolbarYes
End Sub
Private Sub Form_Deactivate()
DoCmd.ShowToolbar tbOrders, acToolbarNo
End Sub
Figure 13.15. The Customize Toolbars dialog.
As you can see, the toolbar is made visible upon the Activate event of the form and is hidden upon deactivation of the form. This occurs so that as the user clicks on and off the form, the toolbar shows only with that particular form.
If you want certain code to run when a toolbar button is clicked, you must call the code from a macro. This involves creating a macro with a Run Code action. It is generally better to include a button that calls a macro only in a custom toolbar; otherwise, an error occurs when the toolbar button is pressed and the macro attached to the button is not found in the current database.
Access has several form filtering features that are part of the user interface. You can opt to include these features within your application, omit them from your application entirely, or control their behavior. For your application to control the behavior of the filter features, it needs to respond to the Filter event. Responding to a Filter event means that your application senses when a filter is placed on the data in the form. When it has sensed the filter, the code in the Filter event executes.
At times you might want to alter the standard behavior of a filter command. For example, you might want to display a special message to the user, or you might want to take a specific action in your code. Another reason for responding to a Filter event could be that you want to alter the display of the form before the filter is applied. For example, if a certain filter is in place, you might want to hide or disable certain fields. When the filter is removed, you might want to return the appearance of the form 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 alter the filtering behavior as desired.
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. If the user invokes the filter by selecting Advanced Filter/Sort, the FilterType parameter equals the constant acFilterAdvanced. The following code demonstrates the use of 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 Sub
This code, placed in the Filter event for the form, evaluates the filter type. If Filter By Form was selected, a message box displays and the filtering proceeds as usual. If the user selects Advanced Filter/Sort, the user is told that she can't do this and the filter process is canceled.
Not only can you test for how the filter was invoked, you can intercept the process when the filter is applied. You accomplish this by placing code in the ApplyFilter event of the form. Here's an 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 Sub
This code evaluates the value of the ApplyType parameter. If it is 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 that it can contain objects from other applications. Access 95 is also an OLE server application. Using Access as an OLE server is covered in Chapter 25, "OLE: Communicating with Other Applications." Access's capability to control other applications using programming code is also covered in Chapter 25. In this section, 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 is part of the Employees table within the NorthWind database. The field type of the Employees table that supports multimedia data is OLE Object. This means that each record within 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 (see Figure 13.16). 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.
Figure 13.16. In-Place activation.
To insert a new object, take the following steps:
Figure 13.17. The Insert Object dialog enables you to create a new object or insert an object from an existing file.
Figure 13.18. The Insert Object dialog as it appears when you select Create from file.
If you double-click on a linked object, you launch the source application for the object. You do not get In-Place activation (see Figure 13.19).
Figure 13.19. Editing a linked object.
Unbound OLE objects are not stored within your database. Instead they are part of the form within which they were created. Like bound OLE objects, unbound OLE objects can be linked or embedded. You create an unbound OLE object by adding an unbound object frame to the form.
The OpenArgs property gives you a way to pass information to a form as it is being opened. The OpenArgs argument of the OpenForm method is used to populate the OpenArgs property of a form at runtime. It works like this:
DoCmd.OpenForm "frmPaymentMethods", , , , , acDialog, "GotoNew"
This code is found within the frmPayments form of the time and billing application. 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.DoMenuItem acFormBar, 3, 0, , acMenuVer70
End If
End Sub
This code evaluates the OpenArgs property of the form, moving to a new record if the OpenArgs property contains the text string "GoToNew" and the PaymentMethodID of the current record is not Null. The OpenArgs property can be evaluated and used anywhere in the form.
Many developers do not realize how easy it is to switch the record source of a form 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 is also a great way to limit the data that is displayed in a form at a particular moment in time. Using a technique of altering a form's record source at runtime, you can dramatically improve performance, especially of a client/server application. Here's an example found in the ShowSales form of the Solutions database. The form is shown in Figure 3.20.
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 Function
Figure 13.20. Changing the record source of a form at runtime.
This 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 and then evaluates the parameter that it was passed (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 record source of the subform. It limits the range of sales values displayed on the subform.
The ShowSales routine proceeds to build a string that contains a SQL statement. The SQL statement selects all required fields from the Customers table and OrderSubtotals query. It builds a WHERE clause including the BeginningDate and EndingDate from the main form as well as the string that was 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. If records are found, the Detail Section of the form is enabled, and the focus is moved to the subform.
Combo and list boxes are very powerful. Being able to properly respond to the NotInList event of a combo box, populate a combo box using code, and select multiple entries in a list box are essential skills of an experienced Access programmer. They are covered in detail in this section.
As discussed earlier in this chapter, the NotInList event occurs when a user types a value in the text box portion of a combo box that is not found in the combo box list. This event occurs only if the LimitToList property of the combo box is set to True. It is up to you whether you want to respond to this event.
At times you might want to respond with something other than the default error message when the LimitToList property is set to True and the user attempts to add an entry. For example, if a user is entering an order and she enters the name of a new customer, you might want to 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 attempts to type an entry that is not found within 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 Sub
The problem with this code is that it warns the user of the problem but does not rectify the problem; thus, this code runs and then the default error handling kicks in.
The NotInList event procedure accepts a response argument. This 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:
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 Sub
As you know, it is easy to populate a combo or list box by setting properties of the control. This method is sufficient for many situations. In other situations, though, you might want to populate a combo or list box programmatically. One example is when you want to populate the combo or list box with values from an array. Another example is when you want to populate the box with table or report names or some other database component.
To populate a combo or list box using code, you create a Callback function. This function tells Access how many rows and columns will be in the combo or list box as well as what data will be used to fill the box. This function becomes the Row Source type for your combo or list box. Access calls the function and then uses its information to populate the combo or list box (see Figure 13.21). The following example is found in the CustomersDialog form that is part of the Solutions database:
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 Function
Figure 13.21. Using a Callback function as the Row Source type for a combo or list box.
The 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.
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 is useful when you are 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 is called, Access automatically supplies a different value for the code, indicating the information that it is requesting. The code item can have the values shown in Table 13.5.
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 that are contained in the box (code 3). Code 9 is called when the form is closed or the combo or list box is queried.
Armed with this knowledge, you can take a good look at the lstForms_Fill function, the Callback function that is used to populate the list box. The purpose of this function is to populate the list box with a list of forms that was opened by the Customer Phone List form. The Customer Phone List form 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:
All this work might seem difficult at first. After you have populated a couple of combo or list boxes, though, it is quite easy. In fact, all you need to do is copy the case structure that you see in the lstForms_Fill function and use it as a template for all your Callback routines.
Access 95 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 Using Code") demonstrates the use of a Multi-select list box. The form shows all the instances of the Customer form that are currently open (see Figure 13.22). It enables users to select which instances of the Customer they want to keep open when the CustomersPhoneList form is closed. The code under the Click event of the OK button looks like this:
Figure 13.22. A 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 Sub
The code uses the For Each...Next construct, along with the ItemsSelected property of the list box to loop through each selected item within 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 within the collection because the collection of forms is one-based. The KeepMeAlive custom property of each form is set to True. The CustomersDialog form is closed. The KeepMeAlive property, when set to True, ensures that the particular instance of the form is not closed.
Many new Access developers do not know the ins and outs of creating and modifying a subform and referring to subform controls. Let's start with some important points you should know when working with subforms:
Many developers do not 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. Here's an 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 must then point at its controls collection. Here's an example:
Forms!frmCustomer!fsubOrders.Controls!txtOrderID
This code refers to the txtOrderID control on the form contained within 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.Controls!txtOrderID.Enabled = False
This code sets the Enabled property of the txtOrderID control on the form contained within the fsubOrders control to False.
The RecordsetClone property of a form is used to refer to the form's underlying recordset. You can manipulate this recordset independent of what is 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 Sub
This example issues the FindFirst method on the RecordsetClone of the form. 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 bookmark of the form 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 Sub
This code creates an object variable that points at the RecordsetClone of the form. The recordset object variable can then be substituted for Me.RecordsetClone, because it references the form's underlying recordset.
Many of the examples in this chapter were 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 will display a splash screen, perform some setup functions, and then load the frmClients form (the starting point for the application). The CHAP13.MDB file contains all of these changes.
Begin by modifying the AutoExec macro so that it hides the Database window and then calls an AutoExec function. 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 Function
The 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, to fill in the CompanyInfo type structure that will be used throughout the application. It turns off the hourglass, opens the frmClients form, and unloads frmSplash if it is still loaded.
Remove the call to CompanyInfo from the frmClients form. This routine is now called from the AutoExec function.
The AutoExec routine is one way to launch an application. Chapter 35, "Distributing Your Application," shows you how to use the Startup property to designate a starting point for your application. Using the Startup property is the method that I prefer.
The splash screen is shown in Figure 13.23. It 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
Figure 13.23. The splash screen.
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. It is therefore paramount that you are able to fully harness their power and flexibility. The techniques covered in this chapter showed you how to work with Form and Control events. You saw many examples of when and how to leverage the event routines associated with forms and specific controls. You also learned about the types of forms available, their uses within your applications, and how you can build them. Finally, you learned several power techniques that will help you to develop complex forms.
