Can anyone help please?
I use a Combo Box as a "Find Box" to list all available (non duplicated)
entries in a table. Great, but I also want the user to input the name that
they want to find and if the name is not in the Table, I want to trap outthe
error (The Item does not exist in the List) and tell the user that the name
does not exist in the Table giving them the option "Do you want to createa
new record in that name?"
I can do this if I run the required string through the Record Set but I can
not seem to trap out the error generated by the Combo Box not being able to
find what has been entered.
Any help appreciated
Ray C
As Tina says, use the control's NotInList event procedure. Here's an
example which adds a City name to a Cities table:
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
And here's one which does the same, but in this case opens a frmCities
form so that other data than the city name, e.g. the county or state
in which the city is in can also be added to the new record:
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 this second example the following code also goes in the frmCities
form's Open event procedure to set the DefaultValue property of the
City control on the form to the name of the city added in the combo
box:
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
