Alex:
How are you adding the new record? If its via the NotInList event procedure
of the combo box you'll need to set the return value of its Response argument
to the acDataErrAdded constant. Here are a couple of examples for adding a
city to a Cities table via the NotInList event procedure, the first for where
the table contains just the city name, the second for where it contains other
data, e.g. the county or state its in, which will also need to be added, in
which case the code also opens a form in dialog mode to do this:
1. Add the city name in combo box only:
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 " & NewData & " 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
End Sub
2. Add city name in combo box then open 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
In the latter scenario the frmCities form's Open event procedure also
contains this code:
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
