List controls in Microsoft Access, such as the combo box and list box, offer choices to the user. From their point of view, they don’t have to enter anything — they simply select an item. From the application developer’s perspective, the database is protected from typos and other erroneous data.
However, on occasion, you may need a list control that’s a bit more flexible, allowing users to update the control’s list. Handing over a bit of control, in the right circumstances, can be helpful for everyone, including you. Users don’t have to wait until you manually update the list to continue their work and you aren’t interrupted to update the list.
This blog post is also available in PDF form as a TechRepublic download, which includes a sample Access database.
Adding or deleting items from a combo box isn’t difficult. If it’s a one-time change, simply update the control’s Row Source property. Another option is to let users add items to the control’s list on the fly. This choice is easy for users — they simply enter the item into the control’s text box component. Your part takes a little more work. You have to program the control to accept non-list items.
Entering a non-list item passes two values to the control’s Not In List event:
- NewData passes the non-list item.
- Response determines how the event handles NewData using the following intrinsic constants:
- acDataErrDisplay is the default. This constant won’t accept a non-list item and displays a default error message.
- acDataErrContinue allows the event to continue, as the code determines.
- acDataErrAdded means NewData has been added to the list.
What makes it all so interesting is that non-list items interact with the control’s list source, or the Row Source property. This property determines how Access populates the control’s list:
- The Value List setting displays an implicit list of values.
- The Table/Query setting displays values from a table or query.
- The Field List setting displays the field names from a data source. The chances are you won’t want to alter a list of field names, so we’re not going to explore this type.
Modify a Value List control
One way to populate a combo box is to use an implicit list of items. Updating this list is a simple process — your choice is deciding whether users can update it. When the answer is yes, the solution requires just a bit of code in the combo control’s Not In List event. Let’s create a simple updateable, unbound combo box:
- In a blank form, insert a combo box control and name it cboMetals.
- Set the Row Source Type property to Value List.
- Set the Row Source property to the following string: Gold; Silver; Copper. A value list uses the semicolon character (;) to delimit list items.
- Set the Limit To List property to Yes. (Don’t skip this setting or your control won’t work as expected.)
- Save the form and view it in Form view.
Click the control’s drop-down arrow to view its list, as shown in Figure A. The control displays the items you entered as the Row Source property. Since the control is unbound, you can select any item without affecting any data.
Display a fixed list of items
The control lets you enter a non-list item, but when you press Enter or Tab, Access displays the error message shown in Figure B. That’s the Limit To List property at work. As is, the control rejects all non-list items.
A control can reject any non-list items
The control needs an additional step when you don’t want the control to reject all non-list items. That’s what the code in Listing A does — it gives the user a choice. With the form in Design view, click the Code button on the Form Design toolbar to launch the form’s module and the enter code in Listing A. Return to the form, save it, and view it in Form view.
Private Sub cboMetals_NotInList(NewData As String, _ Response As Integer) 'Update value list with user input. On Error GoTo ErrHandler Dim bytUpdate As Byte bytUpdate = MsgBox("Do you want to add " & _ cboMetals.Value & " to the list?", _ vbYesNo, "Non-list item!") 'Add user input If bytUpdate = vbYes Then Response = acDataErrAdded cboMetals.AddItem NewData 'Update RowSource property for versions 'XP and older. 'cboMetals.RowSource = _ ' cboMetals.RowSource _ ' & ";" & NewData 'Don't add user input ElseIf bytUpdate = vbNo Then Response = acDataErrContinue cboMetals.Undo End If Exit Sub ErrHandler: MsgBox Err.Number & ": " & Err.Description, _ vbOKOnly, "Error" End Sub
Entering a non-list item, such as Iron, triggers the Not In List event, which displays the message box shown in Figure C. If you click Yes, the code adds the non-list item, as shown in Figure D. If you click No, the code doesn’t add the item and clears it.
The user can choose whether or not to add the non-list item
Adding the non-list item updates the control’s list
The Limit To List setting must be set to Yes and you must set Response to the appropriate constant for this event procedure to work.
Be sure to test this event thoroughly and update it with a custom error-handling routine. In addition, the If statement has a commented expression that updates the Row Source property. If you’re using Access XP or earlier, use the Row Source property setting statement instead of the AddItem method.
For better or worse, this procedure updates the list only for the current work session. You can’t permanently update the value list in Form view. The only way to accomplish that is to open the form in Design view, update the value list, and then save the form. The truth is that it’s easier to change your strategy than to update a value list programmatically (although it can be done).
Modify a Table/Query control
Sometimes the data you want to display in a combo list is already in a table. A simple query retrieves the data for you, which makes the control dynamic. If the data in the table or query changes, so does the control’s list. Or you may want to store a lookup list in a table instead of using a value list control.
The simplest solution is to save each item, but only if you’re certain that you want to add every non-list item to the list. Using the previous example of metals, let’s create an updateable combo box that retrieves its list items from a table.
First, you need a new table. The simplest structure will do for our purposes, so create a new table with a single text field named Metals. Name the table tblMetals. In Datasheet view, enter the three metals from the previous example: Gold, Silver, and Copper. To create a combo box control, do the following:
- In a form bound to tblMetals, insert a combo box control and name it cboMetals.
- Set the control’s Control Source property to Metals.
- Set the Row Source property to the following SQL statement: SELECT DISTINCT Metals FROM tblMetals ORDER BY Metals.
(Traditionally, a combo box based on data will include a primary key field as well as the descriptive text field. We’re working with a single field just to simplify the example.)
It’s important to remember that the combo box is bound to the Metals field. Currently, there are three records in the bound table (tblMetals). If you enter a new metal into one of the existing records, you’ll replace the existing metal. This may or may not be what you want, so be careful to protect existing data if necessary.
Whether you’re updating existing values or entering new ones, the list will update accordingly — eventually. For instance, if you replace Gold with Iron, the control will pass that change to the table, but it won’t update the list until you close and reopen the form. That’s probably not what you want. The simplest fix forces the control to save the record and re-queries the control. Open the form’s module and enter the code in Listing B for the After Update event:
Private Sub cboMetals_AfterUpdate() 'Save record and requery combo control. DoCmd.RunCommand acCmdSaveRecord cboMetals.Requery End Sub
In Form view, replace Gold with Iron and press Enter or Tab. As you can see in Figure E, Iron now replaces Gold in the list. If you don’t want to replace Gold, add a new record for Iron. Then, the list will include all four metals.
Replace existing list items or add new ones
The problem with this solution is that it’s automatic — it passes along every non-list item to the list. Most likely, you’ll want to users more involved in the decision to add, or not add, non-list items.
In addition, unless you have a specific reason for doing so, avoid a bound list control that interacts with data. A better solution retrieves data from a lookup table. A lookup table stores values for the interface and other purposes, but those values aren’t usually part of the database’s natural data. When that’s the case, use an unbound combo box and a lookup table as follows:
- Create a new unbound form, insert a combo box control, and name it cboMetals.
- Set the control’s Row Source property to the following SQL statement: SELECT DISTINCT Metals FROM tblMetals ORDER BY Metals.
- Set the control’s Limit to List property to Yes.
The SQL statement is identical to the bound example. However, because this one is unbound, the control won’t update the source table, tblMetals. Right now, if you enter a non-list item, the control will reject it because the Limit To List property is Yes. With a bit of code, you can update the source table and repopulate the list.
Open the form’s module and enter the event procedure shown in Listing C.
Private Sub cboMetals_NotInList(NewData As String, Response As Integer) 'Allow user to save non-list items. Dim cnn As New ADODB.Connection Dim strSQL As String Dim bytUpdate As Byte On Error GoTo ErrHandler Set cnn = CurrentProject.Connection bytUpdate = MsgBox("Do you want to add " & _ cboMetals.Value & " to the list?", _ vbYesNo, "Non-list item!") If bytUpdate = vbYes Then strSQL = "INSERT INTO tblMetals(Metals) " & _ "VALUES ('" & _ NewData & _ "')" Debug.Print strSQL cnn.Execute strSQL Response = acDataErrAdded ElseIf bytUpdate = vbNo Then Response = acDataErrContinue Me!cboMetals.Undo End If Exit Sub ErrHandler: MsgBox Err.Number & ": " & Err.Description, _ vbOKOnly, "Error" End Sub
This event procedure is similar to the first example. The only difference is that this one uses a SQL INSERT INTO statement to add the non-list item to the control’s source.
In Form view, enter Gold and press Enter or Tab. The control’s Not In List event displays a message box that’s identical to the one in the first example (Figure B). Click Yes and the code concatenates the current non-list item (Gold) into an INSERT INTO SQL statement that evaluates as:
INSERT INTO tblMetals(Metals) VALUES (’Gold’)
Executing that statement adds Gold to tblMetals, which updates cboMetal’s list, as you can see in Figure F.
Update an unbound control
As before, click No if you don’t want to add the non-list item to the control’s list.
In a working situation, you’d probably add code that actually does something with non-list items you don’t add to the list. In this example, the Undo method just deletes those entries. Remember to delimit the list items appropriately. This example uses the single apostrophe (’) to delimit text. Use the pound sign (#) to delimit date or time values. Numeric values don’t require a delimiter.
Controls that control
Versatile controls can make everyone’s work a bit easier. When necessary, you should let users update a list control themselves. But you’ll want to check the list occasionally to make sure users aren’t adding inappropriate items. In fact, you might add a routine that notifies you via e-mail when a user updates a list. For the most part, with good training and quality control measures, allowing users to update a control’s list can be a helpful compromise for everyone.
Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is “Mastering Microsoft SQL Server 2005 Express,” with Mike Gunderloy, published by Sybex. Other collaborations with Mike Gunderloy are “Automating Microsoft Access 2003 with VBA,” “Upgrader’s Guide to Microsoft Office System 2003,” “ICDL Exam Cram 2,” and “Absolute Beginner’s Guide to Microsoft Access 2003″ all by Que. Currently, Susan volunteers as the Publications Director for Database Advisors at http://www.databaseadvisors.com. You can reach her at email@example.com.