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( _
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)
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
'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
'run the SQL statement
'if a record was added, set Response
If .RecordsAffected > 0 Then
'set response to data added
Response = acDataErrAdded
“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.