Code/SQL experts help

[gbanks]

I run this code from a button on a form.. When I run the code it gives me an error 'Run-Time error '3134' Syntax error in INSERT INTO statement.. Obviously the error deals with the INSERT INTO part of the SQL statement, but I checked the "SELECT" part of the SQL and it does pull the data I need.. So that part is correct.. Program is a string for the name of the table I want to insert the data into.. Appendtable is a string for the name of the table I want to select the data from... I even tried building the SQL as an append query and using that SQL but I got the same error.. Any thoughts? Sorry I didn't know the best place to post this Query or Module.. Thanks

Dim Program As String
Dim AppendTable As String
Program = pgmtbl
AppendTable = Program & "Resolutiontbl"

DoCmd.RunSQL "INSERT INTO " & Program & "SELECT " & AppendTable & ".[Voucher#]," & AppendTable & ".[First Name]," & AppendTable & ".[Last Name]," & AppendTable & ".[Appt#] FROM " & AppendTable & "

 

[ntp]

DoCmd.RunSQL "INSERT INTO " & Program & "SELECT " & AppendTable & ".[Voucher#]," & AppendTable & ".[First Name]," & AppendTable & ".[Last Name]," & AppendTable & ".[Appt#] FROM " & AppendTable & "

Notice in your code you have (& Program & "SELECT ") if this is converted let us say program has the string value "tblName" what you are doing is
"tblName" & "SELECT " giving you "tblNameSELECT "
You need a space before the 'S' in SELECT.

ntp

 

[gbanks]

Thanks.. Must have been a brain cramp..

 

[Atomic Shrimp]

I know you've got this sorted now, but I often get this sort of problem; here's a debug tip:

What I do is define a temporary string variable at the top of the code called, say, strSQLtemp, then replace:

DoCmd.RunSQL "[whatever]" & [SomeFieldName] &"[etc]"

with

strSQLtemp = "[whatever]" & [SomeFieldName] &"[etc]"
DoCmd.RunSQL strSQLtemp

Then if it doesn't work, you can put a breakpoint on the DoCmd line and watch the strSQLtemp variable to look for any oddities.

HTH

Mike

 

[BarkerD]

I find it is always helpful to replace any SQL calls in my code with string variables. It makes the code easier to read, and like Mike said, it is easy to check the SQL statement for errors.

I like to use
Debug.Print strSQL

Duane Barker