DBL CLICK Event: data type mismatch in criteria expression

[JohnLute]

I have a combo box double click event that's giving me fits:

Private Sub SupplierID_DblClick(Cancel As Integer)
If IsNull(Me!SupplierID) Then
' no supplier ID selected; show all ...
DoCmd.OpenForm "frmPKSuppliers"
Else
' Show selected supplier ID.
' First, open frmPKSuppliers to show the supplier ...
DoCmd.OpenForm "frmPKSuppliers", _
WhereCondition:="txtSupplierName=""" & _
Me!SupplierID.Column(1) & """"
' Now position the subform, sfrmSupplierIDs,
' to the selected ID ...
With Forms!frmPKSuppliers!sfrmSupplierIDs.Form

.Recordset.FindFirst _

"txtSupplierID=" & Me.SupplierID.Column(0)<
End With
End If
End Sub

I used "><" to mark what the debugger finds as incorrect. Here's the form's
SQL:

SELECT tblSupplierIDs.txtSupplierID, tblSupplierNames.txtSupplierName,
tblSupplierIDsAddresses.City, tblSupplierIDsAddresses.StateOrProvince
FROM tblSupplierNames INNER JOIN (tblSupplierIDs INNER JOIN
tblSupplierIDsAddresses ON tblSupplierIDs.txtSupplierID =
tblSupplierIDsAddresses.txtSupplierID) ON tblSupplierNames.txtSupplierName =
tblSupplierIDs.txtSupplierName;

I can't see what the data type mismatch is. Can anyone help, please?

Thanks!

 

[Ken Snell [MVP]]

You need to use the field name, not the control to which the field is bound,
in the search. Guessing that txtSupplierID is the textbox bound to
SupplierID, you should use SupplierID. Also, when you read the value from
other columns (instead of the bound column) of a combo box or listbox, often
that value is a text string (regardless of what data type it is in the
query). Try casting the value with the proper "type" function. Assuming that
it's a Long Integer type:

.Recordset.FindFirst _
"SupplierID=" & CLng(Me.SupplierID.Column(0))

 

[JohnLute]

Thanks, Ken. The field name is txtSupplierID and the data type is Text.

Is it possible that the data type and the values entereed in which sometimes
include hyphens are throwing this off?

Thanks!

 

[Dirk Goldgar]

Thanks, Ken. The field name is txtSupplierID and the data type is
Text.

Is it possible that the data type and the values entereed in which
sometimes include hyphens are throwing this off?

If txtSupplierID is a text field, then you need to have quotes arounf
the SupplierID value you're searching for:

.Recordset.FindFirst "txtSupplierID=" & _
Chr(34) & Me.SupplierID.Column(0) & Chr(34)

 

[JohnLute]

That did the trick!

THANKS!

My code skills/knowledge is very limited. What does the Chr(34) doing?

 

[Douglas J Steele]

Chr(34) is a double quote. Some of us find it easier to use

.Recordset.FindFirst "txtSupplierID=" & _
Chr(34) & Me.SupplierID.Column(0) & Chr(34)

than

.Recordset.FindFirst "txtSupplierID=""" & _
Me.SupplierID.Column(0) & """"

or

.Recordset.FindFirst "txtSupplierID='" & _
Me.SupplierID.Column(0) & "'"

 

[JohnLute]

Thanks, Douglas!

That certainly does make things easier. Too bad I didn't know about that
long ago as it could've saved me a lot of time in other areas!

 

[Dirk Goldgar]

That did the trick!

THANKS!

My code skills/knowledge is very limited. What does the Chr(34) doing?

I see Doug Steele has already explained. I'm just writing to say
your're welcome, and to let you know that I haven't forgotten about your
outstanding question in the other thread; it's just that I've been
busy, and haven't been able to spare the brainpower to figure it out
yet.

 

[JohnLute]

Thanks, Dirk! I'm more than happy to wait. Yourself including so many here
are so helpful! I'm constantly amazed at the wealth of knowledge that flows
through this forum. I'm trying to tap into it via the Vulcan mind-meld but
I've yet to get the right grip.

Glad to hear you're busy. Idle minds are the playgrounds for the nefarious!