For a combo box whose RowSource draws upon the Customers table use the combo
box's NotInList event procedure to insert a row into the Customers table when
a new customer is typed into the combo box. As there will presumably be
other customer details to insert as well as the name typed into the control
you can include code to open a form bound to the Customers table in dialogue
mode to do this. Opening the form in dialogue mode is important as this
causes the execution of the calling code to pause until the Customers form is
closed. Here's an example of mine for adding a new City via a combo box
bound to a foreign key CityID column in form:
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
You'll see that the above passes the value typed into the combo box (the
event procedure's NewData argument) to the frmCities form as the OpenArgs
argument of the OpenForm method. In the frmCities form's Open event
procedure this is then assigned to the DefaultValue property of the City text
box control on 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
