VBA to Query: Can a form control define a table in the "FROM" statement of query

[tonere]

I have a module that, among other things, appends the field "SFRSTCR_PIDM" from the table "XB201370_F" to the table "XB201270_F". The module runs fine.

When I copy the VBA from the module into a query via SQL design view, I get the error "Invalid Bracketing of name '"[Forms]![F_Create_Extracts]![OIE_Term]"'.

I have a form "F_Create_Extracts" that has a text box "OIE_Term". The form is open and the field is populated with "201370_F". The VBA code from the module is:

strSQL = " INSERT INTO XB201270_F ( SFRSTCR_PIDM )" & _
" SELECT XB_Alias.SFRSTCR_PIDM" & _
" FROM XB" & [Forms]![F_Create_Extracts]![OIE_Term] & " AS XB_Alias;"
DoCmd.RunSQL strSQL

When I copy the code into query SQL design view, it looks like this:

INSERT INTO XB201270_F ( SFRSTCR_PIDM )
SELECT XB_Alias.SFRSTCR_PIDM
FROM XB" & [Forms]![F_Create_Extracts]![OIE_Term] & " AS XB_Alias;

Is this not allowed or is my code missing something?



Thank you all in advance for reviewing.

[pbaldy]

You can do it, but it would have to be in code as in your first example. You need it so the finished SQL resolves to the proper table name, then you can execute it in code:

CurrentDb.Execute strSQL