databasedev.co.uk - database solutions and downloads for microsoft access

How to use the Microsoft Access Not In List event of a combo box

It is often good practice to provide your users with a list of values that they can assign to a record when entering data into a Microsoft Access Database form. With the use of pre filled lists, you can prevent the user from making incorrect data entry, and minimise the change of spelling mistakes or incorrect choice of values.

On occasions, where you have a set selection displayed within a form's combobox, you may wish to allow the user to add new values to the underlying table.

This case would call for you to make use of the combobox's NotInList event.

Firstly, there are certain properties that need to be addressed:

• The combobox must have it's RowSource set to either a table or a query (not value list or field list)
• The combobox must have the RowSourceType set to Table/Query
• You must set the Limit To List property to Yes (This event will only be triggered if this property is set)

Now if we take the following form, which has all of the above properties set for the combo box, and contains a list of values already entered into the Book Categories table (tblBookCategories):

Form containing combo box with Limit To List property set to Yes

If we were to attempt to add a new Book Category to this list, with the properties set as such, we would expect it to give an error message. However, with the Not In List event, we can allow users to update the list if a new value is required.

The following shows what happens when a new value is entered into the combo box (cboBookCategory):

When the user enters a value in the combo box that is not in the list, they will be presented with the message box.

This is triggered by the following code, that is added to the combo box (cboBookCategory) Not In List event:

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

    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String

    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"

    i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Book Category...")
    If i = vbYes Then
        strSQL = "Insert Into tblBookCategories ([strBookCategory]) " & _
                 "values ('" & NewData & "');"
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
End Sub

This will add the new value to the combo box and allow it to be assigned to the record.

There are many variations and approaches to applying this functionality so if this approach does not suit you can find further examples on the Microsoft Knowledge Base, searching for NotInList event.

For a Microsoft Access 2000 database downloadable example please download from either the Microsoft Access forms menu or the Microsoft Access downloads menu.