|
Subject:
|
Data type mismatch -- Combo box bound to database
|
|
Posted By:
|
jamenijamjam
|
Post Date:
|
8/19/2006 12:30:27 PM
|
Hi,
I have a combo box that I've bound to a field in my database. The field is an autonumber. I want to show the corresponding record associated with the number when it is selected in the combo box. I want The corresponding records to appear in textboxes automatically...
I get a Data type mismatch:
Private Sub Combo1_GotFocus()
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & ";Persist Security Info=Fals"
cn.Open
rs.Open "SELECT * FROM tblMaterial WHERE MaterialID = '" & Str(Combo1.Text) & "' ;", cn, adOpenDynamic, adLockOptimistic
With orders
.qty1 = rs.Fields(1)
.desc1 = rs.Fields(2)
.price1 = rs.Fields(3)
End With
rs.Close
I dont know if the str(combo1.txt) in my query is allowed at all. But the query highlights as the error when I try to select an item in my combo box. Also, I'm unsure wether I should use combo1_Change or the one above which is combo1_Getfocus functions for me to get the effect that I want.
my combo1 is bound to the column MaterialId in the table tblMaterial. It is an autonumber in my access database. I'm just a newbie at this and I really dont know if this is the right approach. what am I doing wrong here?
Thanks in advance
|
|
Reply By:
|
BrianWren
|
Reply Date:
|
8/21/2006 2:50:47 PM
|
If you use [cod e] (but without the space I included) and its [/cod e] counterpart, your code will be easier to read... 9Makes it fixed pitch)Private Sub Combo1_GotFocus()
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";" & _
"Persist Security Info=Fals"
cn.Open
rs.Open "SELECT * " & _
"FROM tblMaterial " & _
"WHERE MaterialID = '" & Str(Combo1.Text) & "' ;", _
cn, adOpenDynamic, adLockOptimistic
With orders
.qty1 = rs.Fields(1)
.desc1 = rs.Fields(2)
.price1 = rs.Fields(3)
End With
rs.Close I think the problem is in your SQL. IDs (like MaterialID) are usually numeric. You encapsulated the results of Str(Combo1.Text) in quotes (shown just below in bold, underlined red): rs.Open "SELECT * " & _
"FROM tblMaterial " & _
"WHERE MaterialID = '" & Str(Combo1.Text) & "' ;", _
cn, adOpenDynamic, adLockOptimistic That compares the quoted literal string with a number, a type mismatch.
|
|
Reply By:
|
jorgefejr
|
Reply Date:
|
9/1/2006 8:36:51 PM
|
change this code:
rs.Open "SELECT * " & _
"FROM tblMaterial " & _
"WHERE MaterialID = '" & Str(Combo1.Text) & "' ;", _
cn, adOpenDynamic, adLockOptimistic
to:
rs.Open "SELECT * " & _
"FROM tblMaterial " & _
"WHERE MaterialID = " & clng(Combo1.Text), _
cn, adOpenDynamic, adLockOptimistic
Note: An auto number is a long integer if you are using Acccess; a BigInt if you are using SQL Server.
|
