Automating Microsoft Access with VBA

Adding to the List or Not

The combo box control has a special quality that the list box control lacks. You can enter a value into the text box portion instead of choosing an item from the drop-down list. By default, you can use that value, but you can't add that value to the list. Adding a new item to the control's list takes a bit of work.

Entering a non-list item triggers the combo box control's NotInList event. By default, nothing happens when the event is triggered, but the event passes two arguments, NewData and Response, that you can use to add a value to a list.

NewData equals the input value in the combo box control's text box. Response indicates how the event was handled by the following intrinsic constants:

• acDataErrDisplay This is the default value and displays a default message. Use this constant when you don't want to allow the users to add a new value to the combo box list.

• acDataErrContinue Displays a custom message to the users, most likely asking them if they want the new value added to the control's list. When the response is Yes, the item is added to the list and the code must set Response to acDataErrAdded. When the response is No, the code sets the Response argument to acDataErrContinue.

• acDataErrAdded This value indicates that the value stored by NewData has been added to the combo box control's list.

Updating a Value List

The easiest type of list to programmatically update is the value list. There are many ways to approach the problem, so this section takes the path of least resistance for now. Remember, the Row Source property for a value list control is an explicit list of values separated by the semicolon character (;). To update it, you simply need to modify the Row Source property accordingly.

Now let's create a simple unbound value list control that you can quickly update. (Bound value list controls are impractical and as such, are rare.) To create the example control shown in Figure 12.9, do the following:

1. Open a new, blank form in Design view and add a combo box.

2. Name the combo box cboColors. Change the caption of the combo box's label to "Colors:".

3. Set the Row Source Type property to Value List.

4. Set the Row Source property to the string, "Red;White;Blue".

5. Save the form, and then open it (refer to Figure 12.9) in Form view.

Now, enter the color Yellow in the text box portion. The text box accepts the value, but does nothing with it. That means, the next time you want to select yellow, you must re-enter it instead of selecting it from the list.

Adding new items to the list takes a bit of VBA. To make the necessary changes, do the following:

1. Return the form to Design view and change cboColors' Limit to List property to Yes.

2. Choose [Event Procedure] from the On Not in List property. Click the Builder button to open the form's module and enter the NotInList event's stub statements.

3. Enter the following code to complete the procedure:

   Private Sub cboColors_NotInList(NewData As String, Response As Integer) Dim bytResponse As Byte bytResponse = MsgBox("Do you want to add " & _ cboColors.Value & " to the list?", vbYesNo) If bytResponse = vbYes Then Response = acDataErrAdded cboColors.RowSource = Me!cboColors.RowSource _ & ";" & NewData Else Response = acDataErrContinue Me!cboColors.Undo End If End Sub

4. Return to the form and click View to display it in Form view.

5. Re-enter Yellow in the text box component.

6. When Access displays the message shown in Figure 12.10, click Yes. Figure 12.11 shows the modified drop-down list, which now includes the string Yellow. If you open the Properties window, you'll see that the Row Source property is also updated to include the string yellow.

   Figure 12.10. The MsgBox statement displays this message when you enter a non-list item.

   Figure 12.11. Answering Yes to the previous message adds the non-list item to the list.

Entering a non-list item triggers the NotInList event, which displays the message box (see Figure 12.10) and stores your response in the bytResponse variable. The If statement then adds the new item, or doesn't, depending upon the value stored in bytResponse.

When adding the new item, the procedure also resets the Response argument to acDataErrAdded so the actual insert can be processed (or not) internally. Notice that the Else action resets the Response argument to acDataErrContinue and then uses the Undo method to delete the entry from the text box component.

Updating a Table/Query List

The Table/Query list is probably the most common of the three types of control lists. It's also the most flexible. To display an item in the list, you simply add it to the data source (the table or query). These lists can be bound or unbound and your VBA solution for adding a non-list item will be different for each type.

The simpler solution of the two is with a bound control because all you have to do is save the non-list item to the underlying table and then re-query the control to update its list. To understand this simple process, you need a new combo box control and a table with some data.

First, create a new table named Colors and add one text field named Colors. Enter red, white, and blue into the lone field. Next, bind a new form to the Colors table by selecting Colors in the Tables list. Then, choose Insert, Form and double-click Design View in the New Form dialog box.

In the new, blank form, insert a combo box control, name it cboBound, and set its ControlSource property to Colors. Change the combo box's label's Caption property to Bound. Set the Row Source property to the following SQL statement or use the Query Builder to create the equivalent:

SELECT DISTINCT Colors FROM Colors ORDER BY Colors

This statement retrieves a unique list of items from the Colors field in the Colors table for the combo box control's list.

For a brief review of SQL, see "Review of Access SQL," p. 347.

As is, the control displays a list of colors in the drop-down list, which it gleans from the Colors field in the Colors table. It also highlights the bound record's data as shown in Figure 12.12. At this point, you could enter a non-list item and the control would accept it and update the corresponding record with that item. However, the control won't immediately display the new item in its list. To display the new item, you must close and re-open the form. Then, the control will list any new unique items in its list.

The easiest solution is to control when the input is saved and re-query the control, and you need VBA for that. With the form in Design view, double-click cboBound and choose [Event Procedure] from the After Update property's drop-down list (in the Properties window). Then, click the Builder button to the right. Next, complete the event procedure using the following code:

Private Sub cboBound_AfterUpdate() DoCmd.RunCommand acCmdSaveRecord cboBound.Requery End Sub

TIP

Use the AddItem method to add the current value to the list when using a Value List control. If you need to be compatible with older systems, keep in mind that the AddItem method isn't available in versions earlier than Access 2002.

Return to the form and display it in Form view. Click the New Record button on the Navigation toolbar, enter yellow, and press Enter. Figure 12.13 shows the new list. As you can see, it contains the item you just entered, yellow.

This example is extremely simple because it assumes every new item will be added to the list. In addition, keep in mind that list controls aren't generally best for data-entry purposes. It's too easy to choose a new item from the list, and overwrite the existing data for that record when that's not really what you want to do. Use bound list controls wisely.

You can also grab a list of items from an unbound data source, which means you can't accidentally change existing data. But you can update the underlying data source and hence the list. This is a good solution for controls that rely on lookup tables (as opposed to actual stored data).

Insert a new combo box into the unbound example form (the first form you created). Name the combo box cboUnbound and use the following SQL statement as the Row Source property:

SELECT DISTINCT Colors FROM Colors ORDER BY Colors

Then, set the Limit To List property to Yes.

The SQL statement is identical to the one you used in the bound example. Because the control isn't bound, a more complex solution is needed to update the list's data source. At this point, the list displays a unique list of colors retrieved from the Colors table. If you enter a non-list item, Access rejects it and displays an error message.

With the form in Design view, double-click cboUnbound and select [Event Procedure] from the On Not In List event property's drop-down list, and then click the Builder button to launch the form's module. Complete the NotInList event procedure as follows:

Private Sub cboUnbound_NotInList(NewData As String, Response As Integer) Dim cnn As New ADODB.Connection Dim strSQL As String Dim bytResponse As Byte Set cnn = CurrentProject.Connection bytResponse = MsgBox("Do you want to add this new item " _ & "to the list?", vbYesNo, "New Item Detected") If bytResponse = vbYes Then strSQL = "INSERT INTO Colors(Colors) VALUES('" _ & NewData & "')" Debug.Print strSQL cnn.Execute strSQL Response = acDataErrAdded ElseIf bytResponse = vbNo Then Response = acDataErrContinue Me!cboUnbound.Undo End If End Sub

Return to the form and display it in Form view. Enter black into the unbound control's text box component, which triggers the control's Not In List event and displays the message box shown in Figure 12.14. Click Yes and the If statement executes the INSERT INTO SQL statement, which inserts the current entry into the list's data source (the Colors table). Figure 12.15 shows the new list. If you open the Colors table, you'll also find the new entry there.

You don't have to add the item. When Access displays the message box, click No. Setting Response to acDataErrContinue lets Access continue without making any changes to the data source. In a working situation, you'd probably have some use for entered items that don't make it to the list. To keep this example simple, the Undo method just deletes them.

NOTE

Another common solution to the accompanying problem is to use a Recordset object to update the underlying data source. There's nothing wrong with using a Recordset object, but you haven't been introduced to that object yet (see Chapter 17, "Manipulating Data with ADO," for more information on this object). The SQL solution requires less code and performs quicker than the Recordset object.