You can do it without the need for the button by typing the new client name
into the combo box. This will trigger the NotInList event of the control and
you can put code in the event procedure to open the form for the new client
record to be added, passing the name you typed into the combo box to the form
so it is already in place on the form when it opens, leaving you to add other
data such as the client's address etc. Here's an example which adds a new
city and which you should be able to adapt easily:
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
The Open event procedure of the frmCities form contains the following code:
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If
End Sub
If you prefer to stick with your button then you should ensure that it opens
the form in dialogue mode, which means the execution of the calling code will
pause until the form is closed or hidden, then requery the combo box, e.g.
DoCmd.OpenForm "frmClients", _
DataMode:=acFormAdd, _
WindowMode:=acDialog
' ensure frmClients closed
DoCmd.Close acForm, "frmClients"
' requery combo box
Me.cboClients.Requery
Ken Sheridan
Stafford, England
