Allow User to Add Values in Combo Box
It is often good practice to provide users with a list of values that they can assign to a field when entering data into a Microsoft Access database form. With the use of pre-filled lists like a combo box, you can prevent the user from making incorrect data entry, and minimize the chance of spelling mistakes or incorrect choice of values. On some occasions, however, you may wish to allow the user to add new values to the underlying table. I do this for a field like City as part of a contact address where I would give the user the ability to define a new City that is not on the original list. This case would call for you to make use of the ComboBox’s NotInList event. To make it work, make sure that the following properties are set: Here’s the sample code for the NotInList event:
Private Sub cbxSYAddressCityID_NotInList( _
NewData As String, Response As Integer)
Dim strMsg As String
Dim strSQL As String
' Exit if the combo box was cleared ' Delete this line if the field is required If Len(NewData) = 0 Then Exit Sub
' Confirm that the user wants to add the new city strMsg = """" & NewData & """" & " is not in the list." _
& vbCr & vbCr & "Do you want to add it?" If MsgBox(strMsg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add the city, set the ' Response argument to suppress an error message ' and undo changes. Response = acDataErrContinue
' Display a customized message. cbxSYAddressCityID.SetFocus
Else ' If the user chose to add the new city, insert ' it the SYCity table strSQL = "INSERT INTO SYCity (SYCityName)" _ & " VALUES (" & """" & NewData & """" & ");"
' SetWarnings to False so the Dialog to confirm ' new record won't appear DoCmd.SetWarnings False DoCmd.RunSQL strSQL
' SetWarnings to True again DoCmd.SetWarnings True ' Set Response argument to indicate that new data ' is being added. Response = acDataErrAdded
End If
End Sub
Post a Comment