Adding new items to a combo box

Back to Hints and Tips Index                                             Back to Main Index

(Access 2000/2002/2003)

When you create a form for data entry with a combo box in most cases the combo box will only let you choose values that appear in the drop-down list associated with the combo box. this is generally desirable as it enforces the integrity of your database.

There may be situations where you do wish to allow users to add new items to a list, but only when that item is also added to the table that provides the values for the combo box. In this way the relationship between the tables is maintained without frustrating your users.

The following article describes one method for achieving this outcome.

Create the following tables:

tblLocations

Add a few records to this table.

tblClients

1. Create and save a form with the name frmClients based on the tblClients table:

1. Use the form to add a few clients.
2. When you attempt to assign a client to a location that is not in your list of locations:

                   

you receive the following message:

     

This message is a form of validation confirming that you do not yet have such a location in your tblLocation table. A user could now abandon the entry of the new client, go to the locations table and add the new record. To avoid that process, you may wish to give the user the ability to add the new location "on-the-fly".

We can achieve this by responding to one of the "events" associated with a combo box control; ie we can tell Access how to handle the situation when an item that is typed is not in the list of legitimate values. Perhaps not surprisingly, this event is called the On Not In List event!

1. To make use of this event switch to the frmClients design view and select the combo box:

2. Bring up its properties, select the Event tab, click the On Not in List event, select the [Event Procedure] item from the drop-down list:

3. Click the Build button . Access will open the form’s code module and create a sub procedure ready to enter your code:

This procedure has two "arguments" – ie the information that has to be sent (or "passed") to the procedure. NewData is the value of the item that is not in the list yet and Response lets you tell Access what to do after the procedure finishes.

1. Enter the following code:

Private Sub LocationID_NotInList(NewData As String, Response As Integer)

' Declare the variables you need

Dim strMessage As String

Dim dbClients As Database

Dim rstTypes As DAO.Recordset

' Ask whether the user is to add the current value to the list

strMessage = "Are you sure you want to add '" & NewData & "' to the list of locations?"

If MsgBox(strMessage, vbOKCancel + vbQuestion, "Client entry") = vbOK Then

' If the user responds 'OK' then

' Open the tblLocations table and add the new value

' Open the database object

Set dbClients = CurrentDb

' Open the tblLocations table

Set rstTypes = dbClients.OpenRecordset("tblLocations")

' Use the AddNew method of the open recordset - ie the tblLocations table

rstTypes.AddNew

' Assign the value the user typed in the combo box to the

' LocationName field in the table

rstTypes!LocationName = NewData

' Use the Update method to save the change to the recordset - ie the Locations table

rstTypes.Update

' Requery the list so the new value will appear in future and move to the next record

Response = acDataErrAdded

Else

' If the user responds with 'Cancel' then

' display the standard error message and redisplay the list

Response = acDataErrDisplay

End If

The lines of code beginning with a single ‘ are comments; they appear in green text in your VBA code and are not executed; they are designed to provide explanatory information about the code.

For this code to work, the Access database you are working in needs a special reference to tell it you are using DAO (Data Access Objects). To do this you will need to use References.

1. From the Tools menu of the Visual Basic Editor select References.

2. "Tick" the Microsoft DAO 3.6 Objects Library (it may be the 2.5 version) in the list of Available References (you may need to scroll well down the list of Available References to find the required item).

Adding a record to a list in this way is only one possibility. It worked well in our situation where the tblLocations table was a simple lookup table with only two fields (one of which was AutoNumber and therefore created automatically); it would not be appropriate if we had to add other fields whenever we were adding a new location.

In this situation, the On Not in List event might be used to display another data entry form for entering a new record into the tblLocations table. This technique will be covered in our next newsletter.

Back to Hints and Tips Index                                             Back to Main Index