I have a combobox based on a "Product" table. We typically use those values,
but there are rare instances that we need to type in a new value to use on a
one-time basis, so I don't necessarily want it to become a part of the combo
box. Can this be done? Also, does the record need to stay in the related
table because of the relationship?
What event procedure is required in the OnNotInList property if I want to
allow new values to be added?
Any help is greatly appreciated!
To allow the user to enter a value that is not in the list, without
adding it to the list, set the Combo Box 'Limit to List' property to
No.
Otherwise you would set the 'Limit to List' property to Yes.
How you would add a new item to the list would depend upon what you
are wishing to add.
If it is a new company, for example, and you want to add the company
name, address, phone#, etc., to the Company Table, you would use the
NotInList event to open the Company Form in acDialog, enter the data,
close the form, requery the combo box, and pick up entering data where
you left off.
If it is simply one field you want the add to the list, such as a new
city in a list of cities, you can use the following code to simply add
the entered city to the list.
Try it this way in the Combo NotInList event.
Change the table and field names as needed.
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
' Add string in NewData argument to 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
