Requery the combo box in the subform in the AfterUpdate event procedure of
the form in datasheet view. Controls on a subform are referenced by means of
the Form property of the subform control. The subform control is the control
in the parent form which houses the subform, so the code would be along these
lines:
Forms![YourParentForm]![YourSubformControl].Form![YourComboBox].Requery
However, if the new data comprises only one field in the referenced table
(possibly plus an autonumber primary key) you can automate it via the
NotInList event procedure of the combo box so that a user can type a new item
directly into the combo box. Here's some code for the NotInList event
procedure of a combo box which lists cities:
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
If you need to insert values into other fields in the referenced table you
can pass the value typed into the combo box to a form as its OpenArgs
property like so:
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
Then in the Open event procedure of the other form (frmCities in the above
example) put code like the following, which sets the DefaultValue property of
the City control to the value passed to the form:
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If
End Sub
Ken Sheridan
Stafford, England
