VBA Insert Sql Statement

[Workaholic_711]

Hello,
I have the below code but it keeps erroring out - Run time error 3464


Private Sub Command97_Click()

Dim strSQL As String

strSQL = "INSERT INTO [TblName]([Field1],[ Field2],[ Field3],[ Field4],[ Field5]," _
& "[ Field6],[ Field7],[ Field8],[Field9]," _
& "[ Field10],[ Field11],[ Field12],[ Field13],[ Field13],[ Field14]," _
& "[ Field15],[ Field16],[ Field17],[ Field18],[ Field19],[ Field20],[ Field21],[ Field22],[ Field23],[ Field24]," _
& "[ Field25],[ Field26],[ Field27],[ Field28],[ Field29],[ Field30],[ Field31],[ Field32],[ Field33],[ Field34],[ Field35],[ Field36],[ Field37]) VALUES " _
& "('" & Me!Text100 & "','" & Me!Text106 & "','" & Me!Text114 & "', '" & Me!Text48 & "','" & Me!Text50 & "','" & Me!Text52 & "','" & Me!Text119 & "','" & Me!Text110 & "','" & Me!Text75 & "','" _
& Me!Text113 & "','" & Me!Text77 & "','" & Me!Text80 & "','" & Me!Text82 & "','" & Me.Text84 & "','" & Me.Text86 & "','" & Me.Text88 & "','" & Me.Text90 & "','" & Me.Text92 & "','" & Me.Text94 & "','" & Me.Text96 & "','" _
& Me!Text125 & "','" & Me!Text127 & "',ABS('" & Me!Check161 & "'),ABS('" & Me!Check182 & "'),ABS('" & Me!Check191 & "'),ABS('" & Me!Check194 & "'),ABS('" & Me!Check164 & "'),ABS('" & Me!Check166 & "'),ABS('" & Me!Check168 & "'),ABS('" & Me!Check170 & "'),ABS('" & Me!Check172 & "'),ABS('" & Me!Check174 & "'),ABS('" & Me!Check176 & "'),ABS('" & Me!Check178 & "'),ABS('" & Me!Check180 & "'),ABS('" & Me!Check185 & "'),ABS('" & Me!Check187 & "'),ABS('" & Me!Check189 & "'),ABS('" & Me!Check200 & "'));"

If Me!Text127 = "Yes" Then
MsgBox ("Record has not been added")
Else
MsgBox ("Record has been added")
End If


DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

End Sub

 

[isladogs]

Are those really your actual table, field and control names?
If not, please post the actual SQL.
If genuine, then your naming convention is a big issue.

I suspect your issue is with all the Boolean values based on string values from checkboxes

 

[pbaldy]

This should help:

www.baldyweb.com/ImmediateWindow.htm

You wouldn't want quotes around a number inside the Abs() function.

 

[Workaholic_711]

Thank you for looking into it.

I figured out the problem, the code errors out when any one of the checkboxes is left unchecked.

I tested the button with all check boxes checked, and it works fine but when I leave a box unchecked, that's where it errors out.

 

[MajP]

Wow, I am amazed that works since that code is just a disaster. For each check you take the absolute value of the checkbox and turn it into a string "1" or "0". Uncheck should return "0" and should be no problem, unless this is unbound and Triple State = True. Then that resolves to Null. Then ABS(Null) will fail.

 

[Workaholic_711]

Thank you the feedback and explicitly mentioning that the code is a disaster. If it worked as expected, then I wouldn't have posted the question here

Can you guide me and if possible post a solution to this?

Are you implicating that taking out the ABS(checkbox#) is the solution and the code will not error out if any of the checkboxes is unchecked?

Also, is there a way to append the value as an absolute number , 1 or 0 to the table?

I am a beginner in VBA, therefore any help is appreciated.

Thanks.

 

[June7]

If you want to save value to a number field, don't use apostrophe delimiters. Even if you were saving to text field, the apostrophes are in wrong location.

For number field use:

ABS(" & Me!Check166 & ")

or don't put ABS within quote marks.

& "','" & Me!Text127 & "'," & ABS(Me!Check161) & "," & ABS(Me!Check182) & "," & ABS(Me!Check191) & "," …

Verify whether checkbox is set for triple state.

Date/time fields require # delimiter.

 

[Workaholic_711]

Thank you! Thank you!

It worked!!!!!!