SQL Statement Help with Variable Form Name

[txgeekgirl]

I have a Global Variable MyOpenForm which returns the correct name of the form opened.

My_SQL = "INSERT INTO [Activities Report Data] ([TS_Staff], [TS_Date], [TS_Time], [TS_Index], [TS_Note]) " + _
"SELECT " + [Forms]![MyOpenForm]![User_Echo] + ", #" + D_Date + "#, '" + Left$(Target, 2) + "15', 208, ''"
DoCmd.RunSQL My_SQL

I am trying to stuff it into this SQL statement but it is NOT happy. Any suggestions?

 

[pbaldy]

Try

..."SELECT " & [Forms]![" & MyOpenForm & "]![User_Echo] & ", #"...

 

[pbaldy]

Or perhaps


..."SELECT [Forms]![" & MyOpenForm & "]![User_Echo] , #"...

Not sure exactly what you're trying to accomplish, but maybe that gets you on track?

 

[txgeekgirl]

I have two forms which could pass a value named User_Echo. I had the SQL in If Statements to distinguish the form difference in name. My boss said he would like to see the FormName be a variable so that it can be passed to the SQL statement and I could do away with the IFs.

Now - the problem with your solution is that it puts "" around my form name so I end up with ["FormName"] and it fails.

 

[boblarson]

My_SQL = "INSERT INTO [Activities Report Data] ([TS_Staff], [TS_Date], [TS_Time], [TS_Index], [TS_Note]) " + _
"SELECT " + Forms(MyOpenForm).User_Echo + ", #" + D_Date + "#, '" + Left$(Target, 2) + "15', 208, ''"
DoCmd.RunSQL My_SQL

 

[pbaldy]

I goofed up the first one. Did you try the second?

 

[Endre]

You need Triple Double-Quotes with text, Single double-quotes with numerics e.g

DoCmd.RunSQL("SELECT * FROM TABLE WHERE TABLE.TEXTFIELD = """ & Forms!MainForm!TxtControl & """ AND TABLE.NUMFIELD = " & Forms!MainForm!IntControl & ";")

or the reverse:

DoCmd.RunSQL("SELECT * FROM TABLE WHERE TABLE.NUMFIELD = " & Forms!MainForm!IntControl & " AND TABLE.TXTFIELD = """ & Forms!MainForm!TxtControl & """;")

 

[txgeekgirl]

Bob Larson - You are the winner of the day!!!! Your solution worked. Thank you very much!