none
Data type mismatch error in vba code, please help.

    Question

  • I am still fairly new to vba and I am getting an error in my code.

     

    On my form I have a button which when clicked must display a value from a particular field onto a textbox on the form but only after the user selects particualr values from the combo boxes on the form. However with the current code I am getting the error: -2147217913 Data type mismatch in criteria expression. Below is the code:

     

    Private Sub Command27_Click()
    On Error GoTo ConnectionError
      
      Dim localConnection As ADODB.Connection
      Dim availability As New ADODB.Recordset
      
      Set localConnection = CurrentProject.Connection
      localConnection.CursorLocation = adUseClient
      
      Dim sql As String
     
      Dim myProduct As Integer
      Dim myLocation As String

     

      myProduct = Me.Combo29.ItemData(Me.Combo29.ListIndex)
      myLocation = Me.Combo32.ItemData(Me.Combo32.ListIndex)

     

      sql = "select NoAvailable from ProductInLocation where ProductID='" & CStr(myProduct) & "' & Location= '" & myLocation & "'"


      availability.Sort = "NoAvailable"
      availability.Open sql, localConnection, adOpenStatic
      
      availability.MoveFirst
      
      Dim myAvail As Integer
      
      For counter = 1 To availablilty.RecordCount
        myAvail = myAvail & availability.Fields.Item("NoAvailable")
        availability.MoveNext
      Next
     
      Me.Text14 = myAvail

      Exit Sub
     
    ConnectionError:
      MsgBox Err.Number & " , " & Err.Description
    End Sub

     

    A few things: the product field is a number data type, the location is a text data type and the NoAvailable is a number data type in the database tables.

     

    The other thing i should point out is that one of the combo boxes on the form (product) has several fields, so when the wizard asked me which field i want to uniquely identify the row, i chose the productID, and so this is the only field that appears after the user makes their selection from the combo box. thats why I am trying to read productID into th myProduct variable. I hope this is correct and not the thing causing the problem.

     

    Can someone please help as I require this problem to be rectified urgently. Thanks.

     

    Monday, March 24, 2008 3:11 PM

All replies

  • Hi

     

    Try changing

     

    sql = "select NoAvailable from ProductInLocation where ProductID='" & CStr(myProduct) & "' & Location= '" & myLocation & "'"

     

    to

     

    sql = "select NoAvailable from ProductInLocation where ProductID='" & CStr(myProduct) & "' AND Location= '" & myLocation & "'"

     

     

    Monday, March 24, 2008 3:31 PM
  • the product field is a number data type

    If the productID field is a number, don't convert myProduct to a string, and don't enclose it in single quotes in the sql string (remove the single quotes and other characters I've highlighted in red)

    sql = "select NoAvailable from ProductInLocation where ProductID='" & CStr(myProduct) & "' AND Location= '" & myLocation & "'"
    Tuesday, March 25, 2008 7:37 AM
  • thank u both for the help. I have made that correction, so the code now looks like this:

    sql = "select NoAvailable from ProductInLocation where ProductID=" & myProduct & " AND Location= '" & myLocation & "'"

     

    but now I am getting the following error: 424, object required.

     

    Can someone please help me get rid of this error?

     

    Thanks.

    Tuesday, March 25, 2008 4:02 PM
  • I think you have a typo in your variable name on this line:

      For counter = 1 To availablilty.RecordCount
    You've mis-spelt availability.

     

    If you still get an error after correcting that, in your next post it will help if you could tell us which line is causing the error. Follow the instructions below before you post again.

     

    Can you make some changes to the code temporarily (change them back afterward) to help us to troubleshoot this?

     

    You have a line that says:

    On Error GoTo ConnectionError

    Can you comment that out (put an apostrophe at the beginning of the line), like this:

    ' On Error GoTo ConnectionError

     

    Now try running the code again. You will get a different error message (probably a "run-time error 1004"), but the dialog box should give you the option to debug (if it doesn't, tell us and we'll try something else). Choose that option (it should take you to the Visual Basic Editor), and tell us which line of code it has stopped at (which line is highlighted, probably in yellow). Then hit the [F8] key - it will give you another error - tell us exactly what that error says. This time you can "End" the error diallog box.

     

    Now change back the line you commented out (remove the apostrophe).

     

    So your next post needs to tell us:

    1. Which line it stopped at.

    2. Exactly what the error said.

    Also, if you changed any part of the code except the "sql =" and the commented out line, please re-post the whole code.

    Wednesday, March 26, 2008 1:12 AM
  • I corrected the spelling and it is working now... thank you very much for all your help. Smile

     

    Wednesday, March 26, 2008 3:15 PM