Dynamically refer to Query

[lorirobn]

Hello,

I would like to combine 3 forms because they are almost identical, except for the dynamic SQL generated in the VBA. Instead of the SQL string, can I do this: create a separate Access query for each, and dynamically refer to the appropriate query within the VBA? (would that be a docmd.openquery? do I need any other statements?)

I actually do not even know how to dynamically refer to a query, but if I did, I imagine I can create an 'if' statement?

Thanks in advance for any help.

 

[Eupher]

Hi, lorirobn,

Seems to me it's six of one and half-dozen of the other. I don't think it matters whether you use saved queries or SQL strings in code. The crux of the issue is determining which query/SQL to use. Perhaps you could pass some criteria to the form in the OpenArgs argument of the OpenForm method?

Ken S.

 

[Zion7]

Yes lorirobin, as Eupher said, incidental as how
you save the queries, as long as you have them.
whether you use OpenArg, or maybe from the onLoad Of form,
You could even just generate an SQL, without saving any query.

Private Sub Form_load()
Select case "whatever determines recordset"
case 'Countries"
SQL = "SELECT * FROM tblCountry"
Case "SolarSystem"
SQL = "seLECT * FROM tblSolarSystem " & _
"WHERE txtSatelite = 'Kumar'"
Case "Movies"
SQL = "SELECT txtShow, txtActor," & _
"txtLastName & ', ' & txtFirstname " & _
"FROM tblMovie"
CASE Else
SQL = "qrySaleYearly"
end sElect

Me.recordsource = SQL