SQL statement where the table field names come from a form

[lakefun]

I'm trying to run a sql statement where the field names of the table are supplied by the user in a form. When I run the statement below, I get the value of [forms]![form1]![q1] in q1 and what I want are the values of the field that is named in [forms]![form1]![q1].

INSERT INTO t ( q1 ) SELECT [forms]![form1]![q1] FROM [1st table];


EXAMPLE: [forms]![form1]![q1] = Field 133

Right now, field "q1" has the value "Field 133". "q1" should look just like field "Field 133".

I hope this makes sense.

Thanks!

lakefun

 

[JoeMiller]

DoCmd.RunSQL "INSERT INTO t ( q1 ) SELECT "& [forms]![form1]![q1] & "FROM [1st table];" Joe Miller
joe.miller@flotech.net

 

[lakefun]

After making the changes, I now get an syntax error message about a missing operator in query expression 'Field 133' (which is the value from the form). Any ideas?

Thanks for your help!

 

[JoeMiller]

Ahh.. i'm sorry, I should have noticed this before, you have a SPACE in your field name (a no-no which I won't scold for this time). Change it to this:

DoCmd.RunSQL "INSERT INTO t ( q1 ) SELECT ["& [forms]![form1]![q1] & "] FROM [1st table];" Joe Miller
joe.miller@flotech.net

 

[lakefun]

THANK YOU - THANK YOU - THANK YOU!!!

You are a lifesaver!!

Have a great weekend - I'm going to now!

P.S. But first, I'm going to fix my fieldnames!

 

[JoeMiller]

Glad it's working, have a good weekend..

Joe Miller
joe.miller@flotech.net

 

[lakefun]

Just one more thing.....

I need to add 2 more fields to the statement. When I do
this, I'm getting a syntax error. Would you please look
at it? I'm very new at VB code and this is throwing me for a loop.


DoCmd.RunSQL "INSERT INTO t(q1),(q2),(q3) SELECT " & [Forms]![form1]![q1] & , & [Forms]![form1]![q2] & , & [Forms]![form1]![q3] & " FROM [1st table];"

Thanks again!

P.S. I did remove the space on the field names.

 

[JoeMiller]

What you are doing is building a string to pass to the SQL engine, so you need to put " " around anything that isn't a variable, namely your commas between the different form fields. Try this:

DoCmd.RunSQL "INSERT INTO t(q1),(q2),(q3) SELECT " & [Forms]![form1]![q1] & ", " & [Forms]![form1]![q2] & ", " & [Forms]![form1]![q3] & " FROM [1st table];" Joe Miller
joe.miller@flotech.net

 

[lakefun]

Thanks again!!!!