DoCmd.RunSQL Fun

Morning folks,

First time poster, so apologies if I don't give you all the information you may need to begin with.. (give me a prompt if you need anything else)..

I'm building my first real database to handle version information from a large number of servers and need to build a front end to that database for users to use to query the data to find servers to which upgrades need to be shipped. To that end I'm creating a form with combo boxes populated as the previous box is updated.. I managed to get that bit working and thought the next would be simple.. Run a query based on the selections given..

Here's the code I'm using at the moment..


Private Sub Command14_Click()
Dim FISH
FISH = "SELECT Net_IDS.Net_ID FROM [" & Product_Select_1 & "] INNER JOIN Net_IDS ON [" & Product_Select_1 & "].Net_ID = Net_IDS.Net_ID WHERE [" & Product_Select_1 & "].Version = """ & Version_Select_1 & """ GROUP BY Net_IDS.Net_ID;"
Me.Text12.Value = FISH
DoCmd.RunSQL "SELECT Net_IDS.Net_ID FROM [" & Product_Select_1 & "] INNER JOIN Net_IDS ON [" & Product_Select_1 & "].Net_ID = Net_IDS.Net_ID WHERE [" & Product_Select_1 & "].Version = """ & Version_Select_1 & """ GROUP BY Net_IDS.Net_ID;"
End Sub

Yes, it's a touch messy, and strictly speaking the FISH doesn't need to be there (but I'm using it for testing purposes as I try to work around the problem..

Anyway, when attempting to run this I'm given a runtime error 2342, A RunSQL action requires an argument of an SQL statement..

I'm hoping I've missed something simple!

Cheers in advance folks..

RunSQL is only for ActionQueries and NOT SelectQueries. You can use the SelectQuery and open in a recordset and do things to it. Look in Access help on recordsets.

JR

That would definitely explain why it doesn't work! Looks like I've got a little more reading to do..

Thanks for taking the time to reply..

CORRECTION:

DoCMD.RUNSQL can be used for SELECT queries in the following manner:
DoCMD.RUNSQL "SELECT * INTO table2 FROM table1".....as long as you are selecting into a new table, there will be no dreaded 2342 error message.

there will be no dreaded 2342 error message.

Click to expand...

No only to be replaced with an other error if you run it twice since this is basically a Make-Table query!!!

By definition an Actionquery

JR

For info, the route I used to get around the issue was to assemble the query as a string, (strsql1 = blah, strsql2=more blah, etc ... strsql = strsql1 & strsql2 etc etc... ) Then created a "temp query" with the following...

Set QD = DB.CreateQueryDef("Temp_Query", strSql)
DoCmd.OpenQuery "Temp_Query"

It's not perfect, but it does the job..