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
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
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
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
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
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
' Allow entry and add new item to the list
strSQL = "INSERT INTO tblJobTitles([JobTitle]) " & _
"VALUES ('" & strNewData & "');"
blnNewListItem = True
MsgBox "The new job title has been added to the list." _
, vbInformation, "Acme Oil and Gas"
' Allow entry but do not add item to list
' No action necessary
' Do not allow entry
MsgBox "Please choose a JobTitle from the list.", _
vbExclamation, "Acme Oil
Cancel = True
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
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
blnNewListItem = False
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.