Run Time Error '3134' - Syntax Assistance Required (1 Viewer)

Ranman256 said:

Have you trie using a query instead of code?
Queries tell you what is wrong.

Click to expand...

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, 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, 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, Class, Make, Model, Build_Year, PremiumAmount, FireLevy, GST, StampDuty)" & _
"values( " & Me.cboVehicleRegistered & ",'" & Me.cboStateofRego & "','" & 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.txtRegoNumber & "','" & Me.cboBaseOps & "','" & Me.txtBaseOperations & "','" & Me.txtPostcode & "','" & Me.cboCoverType & "','" & Me.txtStandardaccessories & "','" & Me.txtImposedExcess & "','" & Me.txtTotalVariableExcess & "','" & Me.cboNoClaimBonusEntit & "','" & Me.cboVerifynoClaimBonus & "','" & Me.cboProtectNoClaim & "','" & Me.cboClass & "','" & Me.cboMake & "','" & Me.cboModel & "','" & Me.txtBuildYear & "','" & Me.txtPremium & "','" & Me.txtFire & "','" & Me.txtGST & "','" & Me.txtStamp & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL strInsert
DoCmd.SetWarnings True
MsgBox "Vehicle added successfully"
'add data to table

End If
'refresh data from list on form
subfrmVehicleRecords.Form.Requery
End Sub

ridders said:

So does SQL.
The error message should have told you which section of the code was faulty

Using code tags makes it much easier to read/check code though in your case there are so many values that it's still difficult!

In your values all except the first are for text fields. That includes one called txtAccountNumber.
Is the bound column of cboVehicleRegistered and the destination field Vehicle_Is_Registered a number field?
Do you have any other number or date fields in this sql?

Code:Copy to clipboard

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, 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, 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, Class, Make, Model, Build_Year, PremiumAmount, FireLevy, GST, StampDuty)" & _
"values( " & Me.cboVehicleRegistered & ",'" & Me.cboStateofRego & "','" & 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.txtRegoNumber & "','" & Me.cboBaseOps & "','" & Me.txtBaseOperations & "','" & Me.txtPostcode & "','" & Me.cboCoverType & "','" & Me.txtStandardaccessories & "','" & Me.txtImposedExcess & "','" & Me.txtTotalVariableExcess & "','" & Me.cboNoClaimBonusEntit & "','" & Me.cboVerifynoClaimBonus & "','" & Me.cboProtectNoClaim & "','" & Me.cboClass & "','" & Me.cboMake & "','" & Me.cboModel & "','" & Me.txtBuildYear & "','" & Me.txtPremium & "','" & Me.txtFire & "','" & Me.txtGST & "','" & Me.txtStamp & "')"
DoCmd.SetWarnings False
[COLOR="Red"]DoCmd.RunSQL strInsert[/COLOR]
DoCmd.SetWarnings True
MsgBox "Vehicle added successfully"
'add data to table

End If
'refresh data from list on form
subfrmVehicleRecords.Form.Requery
End Sub

Click to expand...

lebewb said:

The error is showing up as the line that says DoCmd.RunSQL

Further to your point, all fields should be text as this solution is a simple workaround at this stage and has potential variations in the format data will be entered.

Click to expand...

CurrentDb.Execute strInsert, dbFailOnError

ridders said:

Keep the space before values which arnel spotted.
Add text delimiters to the first item in the values section
'" & cboVehicleRegistered & "'

Are you sure the bound column of the combo is text?
Are you sure the dates fields are text?

Also replace the DoCmd.RunSQL line and the two set warning lines with

Code:Copy to clipboard

CurrntDb.Execute strInsert, dbFailOnError

Finally adding error handling would simplify problem solving a lot

Click to expand...