VBA Coding Error - Runtime Error (1 Viewer)

lebewb

New member
Local time
Tomorrow, 09:04
Joined
Sep 4, 2018
Messages
6
Hi all,

I am having some trouble with my VBA code attached below. It is coming up with a runtime error (3464 - data type mismatch). I am unsure of what I have done wrong and any assistance would be greatly appreciated.

Private Sub cmdAdd_Click()
Dim strInsert As String
If IsNull(Me.txtPolicyNum) Or IsNull(Me.txtinception) Or IsNull(Me.txtExpiryDAte) Or IsNull(Me.txtAccountNumber) Then
MsgBox "Please Complete Policy Details"
Else
strInsert = "insert into VehicleRecords (Vehicle_is_registered, State_of_Registration, Registration_Number, State_of_base_operations, Suburb, Postcode, CoverType, Standard_excess, Imposed_excess, Total_variable_excess, No_claim_bonus_entitlement, claim_bonus_verified, protect_noclaim_bonus, Build_Year, Class, Make, Model, Redbook_code, Full_description, Vehicle_value, Standard_accessories, yn_nonstandard_accessories, yn_vehicle_mods, Value_NSA_Mods, NS_accessories, Policy_record, Inception_Date, Expiry_Date, Account_Number, Premium, Fire_Levy, GST, Stamp_Duty, Adj_Percentage, Premium_Subtotal, Premium_Adjustment_Amount, Commission_Amount, Commission_GST, Insured_Name, Postal_Address, Business_Occupation, Interested_Party, Nature_of_Interest)" & _
"values( '" & Me.cboVehicleRegistered & "','" & Me.cboStateofRego & "','" & Me.txtRegoNumber & "','" & Me.cboBaseOps & "','" & Me.txtBaseOperations & "','" & Me.txtPostcode & "','" & Me.cboCoverType & "','" & Me.txtStandardExcess & "','" & Me.txtImposedExcess & "','" & Me.txtTotalVariableExcess & "','" & Me.cboNoClaimBonusEntit & "','" & Me.cboVerifynoClaimBonus & "','" & Me.cboProtectNoClaim & "','" & Me.txtBuildYear & "','" & Me.cboClass & "','" & Me.cboMake & "','" & Me.cboModel & "','" & Me.txtRedbook & "','" & Me.txtFulldescription & "','" & Me.txtVehicleValue & "','" & Me.txtStandardaccessories & "','" & Me.cboNSAccessories & "','" & Me.cboMods & "','" & Me.txtModsValues & "','" & Me.txtnonstandardaccessories & "','" & Me.txtPolicyNum & "','" & Me.txtinception & "','" & Me.txtExpiryDAte & "','" & Me.txtAccountNumber & "','" & Me.txtPremium & "','" & Me.txtFireLevy & "','" & Me.txtGST & "','" & Me.txtStampDuty & "','" & Me.txtAdjPerc & "','" & Me.txtPremAdjAmount & "','" & _
Me.txtCommissionAmount & "','" & Me.txtCommissionGST & "','" & Me.txtSubtotal & "','" & Me.txtInsuredName & "','" & Me.txtPostalAddress & "','" & Me.txtBusiness & "','" & Me.txtParty & "','" & Me.txtInterest & "')"

DoCmd.SetWarnings False
CurrentDb.Execute strInsert, dbFailOnError
DoCmd.SetWarnings True
MsgBox "Vehicle added successfully"
'add data to table
End If
'refresh data from list on form
frmVehicleRecordsSubform.Form.Requery
End Sub
 

isladogs

MVP / VIP
Local time
Today, 22:04
Joined
Jan 14, 2017
Messages
18,186
Data type mismatch occurs when you use incorrect delimiters e.g. text delimiters for number fields, number delimiters for date fields etc.

All fields in your values section have text delimiters
If they are number fields, remove the single quotes.
If date fields, replace the single quotes with #.

Any other datatypes, come back again with details.

In future, please enclose your code in code tags. Use the '#' button on the reply to thread window toolbar
 
Last edited:

Ranman256

Well-known member
Local time
Today, 18:04
Joined
Apr 9, 2015
Messages
4,339
Try not to use long strings of vb to make SQL?
Instead use a query.
This helps you debug the Data type mismatch and other SQL errors.
Code does not.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:04
Joined
May 21, 2018
Messages
8,463
That is a lot to bite off in one swoop. One mistaken " or ' or # or NULL field and this fails.
So I would have a function like this. Then wrap all of your values and no need for delimeters.
..."VALUES (" & csql(Me.cboVehicleRegistered) & "," & csql(Me.cboStateofRego) & "," & csql(Me.txtRegoNumber) & "
when all done debug.print the string so we can seel

Code:
' Converts a value of any type to its string representation.
' The function can be concatenated into an SQL expression as is
' without any delimiters or leading/trailing white-space.
'
' Examples:
'   SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
'   SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
'
'   SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
'   SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
'
' Trims text variables for leading/trailing Space and secures single quotes.
' Replaces zero length strings with Null.
' Formats date/time variables as safe string expressions.
' Uses Str to format decimal values to string expressions.
' Returns Null for values that cannot be expressed with a string expression.
'
' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function CSql( _
    ByVal Value As Variant) _
    As String

    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = "Null"

    Dim Sql             As String
   
    Select Case VarType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = Str(Value)
        Case vbLong             '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = Str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
            If DateValue(Value) = Value Then
               Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
            Else
               Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
            End If
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = Str(Value)
        Case vbByte             '   17  Byte.
            Sql = Str(Value)
         Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Trim(Sql)

End Function

The other solution would be to use a Parameter query which would be shorter and cleaner.

Something like this
Code:
Public Function ParamInsert(TableName As String, TheFields As String, ParamArray TheValues() As Variant) As String
  Dim qdf As QueryDef
  'Set qdf = CreateObject("queryDef")
  Dim i As Integer
  Dim MyParams As New Collection
  Dim strMyParams As String
  Dim strSql As String
  For i = 0 To UBound(TheValues)
    MyParams.Add "Param" & i, "Param" & i
    If strMyParams = "" Then
      strMyParams = "[" & MyParams(i + 1) & "]"
    Else
      strMyParams = strMyParams & ", " & "[" & MyParams(i + 1) & "]"
    End If
  Next i
  strSql = "INSERT INTO " & TableName & " " & TheFields & " VALUES ( " & strMyParams & ")"
  ParamInsert = strSql
  Set qdf = CurrentDb.CreateQueryDef("TempQuery", strSql)
   For i = 0 To UBound(TheValues)
    qdf.Parameters(i) = TheValues(i)
  Next i
  qdf.Execute
  CurrentDb.QueryDefs.Delete ("tempquery")
End Function

Public Sub TestParamInsert()
  Dim TheFields As String
  TheFields = "(FirstName, LastName, OrderID, OrderDate)"
  ParamInsert "MyTable", TheFields, "John", "Smith", 1, #1/1/2018#
End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:04
Joined
May 21, 2018
Messages
8,463
I passed literals into the ParamInsert but you can simply use control references instead.
 

Users who are viewing this thread

Top Bottom