Combo Box type of need, auto add to the existing list

  • Thread starter Thread starter Wayne G. Dengel
  • Start date Start date
W

Wayne G. Dengel

Familiar with the use of Combo Boxes, can I, how do I create a Combo Box (or
other) where the form user may automatically add to the existing list.

For example, I have a list of cities. At the user/form level, the user may
want to add a new city name. How is this done (if it can be)?

Regards,
Wayne
 
I assume you know how to get the existing Cities into the
combo box rowsource, and just need help in adding cities
that are not in the list.

Set the LimitToList property to Yes.

Here is some code to place in the [City] NotInList event.

On Error GoTo Err_City_NotInList
Dim strSQL As String
Dim IntResponse As Integer
' Prompt user to verify they wish to add new value.
Beep
IntResponse = MsgBox("You entered a City which is not in the list." &
vbNewLine _
& "Do you wish to add this City? Y/N ", vbQuestion + vbYesNo +
vbDefaultButton2, _
"City Not Listed")
If IntResponse = vbYes Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to City table
' Next code line is optional. Omit it if not wanted.
' Set city name to Proper Case.
NewData = StrConv(NewData, vbProperCase)
strSQL = " INSERT INTO tblCities(txtCity) SELECT '" & NewData & "'"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
Me!City = NewData
DoCmd.SetWarnings True
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
Me!City = Null
End If

Exit_City_NotInList:
Exit Sub

Err_City_NotInList:
If Err = 2113 Then
Err = 0
Resume Next
Else
MsgBox Str(Err) & " " & Err.Description
Resume Exit_City_NotInList
End If
=====
Change the table, tblCities, and the field, txtCities, to
reflect the actual table and field names you are using.

The Message box, as written, defaults to No.
Remove '+ vbDefaultButton2' to default to Yes.
 
Back
Top