It is often good practice to provide your users with a list of values that they can assign to a record when entering data into a Microsoft Access Database form. With the use of pre filled lists, you can prevent the user from making incorrect data entry, and minimise the change of spelling mistakes or incorrect choice of values.
On occasions, where you have a set selection displayed within a form's combobox, you may wish to allow the user to add new values to the underlying table.
This case would call for you to make use of the combobox's NotInList event.
Firstly, there are certain properties that need to be addressed:
Now if we take the following form, which has all of the above properties set for the combo box, and contains a list of values already entered into the Book Categories table (tblBookCategories):
Form containing combo box with Limit To List property set to Yes
If we were to attempt to add a new Book Category to this list, with the properties set as such, we would expect it to give an error message. However, with the Not In List event, we can allow users to update the list if a new value is required.
The following shows what happens when a new value is entered into the combo box (cboBookCategory):
When the user enters a value in the combo box that is not in the list, they will be presented with the message box.
This is triggered by the following code, that is added to the combo box (cboBookCategory) Not In List event:
Private Sub cboBookCategory_NotInList(NewData As String, Response As Integer) Dim strSQL As String Dim i As Integer Dim Msg As String 'Exit this sub if the combo box is cleared If NewData = "" Then Exit Sub Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr Msg = Msg & "Do you want to add it?" i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Book Category...") If i = vbYes Then strSQL = "Insert Into tblBookCategories ([strBookCategory]) " & _ "values ('" & NewData & "');" CurrentDb.Execute strSQL, dbFailOnError Response = acDataErrAdded Else Response = acDataErrContinue End If End Sub
This will add the new value to the combo box and allow it to be assigned to the record.
There are many variations and approaches to applying this functionality so if this approach does not suit you can find further examples on the Microsoft Knowledge Base, searching for NotInList event.