Drop Down Box add Options to the list through a form

As the other respondents have pointed out, this can be done quite simply by means of the control's NotInList event procedure.  However, a Cities table will in most databases include at least one non-key column other than the city name, e.g. a state or other regional administrative unit in which the city is located.  Simply inserting a row transparently via the NotInList event procedure will usually be insufficient, therefore.  The NotInList event procedure can still be used, but, rather than inserting a row transparently into the Cities table it will open a form in dialogue mode, passing the new city name to it via the OpenArgs mechanism.  The new name is then assigned as the default value for a new record when the cities form opens in dialogue mode.

You'll find an example as NotInList.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the from Contacts form's city combo box's NotInList event procedure is as follows:

Private Sub City_NotInList(NewData As String, Response As Integer)

    Dim ctrl As Control

    Dim strMessage As String

    Set ctrl = Me.ActiveControl

    strMessage = "Add " & NewData & " to list?"

    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

        DoCmd.OpenForm "frmCities", _

            DataMode:=acFormAdd, _

            Windowmode:=acDialog, _

            OpenArgs:=NewData

        ' ensure frmCities closed

        DoCmd.Close acForm, "frmCities"

        ' ensure city has been added

        If Not IsNull(DLookup("CityID", "Cities", "City = """ & _

            NewData & """")) Then

        Response = acDataErrAdded

        Else

            strMessage = NewData & " was not added to Cities table."

            MsgBox strMessage, vbInformation, "Warning"

            Response = acDataErrContinue

            ctrl.Undo

        End If

    Else

        Response = acDataErrContinue

        ctrl.Undo

    End If

End Sub

To assign the value passed to the frmCities form as the DefaultValue property of the City control , the frmCities form's Open evemt procedure is as follows:

Private Sub Form_Open(Cancel As Integer)

    If Not IsNull(Me.OpenArgs) Then

        Me.City.DefaultValue = """" & Me.OpenArgs & """"

    End If

End Sub

However, it must be borne in mind that city and town names can legitimately be duplicated.  There are 41 Springfields in the USA for instance, of which 5 are in Wisconsin.  Consequently, if a city of a particular name has already been included in the Cities table, it will appear in the combo box's list,  so the NotInList event procedure cannot be used to insert another city of the same name.  It would be possible to concatenate the city and state names in the list, but even then this would not cater for where there are multiple cities of the same name in a single state or other regional unit.  It would be necessary to include another distinguishing value in the concatenated expression, e.g. the county.  Parsing the string would be possible, and my demo includes an example of this in the case of personal names in it's opening form.  A better approach in my view, though, is to not use the NotInList event procedure at all, but to open the form in dialogue mode via a separate button on the original form, and pass the value inserted into the new row in the dialogue form back to the combo box in the original form, requerying the combo box to include the new item in the list.  Such a button is included in my demo's opening form to add a new contact name, personal names also being legitimately duplicable of course.  The opening form also shows the use of a concatenated value as the visible column of the combo box, and the use of an additional column in the control's list, in this case a concatenated address, to distinguish between contacts of the same name when selecting an item from the list.  The same could be done with other legitimately duplicable values such as city names of course.

You can also simply use the Not In List event procedure for your combo box.

Private Sub cbx1_NotInList(NewData As String, Response As Integer)

    On Error GoTo Err_Process

    Dim ctl As Control

    'Return Control object that points to combo box.

    Set ctl = Me.ActiveControl

    'Prompt user to verify they wish to add new value.

    If MsgBox("Value is not in list. Add it?", vbExclamation + vbOKCancel, "Not In List") = vbOK Then

        If (DBAddNewValue("tblCity", "CityName", NewData) = True) Then

            'Set Response argument to indicate that data is added.

            Response = acDataErrAdded

        End If

    Else

        MsgBox "Please try again.", vbInformation, "Not In List"

        'If user chooses Cancel, suppress error message and undo changes.

        Response = acDataErrContinue

        ctl.Undo

    End If

Exit_Process:

    Set ctl = Nothing

    Exit Sub

Err_Process:

    MsgBox Err.Number & " " & Err.Description, vbExclamation, "Error"

    Resume Exit_Process

    Resume 0

End Sub

Function DBAddNewValue(TableName As String, FieldName As String, NewValue As Variant) As Boolean

    On Error GoTo Err_Process

    Dim blnReturn As Boolean

    Dim dbs1 As Database

    Dim rst1 As Recordset

    blnReturn = False

    Set dbs1 = CurrentDb

    Set rst1 = dbs1.OpenRecordset(TableName, dbOpenDynaset)

    With rst1

        .AddNew

        .Fields(FieldName) = NewValue

        .Update

        .Close

    End With

    blnReturn = True

Exit_Process:

    Set rst1 = Nothing

    Set dbs1 = Nothing

    DBAddNewValue = blnReturn

Err_Process:

    MsgBox Err.Number & " " & Err.Description, vbExclamation, "Error"

    Resume Exit_Process

    Resume 0

End Function

Hi. There are several ways to accomplish that. Here's one:

1. Create a data entry form for tbl_City
2. Make sure Data Entry property is set to Yes
3. Go to design view of your form based on tbl_Student and select the city combobox
4. Set the Limit To List property to Yes
5. Set the List Items Edit Form to the name of the form you created in step 1 above
6. Save the form and try it out by entering a non-existing city name

Hope that helps...

It is simple and Access provides for it. However, you can't do it on the table. It has to be done on a form. It is NOT recommended that you use lookup fields on the table level. Look ups should be done on forms using list controls.

Access provides for it by adding a NotInList event for comboboxes.

You don't need all the code supplied by runningman. The following placed in the NotInList event should suffice:

     

     On Error GoTo Err_Process

   

    Dim ctl As Control

   

    'Return Control object that points to combo box.

    Set ctl = Me.ActiveControl

   

    'Prompt user to verify they wish to add new value.

    If MsgBox(Newdata & " is not in list. Add it?", vbExclamation + vbOKCancel, "Not In List") = vbOK Then

        CurrentDB.Execute "INSERT INTO tbl_City (City) VALUES('" & NewData & "');", dbFailOnError  

        'Set Response argument to indicate that data is added.

         Response = acDataErrAdded

     Else

        MsgBox "Please try again.", vbInformation, "Not In List"

        'If user chooses Cancel, suppress error message and undo changes.

        Response = acDataErrContinue

        ctl.Undo

    End If

   

Exit_Process:

    Set ctl = Nothing

    Exit Sub

   

Err_Process:

    MsgBox Err.Number & " " & Err.Description, vbExclamation, "Error"

    Resume Exit_Process

    Resume 0

You can research the NotInList event for more details. 

Scott is absolutely correct, you don't need a separate function to add data outside of the basic NotInList procedure.  I gave two procedures so that you could see how the NotInList event is coded, and a separate one for adding data so that you could use the code in other places without having to be concerned about formatting input values in SQL such as text and dates, etc..  This is an area that tends to trip up novice developers, and using the DAO.Recordset doesn't require it.

Using SQL is the more straight forward and robust method for adding data to your database.  Using the DAO method shown in my earlier example is a simpler method and requires only that you know the table and field name to insert a record.

The procedure below merges the two procedures in my previous example for a reusable function that can be utilized any time you need to be able to add to a combo box list.

Private Sub cbxCity_NotInList(NewData As String, Response As Integer)

    Response = ItemNotInList("tblCity", "CityName", NewData)

End Sub

Function ItemNotInList(TableName As String, FieldName As String, NewValue As String) As Integer

    On Error GoTo Err_Process

    Dim intReturn As Integer

    Dim intResponse As Integer

    Dim dbs1 As Database

    Dim rst1 As Recordset

    Dim strMsg As String

    'Default return value

    intReturn = acDataErrContinue

    'Prompt user to verify they wish to add new value.

    strMsg = "Item is not in the list: " & NewValue & vbCrLf & vbCrLf

    strMsg = strMsg & "Do you wish to add it?"

    intResponse = MsgBox(strMsg, vbExclamation + vbOKCancel, "Not In List")

    If (intResponse = vbOK) Then

        Set dbs1 = CurrentDb

        Set rst1 = dbs1.OpenRecordset(TableName, dbOpenDynaset)

        With rst1

            .AddNew

            .Fields(FieldName) = NewValue

            .Update

            .Close

        End With

        'Return value if successful

        intReturn = acDataErrAdded

    End If

Exit_Process:

    Set rst1 = Nothing

    Set dbs1 = Nothing

    If (intReturn = acDataErrContinue) Then

        MsgBox "Please try again.", vbInformation, "Data Not Added"

    End If

    ItemNotInList = intReturn

    Exit Function

Err_Process:

    MsgBox Err.Number & " " & Err.Description, vbExclamation, "Error"

    Resume Exit_Process

    Resume 0

End Function

It is simple and Access provides for it. However, you can't do it on the table. It has to be done on a form. It is NOT recommended that you use lookup fields on the table level. Look ups should be done on forms using list controls.

Access provides for it by adding a NotInList event for comboboxes.

You don't need all the code supplied by runningman. The following placed in the NotInList event should suffice:

     On Error GoTo Err_Process

    Dim ctl As Control

    'Return Control object that points to combo box.

    Set ctl = Me.ActiveControl

    'Prompt user to verify they wish to add new value.

    If MsgBox(Newdata & " is not in list. Add it?", vbExclamation + vbOKCancel, "Not In List") = vbOK Then

        CurrentDB.Execute "INSERT INTO tbl_City (City) VALUES('" & NewData & "');", dbFailOnError  

        'Set Response argument to indicate that data is added.

         Response = acDataErrAdded

     Else

        MsgBox "Please try again.", vbInformation, "Not In List"

        'If user chooses Cancel, suppress error message and undo changes.

        Response = acDataErrContinue

        ctl.Undo

    End If

Exit_Process:

    Set ctl = Nothing

    Exit Sub

Err_Process:

    MsgBox Err.Number & " " & Err.Description, vbExclamation, "Error"

    Resume Exit_Process

    Resume 0

You can research the NotInList event for more details.