copy query to a table

[ossse]

I am trying to use VBA to copy query results (a full query table) into a new Table. It sounds easy but I have been struggling with this all day; trying several different approaches with no luck.

A point in the right direction will be much appreciated.

Thanks

 

[PHV]

Have a look at maketable query (SELECT ... INTO ... FROM ...)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ossse]

Thank you for the quick reply... I've looked at the 'make table queries' but I can't find the correct format to write it in VBA. Do you know another website or a post that has describes it (I can't find one)

 

[PHV]

DoCmd.RunSQL "SELECT ... INTO ... FROM ..."

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ossse]

OK, I've semi-taught myself how to set it up and I am still having some problems. When I physically build the 'make-table query' myself it works... so I copied the SQL from that table into the VBA and I am getting an error. (Note: I am using the Concatenate function to create my third field.

Here is my line of code:

DoCmd.RunSQL "SELECT Tasks.taskCEID AS CEID, Tasks.taskID AS taskID2, Concatenate("SELECT taskCauseFailureCause FROM TaskCauses WHERE taskCauseTaskID=""" & [taskID]) AS Cause INTO test2 FROM Tasks ORDER BY Tasks.taskCEID, Tasks.taskID;"

The error that I am getting is "compile error: expected end of statement". and then the bolded 'SELECT' gets highlighted.

Any Ideas? Thanks

 

[PHV]

What about this ?

DoCmd.RunSQL "SELECT taskCEID AS CEID,taskID AS taskID2,Concatenate('SELECT taskCauseFailureCause FROM TaskCauses WHERE taskCauseTaskID=' & [taskID]) AS Cause INTO test2 FROM Tasks ORDER BY taskCEID,taskID;"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ossse]

Works great! When I run it, a pop up window always asks me

"You are about to paste 190 row(s) into a new table.... do you want to continue... Yes/No"

Is there a way to prevent this pop up window? If not, it's no big deal

 

[sxschech]

docmd.setwarnings false to turn off popup message

docmd.setwarnings true to turn on