Setting query parameters from VBA for a Report

[dana1]

Hello Experts!

Does anyone know if there is a way to set the values of query parameters from VBA for a report's recordsource? (i.e., I want to set the values of the parameters and NOT have the user prompted for them).

Based on a Microsoft page (http://support.microsoft.com/kb/287437), I tried the following approach:

Private Sub Report_Open(Cancel As Integer)
 
    Dim sql As String
    Dim dbsSoftwareConfiguration As Database
    Dim qdf As QueryDef
    Dim rst As Recordset
    Dim stDocName As String
 
    Set dbsSoftwareConfiguration = CurrentDb
 
    'if rpt form is open and "include all sites" box is NOT checked
    If myUtil.FrmIsOpen("frmReportBuilder") And (Not Forms!frmReportBuilder.includeAllSitesCheckBox) Then
 
        sql = "PARAMETERS [pSite_ID] Long; SELECT * FROM qryObsoleteMediaBySite;"
        Set qdf = dbsSoftwareConfiguration.CreateQueryDef("tempQry", sql)
        qdf.Parameters![pSite_ID] = Forms!frmReportBuilder.obsoleteSiteMediaComboBox
 
        Set rst = qdf.OpenRecordset
        Me.RecordSource = qdf.Name
 
        'Me.titleTextBox = "Obsolete Media for " & _
            Forms!frmReportBuilder.obsoleteSiteMediaComboBox.Column(1)
 
    Else
 
        Me.RecordSource = "qryObsoleteMedia"
        'Me.titleTextBox = "Obsolete Media Across All Sites"
    End If
 
    Set qdf = Nothing
    Set rst = Nothing
    Set dbsSoftwareConfiguration = Nothing
End Sub
 

The problem is that setting the recordsource instead of the recordset appears to rerun the query, ignoring the parameter values passed in -- resulting in the standard prompt to the user. Setting the recordset directly is apparently not an option for reports (based on the Microsoft page linked to above). (Note: I have used this method and set the recordset property directly for forms, and it does work in that situation).

I've also tried using the following code to set the parameter directly in the query, but the user is still prompted for the parameter:

dbsSoftwareConfiguration.QueryDefs("qryObsoleteMediaBySite").Parameters("pSite_ID") = _
            Forms!frmReportBuilder.obsoleteSiteMediaComboBox
 

Because I am using the same saved queries in multiple places, I'd rather not bind the criteria in the query directly to the form. If I do that, I have to save a different version of the same query for each form or report I want to use it with.

Any ideas for how to programatically set the value of a parameter for a query that is the recordsource for a report?

Thanks in advance for any ideas!

Dana

[Denburt]

Looks like your taking an interesting approach, sorry I don't have time right now to test out the code you are using. I have one instance where I have to change the query to set my criteria so in the reports on close event I stipulate a clean up function that returns the query to it's original state and the criteria no longer exists (just an idea).

[dana1]

That makes sense.. So instead of trying to set the parameter value, I can just access the .sql property of the query def and replace the "parameter" part of the string with a reference to the appropriate control on form. That might actually work.

I'll play around with it this afternoon and let you know. Thanks!

[Denburt]

Your quite welcome glad we could help. Sounds like your on the right track. Let us know how it goes.

[dana1]

Your suggestion worked quite well. I wrote a little "find and replace" type of function, and just before I open the report I use it on the string in the .sql property of the saved query to replace any instances of my parameter ("[pSite_ID]") with a reference to the control on the form ("[Forms]![frmReportBuilder].[obsoleteSiteMediaComboBox]"). Then after I run the open report command, I use the "find and replace" function again to change the references to the control back to the parameter name.

This means that I can get rid of all my querydef stuff in the open event of the form-- I don't need it anymore.

Works like a champ!

I had it in my head that there had to be a way to pass parameter values to the query, so it didn't even occur to me to just change the sql statement :-) Thanks for the idea!

Dana

[Denburt]

Your quite welcome, if you need anything else give us a holler.