Build Better Access Forms:
Mastering the Combo Box NotInList Event
It's All About Usability
One of the most important tasks for the database designer is to
prevent "bad" data getting into the database. Bad data can happen
because the user might not know what they should enter into a field
or what restrictions might apply, or perhaps they just make a typo.
One way to help prevent bad data is to make the job of entering
data as easy as possible for the user. Make it obvious what the user
has to do and they don't have to guess and risk getting it wrong.
Make it easy to do the job and the user is less likely to make a
mistake and enter something they didn't mean.
In short, the database designer's job is to make their database
usable.
The Benefits of Combo Boxes
A favourite tool for helping the user in their task is the combo
box. This familiar tool consists of a text box to which is attached
a list of possible entries, and is recognised by the button showing
a downward pointing arrow. They are often referred to as
drop-down or pull-down lists but we propellorheads call
them combo boxes (the name indicating that they are a
combination of a text box and a list box).
The combo box can be used anywhere you would use a text box where
it is possible to provide a list of acceptable entries. They are
most commonly used for text entries but can equally be used for
numbers or dates.
Combo boxes help the user because they can offer a number of
possible entries, but their functionality goes further than this.
The database designer can specify whether or not the the user can
make an entry that is not contained in the list. If the Limit To
List property of the combo box is set to No the user is
permitted to make any entry they choose (unless the Validation
Rule property, or the properties of the underlying field, impose any additional restriction). But if the
Limit To List property is set to Yes the form will not
accept any entry for that field other than those included on the
list. This lets you be absolutely certain that nothing will get
entered into the field unless it is an acceptable entry.
Suitable Sources for a Combo Box List
The combo box gets its list from the definition of its Row
Source and Row Source Type properties. The Row
Source Type can be Table/Query (the name of a table, a stored query
or an SQL statement) or Value List (a text string listing
each item as it should appear in the list).
A Fixed List Built From a Table
Having tried the various options for supplying the Row Source
of a combo box I have come to the conclusion that, in most
instances, a table is best. If it is possible that the list might
need to change by the addition or removal of items I usually build a
tool to allow the user to do this (I prefer not to allow
users direct access to the tables themselves!). The table can be
simple with a single field (more for a multi-column list) and
setting the table as the Row Source just requires the Row
Source Type to be set to Table/Query and the
table's name specified as the Row Source...
 
A Dynamic List Built From a Query
In some instances it might be appropriate to have a dynamic list
- one whose contents change to reflect data already in the
database. The list is dynamic because it is
created from a query. Each time the form is opened the query is run
and generates the list using the most up-to-date data as its source.
The query could be based on the current table or a different one.
In the same way as you might specify the name of a table as the
Row Source you can name a stored query. This is why, when you
click the down-arrow next to the Row Source property text box
a list of available tables and queries is displayed...
Rather than use a stored query, I prefer instead to use an SQL
statement. You can type the SQL statement direct into the Row
Source property text box or click the build button (
) next to it to open the query-builder and have Access write the SQL
for you...
A typical SQL statement for creating a list of unique entries
will look something like this:
SELECT DISTINCT tblStaff.[JobTitle]
FROM tblStaff
WHERE tblStaff.[JobTitle] Is Not Null
ORDER BY tblStaff.[JobTitle];
Note that the SQL statement uses SELECT DISTINCT to ensure
that only unique values appear in the list (i.e. that there are no
duplicate entries); the WHERE clause specifies Is Not Null so
that blank entries in the source table are excluded (so there is not
a blank entry on the list); an ORDER BY clause is included to sort
sort the list in ascending alphabetical order.
Choose a method to suit the individual needs of each combo box.
Each method will produce a list but the way the form behaves when
the user makes an entry that doesn't match a list item depends upon
you.
Using a Value List
If the Row Source Type is Value List the Row
Source specifies the actual list items themselves (separated by
semicolons). This is best used when the list items are unlikely to
change, and the list contains just a few items. I often use a value
list for a "Gender" field where there are only two possible entries
(Male and Female) because whilst most people
understand that they can only be one or the other (I'm not getting
into discussions about hermaphrodites or transgender issues here!) a
surprising number of people are confused about how they should
specify it.
For Gender I usually create a text field and use "M" to denote Male and "F" to
denote Female. To make it crystal clear to the user exactly
what the letters mean I use a combo box with a value list row
source. The combo box has 2 columns. The first column is the "bound"
column (i.e. the one whose data gets put into the field) and
contains the letters "M" and "F". The second column (just there for
information) contains the text "Male" and "Female".
When specifying the value list row source for a single column
simply supply a list of items separated by semicolons (e.g.
Red;Yellow;Green;Blue). If you require a multi-column list you
should supply all the items for each row in turn (e.g.
M;Male;F;Female) then use the Column Count, Bound
Column and Column Widths properties to define how the
list appears...
These property settings result in a combo box list looking like
this...
I could have chosen to hide the first (bound) column by setting
its width to zero. This would remove it from the list but would not
affect the entry of its data into the field.
Because the Limit To List property of the combo box is set
to Yes the combo box refuses to accept any entry that isn't
on its list (when there is more than one column, the bound column is
the one that has to be matched). The form does not check the user's
entry until they attempt to leave the combo box (for example by
clicking on another field, pressing their TAB key, moving to another
record, or trying to save the current record). At this point, if the
entry is refused, the NotInList event fires and Access
displays its standard message:
When the user clicks the OK button Access takes them back
to the combo box and opens its list so they can choose an acceptable
entry. The only way the user can move on is to either make a choice
from the list or remove their original entry from the combo box
(e.g. by pressing their ESCAPE key).
Although the standard message is quite friendly you might like to
give the user a bit more help by displaying a custom message.
Responding to User Input
Regardless of how you build the combo box list, if the Limit
To List property of the combo box is set to No the form
will accept whatever the user types into the box. The only
restrictions that might apply are ones you might have created in the
Validation Rule property or those inherited from the form's
underlying table (such as data type - the form won't accept text in
a date or number field for example).
If you set the Limit To List property of a combo box to
Yes any input from the user that does not match an item on the
list prompts the NotInList event to fire. What happens next
is up to you. You can leave it at that. Access will display its
standard message (see example above) and
the form absolutely refuses to accept any input into the combo box
other than one which matches an item on the list. But you still have
a number of options...
Programming the NotInList Event
With a little VBA code you can take charge of proceedings and
decide for yourself how to handle things. Start by creating a code
procedure. Here's a quick way to get started:
- In form design view open the properties window of the combo
box (by right-clicking on it and choosing Properties).
- Click the Event tab and double-click the On Not in List
property text box. You will see the text [Event
Procedure] appear in the text box.
- Click the build button (
) to open the form's code module in the Visual Basic Editor with
an empty code procedure...
The first line of the procedure declares two parameters (shown
between the brackets) that you can make use of in your code:
Private Sub cboGender_NotInList(NewData As String,
Response As Integer)
NewData holds the text that the user entered into the
combo box and that was rejected, causing the event to fire. You can
make use of this, for example, to read back the unacceptable entry
to the user in a custom message. Alternatively, you could offer to
add it to the list by writing it into the list's source table. This
parameter is treated as a String (a piece of text) regardless
of the field's data type.
Response represents an instruction to Access on how to
proceed. There are three possible values represented by constants
(all VBA constants can be referred to by name or by their numerical
value which is why this parameter is an Integer):
acDataErrContinue (return to the combo box and open the list),
acDataErrDisplay (show the standard message),
acDataErrAdded (the item has been added to the list).
Here are some examples of how you might program the NotInList
event procedure...
Refuse the Entry with a Custom Message
The standard message is self-explanatory but you might want to
add a personal touch...
Private Sub
cboGender_NotInList(NewData As String,
Response As Integer)
MsgBox "Sorry, I can not accept that entry." & vbCrLf & _
"Please choose an item from the list." _
, vbExclamation, "Acme Oil and Gas"
Response = acDataErrContinue
End Sub
This code results in the following message...
When the user clicks the OK button they are returned to
the combo box and Access opens the list automatically so they can
see the list of options. The form refuses to accept the entry. Note
the use of acDataErrContinue to instruct Access not to
display the standard message, and the use of vbCrLf to force
a line break in the message text.
Refuse the Entry and Include It in the Message
When the NotInList event fires the offending entry is
passed to the NewData parameter and is available for you to
use in your code. In this example it is simply read back to the user
as part of the message...
Private Sub
cboGender_NotInList(NewData As String,
Response As Integer)
MsgBox "You can not enter " & Chr(34) & NewData & Chr(34) & _
" here. Enter M for Male or F for Female." & vbCrLf & _
"If you do not know the person's gender please
leave " & _
"the box empty.", vbExclamation,
"Acme Oil and Gas"
Response = acDataErrContinue
End Sub
Note that the code makes use of the character code Chr(34)
to insert quote marks into the message...
Again, acDataErrContinue is used to return the user to the
combo box without displaying the standard message.
Offer to Accept the Entry By Adding It to the List
It is possible to accept an invalid entry providing that it is
added to the combo box list. In many cases it is inadvisable to
allow this because the purpose of having a limited list of entries
is to control user choice. Allowing the user the option to add a new
item to the list effectively removes that control (although you
could build a degree of control into the code). But it is sometimes
appropriate and at least it makes the user think about the entry
they have made.
I occasionally use this feature at the testing stage of a
database when its features are still being explored by the users and
the composition of its lists has not been finalised.
The following example illustrates how this can be done. It is
important to note that, if you are going to allow entries to be
added to the Row Source of the combo box, the Row Source should
ideally be a table designed specifically for that purpose, or a
query based on that table.
I'll show the code a piece at a time to begin with. You can find
the entire procedure at the end of this section.
On detecting an invalid entry, my code displays a message box
asking the user if they want to add the new item to the list...
intAnswer = MsgBox("The job title " & Chr(34) &
NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")
This is normal message box code displaying a message with "Yes"
and "No" buttons and storing the user's response in a variable that
I have called intAnswer...
An If Statement then handles the user's response. If the
user clicks the "Yes" button the code must add their entry to the
Row Source table...
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblJobTitles([JobTitle]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new job title has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
I have used an SQL statement to add the new value to the table
because the procedure is very simple and is the same regardless of
which version of Access is being used. This one is similar to an
Access "Append" query and since it is an "action" query it can be
implemented in VBA using DoCmd.RunSQL. Note that I have also
used DoCmd.SetWarnings to suppress (then enable again)
warning messages because otherwise Access would ask the user's
permission to add a record to a table.
I have added a message box at this point to confirm to the user
that the new item has been successfully added to the list...
Finally, I used acDataErrAdded to tell Access that I have
added the item to the combo box's Row Source. When it receives this
information it requeries the combo box to refresh the list then
compares the entry with it again. If everything is OK the form
accepts the entry and allows the user to move on.
The next time the user opens the combo box list they will see
that the new item has been added. In my example I used an SQL
statement (like the one described earlier)
based on a purpose-built table for the Row Source of the combo box.
Using an SQL statement rather than the table itself allows me to
specify that the list is sorted in alphabetical order so that the
new item appears in the appropriate position in the list. If I had
used the table itself as the Row Source, each new item would be
added to the end of the list. Here's the finished result...
If the user chooses not to add the new item to the list (perhaps
they just made a typo) the second part of the If Statement is
implemented...
Else
MsgBox "Please choose a job title from the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrContinue
End If
It displays a custom message reminding the user to choose a valid
entry from the list...
Then acDataErrContinue instructs Access to return to the
combo box, without accepting the entry, and open the list...
NOTE: Instead of displaying a custom message I could have used
acDataErrDisplay or, since this is the default value of the
Response parameter, left out the Else part of the If
Statement altogether and allowed Access to display its default
message.
Here is the full code procedure:
Private Sub cboJobTitle_NotInList(NewData As String,
Response As Integer)
On Error GoTo
cboJobTitle_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The job title " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblJobTitles([JobTitle]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new job title has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose a job title from the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrContinue
End If
cboJobTitle_NotInList_Exit:
Exit Sub
cboJobTitle_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboJobTitle_NotInList_Exit
End Sub
Note that I have added a simple error handler to the code. It is
always advisable to include error handling in your Access VBA code,
especially when it manipulates a recordset as this does, in case
something unforeseen happens (e.g. the new entry might be of the
wrong data type or it might exceed the specified field size).
You could easily write additional code to examine the entry
before adding it to the list to see that it satisfies any
requirements you might have, and thereby exercise some control over
what gets added.
Adding an Item to a Value List
In the previous example items are added to the combo box list by
adding them to the table on which the list is based. It is also
possible to add an item to a value list. The technique is very
simple - no SQL statement is required so it (and its associated
DoCmd statements) can be omitted. Instead a single line of code
something like this can be substituted:
Me.cboJobTitle.RowSource = Me.cboJobTitle.RowSource
& ";" & NewData
This line takes the current Row Source value list then adds a
semicolon followed by the user's entry. It has some disadvantages...
The new item appears at the end of the list (I could write some
code to sort the list into alphabetical order each time an item is
added but I'm not sure it's worth the bother!).
When the form is closed the new item is lost and will not appear
when the form is opened again (I could write some code to switch the
form into design view and make the change, save it, and switch back
into form view... not forgetting to have remembered the user's
entries on the form up to that point... but again it isn't worth the
bother!).
If it is a multi-column list you must remember to fill each
column so that if several items are added each appears on a new
line.
In short, if you want the user to be able to add new items to the
list you're probably going to need a Row Source in the form of a
purpose-built table.
Summary
- When the user makes an entry in a combo box on an Access form,
Access knows whether or not that entry matches an item on the
combo box's list.
- To make use of this facility you must set the Limit To List
property of the combo box to Yes. This causes the form to
reject any non-matching entry.
- You can customise the form's response to an non-matching entry
by writing a VBA code procedure for the NotInList event of
the combo box.
- You can display a custom message and optionally offer to add
the new item to the list.
- The form can only accept the non-matching entry if the new
item is first added to the list.
- If you want to offer the facility to add items to the combo
box list the most suitable Row Source for the list is a
purpose-built table.
|
