You can type a new term directly into a combo box, and then use its NotInList
event procedure to insert a new row into the referenced table. Firstly make
sure the combo box's LimitToList property is set to true (Yes in the
properties sheet). There are then two possible scenarios:
1. The term you type into the combo box is all that needs to be entered
into the referenced table.
2. There are other columns in the referenced table which need to have data
entered in addition to that which you type into the combo box.
The following examples are for adding a city into a cities table. Firstly
if the city name is the only data to be entered (other than an autonumber
CityID which will be entered automatically. For this the code in the combo
box's NotInList event procedure goes like this:
Private Sub cboCities_NotInList(NewData As String, Response As Integer)
Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String
Set ctrl = Me.ActiveControl
strMessage = "Add new city to list?"
strSQL = "INSERT INTO Cities(City) VALUES(""" & _
NewData & """)"
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If
Set cmd = Nothing
End Sub
For the second scenario, where you also need to enter other data, such as
which state the city is in, the code needs to be extended so that it opens a
form to add the state etc, and then returns the user to the combo box. The
code for the combo box's NotInList event procedure in this case goes like
this:
Private Sub cboCities_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
In the form bound to the Cities table, frmCities in the above example, you
need to put some code in its Open event procedure to set the default value of
the City control to the value you typed into the combo box, and which was
passed to the form by means of the OpenArgs mechanism. This code goes like
this:
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If
End Sub
By setting the DefaultValue property rather than the Value property (which
is the default property so is usually omitted) the user can back out of the
cities form without adding the new record if they wish.
Whichever of these scenarios is appropriate in your case, once the new row
has been inserted into the referenced table the user is returned to the combo
box with the new item in the list.
Ken Sheridan
Stafford, England
