Data type mismatch -- Combo box bound to database

jamenijamjam · August 19th, 2006, 12:30 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
:)

BrianWren · August 21st, 2006, 02:50 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)

Code:

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):

Code:

    rs.Open "SELECT * " & _

Code:

            "FROM   tblMaterial " & _
            "WHERE  MaterialID = [u]'</u>" & Str(Combo1.Text) & "[u]'</u> ;", _
            cn, adOpenDynamic, adLockOptimistic

That compares the quoted literal string with a number, a type mismatch.

jorgefejr · September 1st, 2006, 08:36 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.