I need help to troubleshoot an Insert Into SQL statement.

[johngalt69austin]

I am writing an "Insert Into" query that when activated (via a check box event procedure) will insert into table "NCStatusChange" the following fields:

NCNumber
SystemNumber
DateTime
UserId
via a SQL query. The data is being pulled from a form called "frmEngByNCNo". Unfortunately it is not working and I can't figure out why. It keeps coming back to a "Runtime Error" Invalid Syntax in INSERT INTO statement. Can anyone else see where I am going wrong?


Private Sub IssueClosed_Click()
On Err GoTo Err_IssueClosed_Click

Dim SQL As String

Dim SystemNumber As String
Dim UserID As String
Dim NCNumber As String
Dim DateTime As String



SystemNumber = [Forms]!frmEngByNCNo.SystemNumber
DateTime = [Forms]!frmEngByNCNo.DateTime
UserID = [Forms]!frmEngByNCNo.IssueClosedBy
NCNumber = [Forms]!frmEngByNCNo.NCNumber

SQL = "INSERT INTO NCStatusChange(NCNumber, SystemNumber, DateTime, UserId)" & _
"Values('" & NCNumber & "','" & SystemNumber & "','" & DateTime & "', '" & UserID & "');"

DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

Exit_IssueClosed_Click:
Exit Sub

Err_IssueClosed_Click:
MsgBox Err.Description, vbOKOnly
Resume Exit_IssueClosed_Click

End Sub

 

[lespaul]

Is NCNumber a string? If not, remove the ' from the statement:

" & NCNumber & "

Same with SystemNumber. If it's not a string, remove the '.

DateTime shouldn't have a ' it should have a # (maybe, I'm not sure how JET handles the date. I know in a true Access query you HAVE to have the # around date fields)

try that.

les

 

[JonFer]

Set a breakpoint in your module at the "SQL=" step (click in the grey column that is the left border) and then hit F8 to process that step. Position your cursor over the SQL variable and you'll see the SQL statement (which is lacking a few spaces from what I see). You can also add the "SQL" variable to your Watch window.

 

[KaayJaay]

Im doing it the way you said now, but i am gettin an SQL error, but i cant see anything wrong. Any Suggestions? Heres Code:

Private Sub Command20_Click()
On Err GoTo Err_IssueClosed_Click

Dim SQL As String

Dim mu_id As String
Dim tech_num As String
Dim Name As String
Dim SBC_ID As String
Dim veh_num As String
Dim page_num As String
Dim vts_num As String


mu_id = [Forms]![Add Technician]![MU]
tech_num = [Forms]![Add Technician]![Tech#]
Name = [Forms]![Add Technician]![Name]
SBC_ID = [Forms]![Add Technician]![SBCUID]
veh_num = [Forms]![Add Technician]![Veh#]
page_num = [Forms]![Add Technician]![Pager#]
vts_num = [Forms]![Add Technician]![VTS]


SQL = "INSERT INTO Tech_Info(MU, Tech#, Name, SBCUID, Veh#, Pager#, VTS) Values('" & mu_id & "','" & tech_num & "','" & Name & "', '" & SBC_ID & "', '" & veh_num & "', '" & page_num & "', '" & vts_num & "');"

DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

Exit_IssueClosed_Click:
Exit Sub

Err_IssueClosed_Click:
MsgBox Err.Description, vbOKOnly
Resume Exit_IssueClosed_Click

End Sub

KaayJaay

 

[lespaul]

what are these field types in the TABLE?

MU
Tech#
Name
SBCUID
Veh#
Pager#
VTS

if MU is a string in the table it needs the ' in the SQL you are building, if it's a number in the table it doesn't need the ' in the SQL you are building.


Leslie

 

[johngalt69austin]

Try This.

Private Sub Command20_Click()
On Err GoTo Err_IssueClosed_Click

Dim SQL As String

Dim mu_id As String
Dim tech_num As String
Dim Name As String
Dim SBC_ID As String
Dim veh_num As String
Dim page_num As String
Dim vts_num As String


mu_id = [Forms]![Add Technician]![MU]
tech_num = [Forms]![Add Technician]![Tech#]
Name = [Forms]![Add Technician]![Name]
SBC_ID = [Forms]![Add Technician]![SBCUID]
veh_num = [Forms]![Add Technician]![Veh#]
page_num = [Forms]![Add Technician]![Pager#]
vts_num = [Forms]![Add Technician]![VTS]


SQL = "INSERT INTO Tech_Info (MU, Tech#, Name, SBCUID, Veh#, Pager#, VTS) & _
“Values('" & mu_id & "','" & tech_num & "','" & Name & "', '" & SBC_ID & "', '" & veh_num & "', '" & page_num & "', '" & vts_num & "');"

DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

Exit_IssueClosed_Click:
Exit Sub

Err_IssueClosed_Click:
MsgBox Err.Description, vbOKOnly
Resume Exit_IssueClosed_Click

End Sub


Are you using the "#" sign as field names (tech_num = [Forms]![Add Technician]![Tech#]) in your tables? I think I would change the naming configuration to "No" or "Num" or something like that. If those are just your label names then you need to specify the field names in your description.

jcw

 

[PHV]

I would use brackets for the fieldnames with # in their name, and for the reserved keyword Name.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244