Trap error in Combo Box

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 out the
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 create a
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

have you looked at the NotInList event property of the combobox control?

hth


"Ray C" wrote in message
...
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 out
the
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 create
a
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

On Dec 28, 5:52*pm, Ray C wrote:
*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 out the
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 create a
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

looking for models for soft core porn
great rates get in touch females only

This is great Ken, I don't know why i did not work that out for myself. Thank
you so much for your help.

Regards Ray C


"Ken Sheridan" wrote:

On Dec 28, 5:52 pm, Ray C wrote:
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 out the
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 create a
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