Passing parameters to report record source subquery

[Seth Schrock]

I have a very large UNION query with both sides pulling from several tables and subqueries. Due to the nature of the query, it naturally runs very slowly: about six and a half minutes. To fix this, I'm having to put a WHERE clause in the subqueries to filter the foreign keys as well as in the main UNION query. This query is the basis for a report. Normally, I would just open the report, passing it a WhereCondition argument, but that only filters the main query, after all the joins have taken place. My problem how is getting this foreign key value to the subquery. My original thought was to change the SQL code using some Replace Statements that would add the appropriate WHERE conditions, but due to the large amount of text in the query, I'm not eager to copy all those lines and fix quotes, etc. into a VBA variable.

I have seen code that allows VBA to pass a parameter value to a query, but not if I'm calling the query as the record source for a report. Is there a way to do that? Or is there a better method?

[zmbd]

Just a starting point, if you are using ACC2010 or newer there is the tempvars collection that you could use to pass the parameter.

As for a more on-point answer, I'll have to think thru what's happening when you send the data thru.

There is the on_load event in the report....

-z

[jforbes]

An option would be to use the existing Query Definition and do the parameter replacements yourself. Probably not the best solution, but it does have the advantage of maintaining the Query through the QBE Editor.

Something like this to do the substitution and set the report source:

    Dim oQueryDef As DAO.QueryDef
    Dim sSQL As String
 
    Set oQueryDef = CurrentDb.QueryDefs("ParameterQuery")
 
    sSQL = oQueryDef.SQL
    sSQL = Replace(sSQL, "[Parameter1]", "ValueToUse")
 
    Me.RecordSource = sSQL
 
    oQueryDef.Close
 
    Set oQueryDef = Nothing

If you were to use a Template Query Definition for the basis of the Query and then use a second query that is used as the Report RecordSource. You could then Edit the Template QueryDef through the QBE Editor and the use some code like the following to update the QueryDef that the Report would use:

    Dim oQueryDefTemplate As DAO.QueryDef
    Dim oQueryDef As DAO.QueryDef
    Dim sSQL As String
 
    Set oQueryDefTemplate = CurrentDb.QueryDefs("ReportQueryTemplate")
    Set oQueryDef = CurrentDb.QueryDefs("ReportQuery")
 
    sSQL = oQueryDefTemplate.SQL
    sSQL = Replace(sSQL, "[Parameter1]", "ValueToUse")
    sSQL = Replace(sSQL, "[Parameter2]", "ValueToUse")
    sSQL = Replace(sSQL, "[Parameter3]", "ValueToUse")
    oQueryDef.SQL = sSQL
 
    oQueryDefTemplate.Close
    oQueryDef.Close
 
    Set oQueryDefTemplate = Nothing
    Set oQueryDef = Nothing

[mbizup]

The Tempvars collection, mentioned by zmbd would be the easiest/most straight forward thing for this, I think. They are similar to global variables in a sense, but don't lose values when errors are encountered like globals do. They can be used in property sheets, forms/reports, queries, code... See the following for an excellent explanation:
https://accessexperts.com/blog/2010/...2007-and-2010/

Set them in code prior to opening the report (YourTempVarName can be any name you choose, but avoid spaces, special characters, etc):

TempVars.add "YourTempVarName", YourTempVarValue

And then use them in your query, subquery, report filter property or anywhere else:

WHERE YourField = TempVars!YourTempVarName

Alternate syntax:

WHERE YourField = TempVars("YourTempVarName")

[Seth Schrock]

I think in this case I will use JForbes' idea. In looking over each time my report is called, there is one instance where I'm pulling a group of IDs, so I'm having to use the In() criteria and not the equals, so I don't think that I can use the TempVar method as that doesn't let me change from = 5585 to In(5585, 5600, 5616). Thank-you to everyone for your suggestions.

[zmbd]

but can you not use
IN(tempvars("one"),tempvars("two"),tempvars("three "))

Now I have not tried this myself.... I've really just started playing with the tempvars object; however, it has proven very useful.