How to create a simple Add-to Combo Box.
Access archon #161
Introduction
Back when my Access Archon column appeared in Office Watch (around 1998), and there were no code samples, in Access Archon #7 and #8 I described how to create add-to combo boxes that let users enter new list items on-the-fly. In this article I update my code to make it more generic (and thus easier to reuse in your databases), and provide a sample database.
Creating a Simple Add-to Combo Box
If you have a combo box whose row source is a lookup table with a single field, or one data field and an AutoNumber ID field, you can implement an add-to combo box with just code on the combo box’s NotInList event. The words highlighted in yellow are the only text you have to change when using this code in your databases.
VBA Code
Private Sub cboCategoryID_NotInList(strNewData As String, intResponse As Integer)
‘Set Limit to List to Yes
On Error GoTo ErrorHandler
Dim intResult As Integer
Dim strTitle As String
Dim intMsgDialog As Integer
Dim strMsg1 As String
Dim strMsg2 As String
Dim strMsg As String
Dim cbo As Access.ComboBox
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strTable As String
Dim strEntry As String
Dim strFieldName As String
‘The name of the table that is the combo box’s row source
strTable = “tblCategoriesSimple“
‘The type of item to add to the table
strEntry = “Category“
‘The field in the lookup table in which the new entry is stored
strFieldName = “CategoryName“
‘The add-to combo box
Set cbo = Me![cboCategoryID]
‘Display a message box asking whether the user wants to add a new entry.
strTitle = strEntry & ” not in list”
intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
strMsg1 = “Do you want to add “
strMsg2 = ” as a new ” & strEntry & ” entry?”
strMsg = strMsg1 + strNewData + strMsg2
intResult = MsgBox(strMsg, intMsgDialog, strTitle)
If intResult = vbNo Then
‘Cancel adding the new entry to the lookup table.
intResponse = acDataErrContinue
cbo.Undo
GoTo ErrorHandlerExit
ElseIf intResult = vbYes Then
‘Add a new record to the lookup table.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTable)
rst.AddNew
rst(strFieldName) = strNewData
rst.Update
rst.Close
‘Continue without displaying default error message.
intResponse = acDataErrAdded
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit
End Sub
To test this code, open frmProductsSimple in the sample database (Add-to Combo Boxes (AA 161).mdb). When you type in a new category name, you will get the message box shown in Figure A.
Figure A. A question about adding a new category.
If you click Yes, the new category is added to the row source table, and the combo box’s list is requeried. The new category is now available on all records, as shown in Figure B.
Figure B. The newly created category in the drop-down list.
Supporting Files
The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch161.zip, which is the last entry in the table of Access Archon columns for Access Watch.
Document Name |
Document Type |
Place in |
Add-to Combo Boxes (AA 161).mdb |
Access 2000 database; can also be used in higher versions of Access. |
Wherever you want |