How To Create A Drop Down List In Access Table

A combo box control lets you pick items from a drop-down list. Typically, adding new items to the list requires updating the records in a separate table. Using the NotInList event, however, users can add new values to the combo box control directly, allowing new records to be created on the fly.

Consider a teacher using Access to keep track of questions and answers. Each question is assigned a category. Sometimes, however, the desired category isn't included in the list. For example, the category for the question shown in Figure 1 is "Organic Chemistry."

To see what's going on behind the scenes, go to the Design view of the form and display the Property Sheet for the appropriate combo box. Figure 2 shows the Event tab of the Property Sheet for the CategID control, which displays the Category field. The Category table contains the values in the drop-down, and CategID is its primary key. It's also an AutoNumber, which means that new values are automatically created when records are added.

Click on the builder button (…) for the "On Not in List" property. This takes you to the code for a procedure called "CategID_NotInList":

Private Sub CategID_NotInList( _

NewData As String,_

Response As Integer)

Call combo_NotInList( _

"Category", "Category",

NewData, Response)

End Sub

The Sub declaration, parameters, and End Sub are automatically added. By definition, the parameters for the NotInList event are the new data that was entered and the response back to the user. All you type is what you want it to do, which, in this case, is call a sub named "combo_NotInList." Two new parameters are passed—the table name and field, both of which happen to be called "Category"—along with the two built-in parameters. This is the custom procedure that's called:

Public Sub combo_NotInList( _

ByVal psTablename As String _

,ByVal psFieldname As String _

,ByVal NewData As String _

,ByRef Response As Integer)

'ignore errors

On Error Resume Next

'initialize Response to error

Response = acDataErrContinue

Dim sSQL As String _

,sMsg As String

'Ask if user wants to add a new record

sMsg = """" & NewData _

& """ is not in the current list. " _

& vbCrLf & vbCrLf _

& "Do you want to add it? " _

'if the user didn't click Yes, then exit

'so they can change the new value

If MsgBox(sMsg,vbYesNo, "Add New Data") <> vbYes Then

GoTo Proc_Exit

End If

'SQL statement to add record to psTablename

'set psFieldname = "NewData"

sSQL = "INSERT INTO [" & psTablename & "] " _

& "([" & psFieldname & "])" _

& " SELECT """ & NewData & """;

'write SQL to Immediate window in case there's a problem

Debug.Print sSQL 'comment or remove later

With CurrentDb

'run the SQL statement

.Execute sSQL

'if a record was added, set Response

If .RecordsAffected > 0 Then

'set response to data added

Response = acDataErrAdded

End If

End With

Proc_Exit:

End Sub

"Public Sub combo_NotInList" declares a public subroutine named "combo_NotInList." In the program, the parameters are called "psTablename," which is the table to which new records will be added; "psFieldname" for the fieldname with a new value of "NewData"; and "Response" to change and send back with a new value.

This code skips all errors and initializes the response to be an error in case the user doesn't want to add a new value. Variables are dimensioned, and a message is constructed. A message box is displayed with Yes and No buttons. If the user doesn't choose Yes, code goes to the statement following the Proc_Exit line label, which is the end of the sub.

If the user wants to add the new value, an SQL statement is constructed to insert a record into the psTablename table and set the psFieldname field to the NewData value (delimited with quote marks since it's text). "Debug.Print sSQL" writes the SQL statement to the debug (Immediate) window in case there's a problem with the statement.

The current database executes the SQL statement. If the records affected is greater than zero, then a record was added and the response is set to acDataErrAdded, which means all is good and no error message is displayed to the user.

Download this month's file: SF2010_DATABASE_NotInList

Watch a video on this topic.

SF SAYS

The NotInList event is a quick way to add values to whatever table provides the list for a combo box.

Crystal Long teaches and develops with Microsoft Access, Excel, and Office and specializes in remote training. She connects and helps as your project is built. Visit www.MsAccessGurus.com for information.