An Alternative to the NotInList Event

Mailbag...

Can I Use the Combo Box NotInList Event to Allow an Entry Without Adding it to the List?

"In your tutorial on the combo box NotInList event you show how to accept an entry that isn't on the combo box list by letting the user add the new item to the list. I want my users to have the option to add a new item to the list but I also want them to be able to make an entry without having to add it to the list."

No, you can't use the NotInList event to do this because in order for the NotInList event to fire the Limit To List property of the combo box must be set to Yes, and when this is the case Access absolutely refuses to accept an item unless it's on the list. But there is another way which makes use of the combo box BeforeUpdate event. Here's how it works...

Using the BeforeUpdate Event

The BeforeUpdate fires when the user leaves the combo box having made a change to its value, but before that value is entered into the field (there is a corresponding AfterUpdate event). Because the BeforeUpdate event happens before the new value is committed to the field this event has a Cancel parameter allowing you stop proceedings by setting Cancel = True.

This example makes use of the BeforeUpdate event of the combo box to check the user's entry against the combo box list (or the source of the list - I have offered two solutions). If the entry does not match the list then the user is offered the option to continue by adding it to the list, to continue without adding it to the list, or to return to the combo box and change their entry to a matching one.

The code below uses the same example data as in my original tutorial Mastering the Combo Box NotInList Event so take a look at that if you need to understand how the various items relate to each other.

Checking the User's Entry Against the List

If you have the Limit To List property of the combo box set to Yes then Access will do this for you and automatically fire the NotInList event if a match isn't found. But in order to allow the user the option to make a non-matching entry we must set the Limit To List property to No. This means that we have to set up a routine to check the entry ourselves. Here are two different ways to do it...

If the combo box gets its list direct from a table you can compare the user's entry with the items in the table. For convenience, the entry is first read into a variable called strNewData. Then the DCount function is used to count how many occurrences of that item are found in the combo box's RowSource table. If the result is anything other than zero then the item must already be on the list so the procedure exits without any further action being taken. Note that I have also included a line to exit the procedure if the user has left the combo box empty as this would cause a problem when we tried to compare the list items with a Null value (you could modify this if you wanted to insist on an entry)...

Dim strNewData As String
If IsNull(Me.cboJobTitle.Value) Then Exit Sub
strNewData = Me.cboJobTitle.Value
If DCount("[JobTitle]", "tblJobTitles", "[JobTitle]='
" & strNewData & "'") > 0 Then Exit Sub

An alternative, and possibly more efficient way to check the user's entry is compare it with the actual list (rather than its source table). A combo box list has an ItemData property that can be used to read each list item individually by specifying its index items. Note that when counting items in VBA using their "index" number we usually start from zero. Here, a new variable i is used as a counter. The first part of the procedure is as before, then it loops through the combo box list an item at a time comparing each item with the user's entry. If is finds a match the procedure exits without taking any further action. If the loop finished without exiting this must mean that there was no match.

Dim strNewData As String
Dim i As Integer
If IsNull(Me.cboJobTitle.Value) Then Exit Sub
strNewData = Me.cboJobTitle.Value
For i = 0 To Me.cboJobTitle.ListCount - 1
If Me.cboJobTitle.ItemData(i) = strNewData Then Exit Sub
Next i

Asking the User What They Want to Do

Here I have expanded a little on my original tutorial example. The message is the same, notifying the user that their entry doesn't match the list and asking them if they want to add it, but this time I have added the option to Cancel...

Dim intAnswer As Integer
intAnswer = MsgBox("The job title " & Chr(34) & strNewData & _
    Chr(34) & " is not currently listed." & vbCrLf & _
    "Would you like to add it to the list now?" _
    , vbQuestion + vbYesNoCancel, "Acme Oil and Gas")

Of course, simply showing a message box isn't enough, you have act on the user's response. I have used a Case Statement for this because it's a little easier to read. An If Statement would work just as well. The case statement is in three parts to match the three buttons on the message box...

Dim strSQL As String
SelectCase intAnswer
    Case vbYes
        ' Allow entry and add new item to the list
        strSQL = "INSERT INTO tblJobTitles([JobTitle]) " & _
                 "VALUES ('" & strNewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        blnNewListItem = True
        MsgBox "The new job title has been added to the list." _
            , vbInformation, "Acme Oil and Gas"
    Case vbNo
        ' Allow entry but do not add item to list
        ' No action necessary
    Case vbCancel
        ' Do not allow entry
        MsgBox "Please choose a JobTitle from the list.", _
            vbExclamation, "Acme Oil and Gas"
        Me.cboJobTitle.Undo
        Me.cboJobTitle.Dropdown
        Cancel = True
End Select

If the user clicks the Yes button the procedure adds the new item to the combo box's RowSource table. The process is exactly the same as in the tutorial example but I have added an extra line which sets the value of the blnNewListItem variable to true (more about this later). The result is that their entry is accepted and the new item is added to the list.

If the user clicks the No button then nothing needs to be done. Since the combo box Limit To List property is set to No their non-matching entry is automatically accepted. The list remains unchanged. (NOTE: In fact this part of the case statement could have been omitted completely but I left it in for clarity).

If the user clicks the Cancel button (presumably they have changed their mind and want to undo their entry) they are shown a message reminding them to choose from the list, their entry is removed and the list dropped ready for them to make their choice. Finally the Cancel parameter of the BeforeUpdate event is set to True to cancel the updating of the combo box.

Requerying the List

If the new item is added to the list it won't actually appear until the list is requeried. This happens automatically when a form opens but it should really be done each time a new item is added to the source table. When the NotInList event is used the list gets requeried automatically when the statement Response = acDataErrAdded is executed. That won't happen with this method so you need to write some more code.

The instruction to requery the combo box can not be given as part of the BeforeUpdate code because Access requires that the value the combo box is currently holding has to be saved first. In other words until the BeforeUpdate event is over the combo box is still busy with something else. But right after the BeforeUpdate event comes the AfterUpdate event so this is an ideal place to put the code.

Since I don't want the combo box to be requeried every time an entry is made, but only when the list source has been added to, I added the line blnNewListItem = True to part of the Case Statement just after the new item was added to the table. blnNewListItem is a public variable (i.e. one that is not located inside a procedure) so that it can pass values from one procedure to another. Public variables must be declared in the Declarations area at the top of the code module (just after the line(s) Option Compare Database and/or Option Explicit)...

Public blnNewListItem As Boolean

When a new item is added to the source table the value of this variable is set to True. The code for the combo box's AfterUpdate event simply looks at this variable and, if its value is True, requeries the combo box then resets the variable to False...

Private Sub cboJobTitle_AfterUpdate()
    If blnNewListItem = True Then
        Me.cboJobTitle.Requery
        blnNewListItem = False
    End If
End Sub

Job Done!

Now you have a process that mimics the NotInList event with the difference that its will allow the user to save an entry that doesn't match the combo box list.

^ top

Hit Counter