|   Home    |    Excel    |    Access   |   Word   |   Outlook   |   FrontPage   |   VBA   |   Downloads   |   Index   |
The NotInList Event

Coding Note

Sometimes lines of code can be very long! Where I have had to break a line of code to fit it on the page I have used the code continues on next line symbol to indicate that the line below is a continuation and that the code should be written as a single line

<Blah blah blah code continues on next line
blah>

should be written:

<Blah blah blah blah>

 

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...

Using a table as the Row Source for a combo box.   A combo box list built from a table.

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...

Specifying a stored query as the Row Source of a combo box.

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...

Using an SQL statement as the Row Source of a combo box.

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...

Combo box properties for a multi-column value list.

These property settings result in a combo box list looking like this...

A simple two-column combo box list.

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:

The standard "Not In List" warning 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:

  1. In form design view open the properties window of the combo box (by right-clicking on it and choosing Properties).
  2. 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.
  3. Click the build button ( The "Build" button. ) to open the form's code module in the Visual Basic Editor with an empty code procedure...

Creating an Event 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...

A custom message refusing the user's entry.

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...

Including the rejected entry in the custom 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...

The user is asked if they want to add a new entry to the list.

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...

A message box confirms that the new item has been added.

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...

The new item has been added to the list.

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...

A message reminds the user to choose an item from the list.

Then acDataErrContinue instructs Access to return to the combo box, without accepting the entry, and open the list...

Access opens the combo box list for the user to make their choice.

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.

 

^ top
 

 

 

 

 

Hit Counter