Access Cookbook

Create a Combo Box That Accepts New Entries

Problem

You’re using combo boxes for data entry on your forms, and you want to allow users to add a new entry to the list of values in the combo box. Can you do this without forcing users to close the data entry form, add the record using a different form, and then return to the original form?

Solution

You can use the NotInList event to trap the error that occurs when a user types into a combo box a value that isn’t in the underlying list. You can write an event procedure attached to this event that opens a pop-up form to gather any necessary data for the new entry, adds the new entry to the list, and then continues where the user started. This solution demonstrates how to create combo boxes that accept new entries by using the NotInList event and the OpenArgs property of forms.

Load the sample database 09-06.MDB and open the frmDataEntry form in form view. This form allows you to select a U.S. state from the combo box, but the list is purposely incomplete for the example. To enter a new state, type its abbreviation in the form and answer Yes when Access asks whether you want to add a new record. A form will pop up, as shown in Figure 9-18, to collect the other details (in this case, the state name). When you close the form, you’ll be returned to the original data entry form with your newly added state already selected in the combo box.

Figure 9-18. Adding a new record to the underlying table

To add this functionality to your own combo ...