VBA type mismatch error

Question

I have no clue what this is. Apparently it has to be some syntax error of some sort but I can not figure out what it is for the life of me! All I am doing is save data into a table in SQL Server 2008 and I am using vba in microsoft dynamics great plains 2010. The specific line it is throwing an error is

 rs("FREIGHT_RATE") = frmItemProfile.txtFreightRate

but it seems to change lines whenever I make an adjustment, so there is probably something wrong in this method somewhere. The error message only says "type mismatch" Any Help would be amazing.

Public Sub SaveRecord(strItemNumber As String)
Dim qry As String
Set rs = New ADODB.Recordset

'query table name
    qry = "SELECT * FROM dbo.PCI_ITEM_PROFILE where ITEMNMBR = '" & strItemNumber & "'"

'open recordset
    rs.Open qry, strDSNPCI, adOpenStatic, adLockPessimistic

        If rs.EOF <> True Then
            'Time to Update Record
                rs("RCD_KEY") = frmItemProfile.txtRCDKey
                If frmItemProfile.txtCopyRightDate = "" Then
                    rs("COPYRIGHT_DATE") = #1/1/1900#
                Else
                    rs("COPYRIGHT_DATE") = frmItemProfile.txtCopyRightDate
                End If
                rs("FIRST_CATALOG") = frmItemProfile.txtFirstCatalog
                rs("CATEGORY") = frmItemProfile.txtCategory
                rs("SERIES_CD") = frmItemProfile.txtSeriesCD
                rs("PARENT_CD") = frmItemProfile.txtParentCD
                rs("TYPE") = frmItemProfile.txtType
                rs("COMMODITY_CD") = frmItemProfile.txtCommodityCD
                rs("BARCODE_1") = frmItemProfile.txtBarCodeOne
                rs("BARCODE_2") = frmItemProfile.txtBarCodeTwo
                rs("BARCODE_3") = frmItemProfile.txtBarCodeThree
                rs("BARCODE_4") = frmItemProfile.txtBarCodeFour
                rs("CLASS_GROUP") = frmItemProfile.cmbClassGroup
                rs("FREIGHT_RATE") = frmItemProfile.txtFreightRate
                rs("ITEM_LENGTH") = frmItemProfile.txtItemLength
                rs("ITEM_WIDTH") = frmItemProfile.txtItemWidth
                rs("ITEM_HEIGHT") = frmItemProfile.txtItemHeight
                rs("USER2ENT") = frmItemProfile.txtUserEnt
                rs("CREATE_DATE") = frmItemProfile.txtCreateDate
                rs("MODIFDT") = frmItemProfile.txtModifyDate
                rs("IN_KIT") = frmItemProfile.txtInKit
                rs("IN_BOM") = frmItemProfile.txtInBom
                rs("REP_COMM_PCT") = frmItemProfile.txtRepPct
                rs("REP_COMM_EXCEPT") = frmItemProfile.txtRepCommExcept
                rs("ROYALTY_ITEM") = frmItemProfile.txtRoyaltyItem
                rs("PPC_PAGES") = frmItemProfile.txtPPCPages
                rs("PPC_PAPER") = frmItemProfile.txtPPCPaper
                rs("PPC_TONERCURVE") = frmItemProfile.txtPPCTonerCurve
                rs("PPC_COIL") = frmItemProfile.txtPPCCoil
                rs("PPC_IMPRESSIONS") = frmItemProfile.txtPPCImpressions
                rs("DROP_SHIP_ITEM") = frmItemProfile.txtDropShipItem
                rs("OP_CD") = frmItemProfile.txtOPCD
                If frmItemProfile.txtOPDate = "" Then
                    rs("OP_DATE") = #1/1/1900#
                Else
                    rs("OP_DATE") = frmItemProfile.txtOPDate
                End If
                rs("NOTES") = frmItemProfile.txtNotes
                rs.Update
        End If
    rs.Close
    Set rs = Nothing
 End Sub

This message indicates that the value of frmItemProfile.txtFreightRate is not allowable for the MS-SQL FREIGHT_RATE type, which is ??? float ???. Usually debugging the code and looking at the value is the best way to see what is going on. — ron tornambe, Feb 02 '12 at 19:15
Its just a task of going through all the types of the record sets and the types which your trying to pass to them. Try doing one at a time and testing each one to see exactly which ones are falling over — Standage, Feb 02 '12 at 19:18
It is typically the correct type for a freight column, but if the value you are trying to set it to is non-numeric and error will ensue. You can try testing for IsNumeric( frmItemProfile.txtFreightRate) and assigning 0 (or Null if you wish) and see if that resolves the problem. — ron tornambe, Feb 02 '12 at 19:26
ahh Ron, thats it. Sheeesh something so stupid. Create an answer so I can accept it :) — javasocute, Feb 02 '12 at 19:37

score 0 · Answer 1 · answered Feb 03 '12 at 08:40

Here's a possible repro:

Dim rs
Set rs = CreateObject("ADODB.Recordset")

With rs
  .Fields.Append "FREIGHT_RATE", adDouble, , 32  ' adFldIsNullable
  .Open
  .AddNew
  rs("FREIGHT_RATE") = "fifty-five"

End With

The error I get:

Multiple-step operation generated errors. Check each status value.

VBA type mismatch error

1 Answers1