Previous Page TOC Index Next Page Home


13

Let's Get More Intimate with Forms: Advanced Techniques

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.

What Are the Form Events and When Do You Use Them?

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.

Current

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.

BeforeInsert

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.

BeforeUpdate

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.

AfterUpdate

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.

AfterInsert

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

BeforeInsert®BeforeUpdate®AfterUpdate®AfterInsert

The BeforeInsert event occurs when the user types the first character, the BeforeUpdate event occurs when the user updates the record, the AfterUpdate event occurs when the record is updated, and the AfterInsert event occurs when the record that is being updated is a new record.

Delete

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.

BeforeDelConfirm

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.

AfterDelConfirm

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.

Open

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.

Load

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.

Resize

The Resize event occurs when a form is opened and whenever the size of the form changes.

Unload

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.

Close

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.

Activate

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

Deactivate

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.

GotFocus

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.

LostFocus

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.

Click

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.

DblClick

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.

MouseDown

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.

MouseMove

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.

MouseUp

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.

KeyDown

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.

KeyUp

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.

KeyPress

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.

Error

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."

Filter

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.

ApplyFilter

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.

Timer

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.

Understanding the Sequence of Form Events

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.

What Occurs When a Form Is Opened?

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.

What Occurs When a Form Is Closed?

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.

What Occurs When a Form Is Sized?

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.

What Occurs When Focus Shifts from One Form to Another?

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.

What Occurs When Keys Are Pressed?

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.

What Occurs When Mouse Actions Happen?

When the user clicks the mouse button, the following events occur:

MouseDown®MouseUp®Click

What Are the Section and Control Events and When Do You Use Them?

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.

BeforeUpdate

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.

AfterUpdate

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.

Updated

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.

Change

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.

NotInList

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.

Enter

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.

Exit

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.

GotFocus

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.

LostFocus

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.

Click

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:

When the spacebar is pressed while a command button has the focus.

When the Default property of a command button is set to Yes and the Enter key is pressed.

When the Cancel property of a Command button is set to Yes and the Escape key is pressed.

Wen an accelerator key for a command button is used.

DblClick

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.

MouseDown

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.

MouseMove

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.

MouseUp

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.

KeyDown

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.

KeyUp

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.

KeyPress

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.

Understanding the Sequence of Control Events

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.

What Happens When Focus Is Moved to or from a Control?

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)
What Happens When the Data in a Control Is Updated?

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.

Referring to Me

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.

What Types of Forms Can I Create and When Are They Appropriate?

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.

Single Forms: Viewing One Record at a Time

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.


Figure 13.1. A Single form.

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.

Continuous Forms: View Multiple Records at a Time

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.

Multipage Forms: When Everything Does Not Fit on One Screen

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:

Tabbed Forms: Conserving Screen Real Estate

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.

Switchboard Forms: Controlling Your Application

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 Screen Forms: A Professional Opening to Your Application

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:

  1. Create a new form.

  2. Set the Scrollbars property to Neither, the Record Selectors property to No, the Navigation Buttons property to No, the Auto Resize property to Yes, the AutoCenter property to Yes, and the Border Style to None.

  3. Make the form Popup and Modal.

  4. Add a picture to the form. Set the properties of the picture.

  5. Add any text you would like to put on the form.

  6. Set the timer interval of the form to the amount of seconds that you want the splash screen to display.

  7. Code the Timer event of the form for DoCmd.Close.

  8. Code the Unload event of the form 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 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: Gathering Information

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.

Using Built-In Dialogs

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.

Message Box

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

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.

The MsgBox Function

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

Input Box

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.

Common Dialogs

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."

Adding Custom Menu Bars to Your Forms

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.

Designing a Menu

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.

Modifying the Menu

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.

Tips When Working with Menus

Keep in mind a few tips when designing menus:

Adding Custom Toolbars to Your Forms

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.

Taking Advantage of Built-In Form Filtering Features

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.

Including Objects from Other Applications: Linking Versus Embedding

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

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:

  1. Move to the record that will contain the OLE object.

  2. Right-click on the OLE Object control and select Insert Object. The Insert Object dialog appears (see Figure 13.17).


Figure 13.17. The Insert Object dialog enables you to create a new object or insert an object from an existing file.

  1. Select an object type. Select Create New if you want to create an embedded object. Select Create from File if you want to link to or embed an existing file.

  2. If you select Create from File, the Insert Object dialog changes to look like Figure 13.18.


Figure 13.18. The Insert Object dialog as it appears when you select Create from file.

  1. Select Link if you want to link to the existing file. Do not click Link if you want to embed the existing file. If you link to the file, the Access table will contain 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 within the Access table.

  2. Click Browse and select the file that you want to link to or embed.

  3. Click OK.

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

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.

OpenArgs

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.

Switching a Form's Record Source

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.

Power Combo Box and List Box Techniques

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.

Handling the NotInList Event

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

Populating a Combo or List Box with a Callback Function

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.

Handling Multiple Selections in a List Box

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.

Power Subform Techniques

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:

Referring to Subform Controls

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.

Synchronizing a Form with Its Underlying Recordset

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.

Practical Examples: Applying Advanced Techniques to Your Application

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.

Building an AutoExec Routine to Launch the Application

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.

Building a Splash Screen

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.

Summary

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.

Previous Page TOC Index Next Page Home