Absolutely Stumped on a DoCmd.RunSQL issue

[Nuke1096]

So I'm trying to put together a pretty basic form for a super small project. All I'm looking to do currently is have a form with 3 fields and a submit to put into a database when the submit button is clicked. I'm using the DoCmd.RunSQL function cause I've used it in the past alot. However, for some reason I keep getting this error...


Runtime 3134 Error "Syntex Error in Insert Into Statement"

What's odd though is that I am basically copying and pasting my code from old projects so it should not be any different outside of obvious stuff like text box names, which I checked. Anywhere, here is my code. I made sure the form fields were unbound and enabled also.

  Private Sub SubmitButton_Click()     
     
  Dim strSQL As String
     
      
  ' Appends the form fields to the table.
  
  strSQL = "INSERT INTO Table_Lancaster_Dispatch (Log, Date, Dispatcher) " & _
  "SELECT [Forms]![Form_Dispatch_New]![LogBox] AS Expr1, " & _
  "[Forms]![Form_Dispatch_New]![DateBox] AS Expr2, " & _
  "[Forms]![Form_Dispatch_New]![DispatchBox] AS Expr3"
  
  DoCmd.RunSQL (strSQL)

  End Sub

Totally stumped.

[amrut]

A semi-colon at the end of SQL statement

 strSQL = "INSERT INTO Table_Lancaster_Dispatch (Log, Date, Dispatcher) " & _
  "SELECT [Forms]![Form_Dispatch_New]![LogBox] AS Expr1, " & _
  "[Forms]![Form_Dispatch_New]![DateBox] AS Expr2, " & _
  "[Forms]![Form_Dispatch_New]![DispatchBox] AS Expr3" & ";"

[rpeare]

type
debug.print strSQL before you attempt to run the SQL code and see if it's giving you a valid SQL statement, you can check by cutting and pasting the sql string from your immediate window into the query designer and seeing what it's actually attempting to put into your table, but I suspect it's becaquse you're not qualifying strings and dates correctly.

For instance:

strSQL = "INSERT INTO Table_Lancaster_Dispatch (Log, Date, Dispatcher) VALUES (" 
strsql = strsql & "'" & [Forms]![Form_Dispatch_New]![LogBox] & "', "
strsql = strsql & "#" & [Forms]![Form_Dispatch_New]![DateBox] & "#, " 
strsql = strsql & "'" & [Forms]![Form_Dispatch_New]![DispatchBox] & "')"

I assume you're trying to insert a record into a table based on what's showing in specific fields on your form (i.e. an unbound data entry form)

For the purposes of this example I assumed LOG and DISPATCHER were text values, if either is a number you want to remove the text (') notations from the stringfor example if dispatcher is a number (FK to an employees table for example) you'd have:

strSQL = "INSERT INTO Table_Lancaster_Dispatch (Log, Date, Dispatcher) VALUES (" 
strsql = strsql & "'" & [Forms]![Form_Dispatch_New]![LogBox] & "', "
strsql = strsql & "#" & [Forms]![Form_Dispatch_New]![DateBox] & "#, " 
strsql = strsql & [Forms]![Form_Dispatch_New]![DispatchBox] & ")"

[Nuke1096]

I assume you're trying to insert a record into a table based on what's showing in specific fields on your form (i.e. an unbound data entry form)

Correct, that's exactly what I'm trying to do. Log, Date, and Dispatcher are the 3 Table Fields in my table (plus an ID field). LogBox, DateBox, and DispatchBox are the Text Fields on the form. Unfortunately, I tried your code and it produced the same error. I'm stumped as to why my code wont work though when its the exact code I used in many other forms to do the same thing.

Also Amrut, I tried adding the semicolon too but that didn't make any difference.

[rpeare]

paste the actual string that's being produced

debug.print StrSQL (as explained in the my previous post)

did you cut and paste the SQL code that you are generating into a query designer to see what it's actually trying to do vs what you think it's trying to do.

Look in your immediate window cut and paste whatever is showing there to this forum

[Nuke1096]

Where can I find the code that the debug.print StrSQL generates so that I can post it? I put that line of code before the DoCmd.RunSQL (strSQL) line but nothing really happens outside of the usual error.

[Nuke1096]

Ok, I think I figured out how to get the info from the immediate window.

This is what it shows there with the debug.print line in my code

INSERT INTO Table_Lancaster_Dispatch (Log, Date, Dispatcher) SELECT [Forms]![Form_Dispatch_New]![LogBox] AS Expr1, [Forms]![Form_Dispatch_New]![DateBox] AS Expr2, [Forms]![Form_Dispatch_New]![DispatchBox] AS Expr3

[rpeare]

paste the actual string that's being produced

debug.print StrSQL (as explained in the my previous post)

did you cut and paste the SQL code that you are generating into a query designer to see what it's actually trying to do vs what you think it's trying to do.

Look in your immediate window cut and paste whatever is showing there to this forum

Third time's the charm....

[Nuke1096]

When I tried your code instead of mine, it gave me this.

INSERT INTO Table_Lancaster_Dispatch (Log, Date, Dispatcher) VALUES ('zzzzzzzz', #1/17/2014#, zzzzzzzz)

[rpeare]

that's exactly what it's supposed to say, except, as I pointed out in my original post, the value of zzzzzzz in the DISPATCHER field has to be enclosed in ' marks if it's a text value (as you've typed it).

[Nuke1096]

Using your code of....

  
  strSQL = "INSERT INTO Table_Lancaster_Dispatch (Log, Date, Dispatcher) VALUES ("  
  strSQL = strSQL & "'" & [Forms]![Form_Dispatch_New]![LogBox] & "', "
  strSQL = strSQL & "#" & [Forms]![Form_Dispatch_New]![DateBox] & "#, "
  strSQL = strSQL & "'" & [Forms]![Form_Dispatch_New]![DispatchBox] & "')"

I still get the same error with this showing up in the immediate window. If this is what is suppose to be happening, why does the error persist? Even more confused now.

INSERT INTO Table_Lancaster_Dispatch (Log, Date, Dispatcher) VALUES ('zzzzzz', #1/17/2014#, 'zzzzz')

[rpeare]

WHat are the data types of

LOG, DATE and DISPATCHER

to note a text field you enclose in ' marks
to note a date field you enclose in # marks
to note a number field you don't use any marks

Does your form agree with what you are trying to append to your table

In other words if your DISPATCHER on your table is a number (for instance referencing a FK to an employee table) but on your form, you have a combo box where the bound column is the employee name, you're going to have a basic disconnect in trying to append the information because one is a text field, one is a number field.

[Nuke1096]

WHat are the data types of

LOG, DATE and DISPATCHER

to note a text field you enclose in ' marks
to note a date field you enclose in # marks
to note a number field you don't use any marks

Does your form agree with what you are trying to append to your table

In other words if your DISPATCHER on your table is a number (for instance referencing a FK to an employee table) but on your form, you have a combo box where the bound column is the employee name, you're going to have a basic disconnect in trying to append the information because one is a text field, one is a number field.

Log and Dispatcher are standard text strings. Date is date.

The properties for Log and Dispatcher are identical, except in the table where Log has 255 character limit and Dispatcher has 45. I attached my database so you can better see what how I have everything set.

[rpeare]

I don't think you cut and paste the SQL statement into a Query window or it'd have been obvious I think.

You are using 'date' as a field name, date is a reserved word and you should not use it as the name for ANY database object (field name, query name, table name, etc)

If you are determined to use these field names you would have to enclose the field name in square brackets [ ]

One other point to note, your controls are unbound but your form has a record source listing your table. You should probably delete your record source from your form if you want this to remain an unbound form.