Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Power Combo Box and List Box Techniques

Combo and list boxes are very powerful. Being able to properly respond to a combo box's NotInList event, to populate a combo box by using code, and to select multiple entries in a list box are essential skills of an experienced Access programmer. They're covered in detail in the following sections.

Handling the NotInList Event

As previously discussed, the NotInList event occurs when a user types a value in the text box portion of a combo box that's not found in the combo box list. This event takes place only if the LimitToList property of the combo box is set to True. It's up to you whether you respond to this event.

You might want to respond with something other than the default error message when the LimitToList property is set to True and the user tries to add an entry. For example, if a user is entering an order and she enters the name of a new customer, you could react by displaying a message box asking whether she really wants to add the new customer. If the user responds affirmatively, you can display a customer form.

After you have set the LimitToList property to True, any code you place in the NotInList event is executed whenever the user tries to type an entry that's not found in the combo box. The following is an example:

Private Sub cboPaymentMethodID_NotInList(NewData As String, _
Response As Integer)
'If payment method is not in the list,
'ask user if they want to add it
If MsgBox("Payment Method Not Found, Add?", _
vbYesNo + vbQuestion, _
"Please Respond") = vbYes Then
'If they respond yes, open the frmPaymentMethods form
'in add mode, passing in the new payment method
DoCmd.OpenForm "frmPaymentMethods", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
'If form is still loaded, unload it
If IsLoaded("frmPaymentMethods") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmPaymentMethods"
'If the user responds no,
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
End If
End Sub

When you place this code in the NotInList event procedure of your combo box, it displays a message asking the user whether she wants to add the payment method. 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 frmPaymentMethods form, ready to add the payment method whose name she typed.

The NotInList event procedure accepts a response argument, which is where you can tell VBA what to do

after your code executes. Any one of the following three constants can be placed in the response argument:

• acDataErrAdded
  This constant is used if your code adds the new value into the record source for the combo box. This code re-queries the combo box, adding the new value to the list.

• AcDataErrDisplay
  This constant is used if you want VBA to display the default error message.

• AcDataErrContinue
  This constant is used if you want to suppress VBA's error message, using your own instead. Access still requires that a valid entry be placed in the combo box.

Working with a Pop-Up Form

The NotInList technique just described employs the pop-up form. When the user opts to add the new payment method, the frmPaymentMethods form displays modally.

This halts execution of the code in the form that loads the frmPaymentMethods form (in this case, the frmPayments form). The frmPaymentMethods form is considered a pop-up form because the form is modal, it uses information from the frmPayments form, and the frmPayments form reacts according to whether the OK or Cancel button is selected. The code in the Load event of the frmPaymentMethods form in the time and billing database appears as follows:

Private Sub Form_Load()
Me.txtPaymentMethod.Value = Me.OpenArgs
End Sub

This code uses the information received as an opening argument to populate the txtPaymentMethod text box. No further code executes until the user clicks either the OK or the Cancel command button. If the user clicks the OK button, the following code executes:

Private Sub cmdOK_Click()
Me.Visible = False
End Sub

Notice that the preceding code hides, rather than closes, the frmPaymentMethods form. If the user clicks the Cancel button, this code executes:

Private Sub cmdCancel_Click()
DoCmd.RunCommand acCmdUndo
DoCmd.Close
End Sub

The code under the Cancel button first undoes the changes that the user made. It then closes the frmPaymentMethods form. Once back in the NotInList event of the cboPaymentMethod combo box on the frmPayments form, the following code executes:

If IsLoaded("frmPaymentMethods") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmPaymentMethods"
Else
Response = acDataErrContinue
End If

The code evaluates whether the frmPaymentMethods form is still loaded. If it is, the user must have clicked OK. The Response parameter is set to acDataErrAdded, designating that the new entry has been added to the combo box and to the underlying data source. The code then closes the frmPaymentMethods form.

If the frmPaymentMethods form is not loaded, the user must have clicked Cancel. The user is returned to the combo box where he must select another combo box entry. In summary, the steps are as follows:

Adding Items to a Combo Box or List Box at Runtime

Prior to Access 2002, it was very difficult to add and remove items from list boxes and combo boxes at runtime. The next section, "Populating a Combo or List Box with a Callback Function," illustrates this point. Access 2002 and Access 2003 list boxes and combo boxes support two powerful methods that make it easier to programmatically manipulate these boxes at runtime. The AddItem method allows you to easily add items to a list box or a combo box. The RemoveItem method allows you to remove items from a combo box or a list box. Here's an example:

Private Sub Form_Load()
Dim obj As AccessObject
'Loop through all tables in the current database
'adding the name of each table to the list box
For Each obj In CurrentData.AllTables
Me.lstTables.AddItem obj.Name
Next obj
'Loop through all queries in the current database
'adding the name of each query to the list box
For Each obj In CurrentData.AllQueries
Me.lstTables.AddItem obj.Name
Next obj
End Sub

This code is found in the frmSendToExcel form that's part of the CHAP9EX database. It loops through all tables in the database, adding the name of each table to the lstTables list box. It then loops through each query in the database, once again adding each to the list box.

Populating a Combo or List Box with a Callback Function

As mentioned in the previous section, prior to Access 2002, the only way to add items to a combo box or list box at runtime was to use a Callback function. Although the AddItem method renders the Callback function technique nearly extinct, many legacy applications use Callback functions. The technique is therefore covered in this section.

As you know, it's easy to populate a combo or list box by setting the control's properties. This method is enough for many situations; however, there are times when you might want to populate a combo or list box programmaticallywith values from an array, for example. You might also want to populate the box with table or report names or some other database component.

To populate a combo or list box using code, you create a Callback function, which tells Access how many rows and columns will be in the combo or list box and 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; then it uses its information to populate the combo or list box. The example in Listing 9.2 is found in the frmSendToExcelCallBack form that's part of the Chap9Ex database.

Listing 9.2 Filling a List Box By Using a Callback Function

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 Chapter 14, "What Are ActiveX Data Objects and Data Access Objects, and Why Are They Important?" Each element of the case structure seen in the routine is called as each code is sent by Access. Here's what happens:

• When Access sends the code of 0, the tables and views are counted. The code loops through each table and query in the database. If it is not a system table, its name is added to the sastrTables array. The return value of the function is the number of tables and views in the database.

• When Access sends the code 1, the return value is a unique value equal to the return value of the Timer function.

• When Access sends the code 3, the return value is set equal to the count of tables and queries in the database.

• When Access sends the code 4, the return value is set to 1 (one column).

• When Access sends the code 5, the return value is set to 1, forcing a default width for the combo or list box.

• Access then automatically calls code 6 by the number of times that was returned for the number of rows in the combo or list box. Each time code 6 is called, the form object variable is set equal to a different element of the form collection. The table or query name is returned from the function. The table or query name is the value that's added to the list box.

All this work might seem difficult at first. After you have populated a couple of combo or list boxes, though, it's quite easy. In fact, all you need to do is copy the case structure you see in the FillWithTableList function and use it as a template for all your Callback routines.

Handling Multiple Selections in a List Box

List boxes in Access 97 and above have a Multiselect 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 frmReportEngine form, found in the Chap9Ex database, illustrates the use of a multiselect list box. The code under the Click event of the Run Reports button looks like Listing 9.3.

Listing 9.3 Evaluating Which Items Are Selected in the Multiselect List Box

This code first checks to ensure that the list box is a multiselect list box. If it is, and at least one report is selected, the code loops through all the selected items in the list box. It prints each report that is selected.