In the AfterInsert event procedure of the second form requery the combo box
on the first form and set its value to the values just entered in the second
form, e.g.
Dim ctrl As Control
On Error Resume Next
Set ctrl = Forms("YourFirstForm")("YourComboBox")
If Err.Number = 0 Then
ctrl.Requery
ctrl = Me.YourField
End If
The error handling caters for the second form being used when the first is
not open.
You can however use the NotInList event procedure of the combo box to insert
the new row into the table by typing the value directly into the combo box.
If you are only entering a value into one column in the table, the one whose
value is shown in the combo box, then you can do it without having to open a
second form. Here's an example of code which does this when adding a new
city name to a combo box:
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
And here's the same, but this time opening a second form, frmCities so that
other data than the city name can be added in the new row in the Cities
table, e.g. county or state. The value typed into the combo box (the new
city name) is passed to the frmCities form as its OpenArgs property. Note
that the frmCities form is opened in dialogue mode; this causes the code
execution in the combo box's NotInList event procedure to pause until
frmCities is closed:
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
With the second of these the following code goes in the frmCities form's
Open event procedure to set the DefaultValue property of its City text box
control to the
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If
End Sub
Note that when setting the DefaultValue property of a control it should be
wrapped in literal quotes as above regardless of its data type as this
property is always a string expression.
BTW rather than use a table as the RowSource property its usually better to
use a sorted query based on the table as this enables you to sort the combo
box's list, e.g. in the above case the RowSource would be:
SELECT CityID, City
FROM Cities
ORDER BY City;
The query does not have to be saved, the RowSource property can be, and
usually is, the SQL statement.
I should point out that this example is simplified a little for the sake of
clarity. As in real life city names can be duplicated you might want to add
a city name which already is listed as a new row in Cities with a different
CityID. What I'd do in a real life scenario would be to also include the
region (and the country if its an international database) in which the city
is located as another column in the combo box's list, and in addition to
using the above code also allow the user to open the frmCities form directly
by double-clicking the combo box to add a new city with the same name as one
already in the Cities table, e.g. Paris, France as well as Paris, Texas.
Ken Sheridan
Stafford, England
