Skip to content

Add-to Combo Boxes, Part 1

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

About this author