Hi,
I have a combo box which lists values from a Table. I
would like to be able to give the option to the user to add
values other than the ones listed.
How I do this in VBA?
Do you mean add the new values to the table, or simply allow the new
data in the field without adding it to the combo table?
To simply allow the new data without adding it, set the Combo
LimitToList property to No.
To actually add the new data to the table......
There are several different methods. Also, the method wiould depend
upon whether you were adding just one field's data or you needed to
add more than one field at the time, such as a company name, address,
phone#, etc.
The following will add one field, in this case just a new city name,
to a table. Change the field and table names to whatever yours are.
Try it this way in the Combo NotInList event.
(Watch for word wrap on the longer lines.)
On Error GoTo Err_City_NotInList
Dim IntResponse As Integer
' Prompt user to verify they wish to add new value.
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
' Capitalize just the first letter of the city
NewData = StrConv(NewData, vbProperCase)
' Add the string in NewData argument to the City table
CurrentDb.Execute " INSERT INTO tblCities(txtCity) SELECT " &
chr(34) & NewData & chr(34) & ";",dbFailOnError
Me!City = NewData
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
