DoCmd.RunSQL Fun

[boostbyslinky]

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..

 

[JANR]

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

 

[boostbyslinky]

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..

 

[jason97m]

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.

 

[JANR]

there will be no dreaded 2342 error message.

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

 

[boostbyslinky]

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..