CODEWITHSUNDEEP
sqlvbams-access
David Cady
David Cady

Reputation: 61

How do I set parameters for an embedded query on an Access report from an Access form?

I have an existing Access report that runs off a Query. In the existing report I have the parameters set with brackets; [CSRName], [StartDate], and [EndDate]. I have those parameters being filled form a form using the VBA below;

All of this works just fine. My problem is this; I have this query counting the total number of errors a CSR makes. I can easily display in the report the total number of errors. What I'd like to do is have another textbox on the report that shows the total number of completed items by the CSR. In order to do this I need to embed the Control Source for the textbox with another query. I have that query written and it works outside the form. What I need to do though is pass the same parameters that I am using to generate the report into this embedded query on the report. I can't figure out how to do that. I can pass the parameters to the report itself, and it works fine, but trying to do the same won't pass the parameters to the embedded query in the report. It keeps popping up an input box and asking me to give it the variables.

I assumed I could treat the embedded query the same that I could the query the report is based off, so I used the same process to try to pass the parameters to it as well, but it did not work.

Private Sub RunQuery_Click()

    DoCmd.SetParameter "CSRName", Me.CSRNameCB
    DoCmd.SetParameter "StartDate", "#" & Me.StartDate & "#"
    DoCmd.SetParameter "EndDate", "#" & Me.EndDate & "#"

    DoCmd.OpenReport "rpt_CSRErrorTracking", acViewPreview

End Sub

What I need is the method to pass the parameters from the form to both the query that the report is based off, and the query embedded in the report itself.

Upvotes: 0

Views: 228

Answers (1)

Bullschmidt
Bullschmidt

Reputation: 47

How about having the queries that the report and report field are based on store the "link" to the form's fields something like this (query by form concept)...

In the query's criteria for an appropriate field:
= [Forms]![MyForm]![CSRNameCB]

And:
Between [Forms]![MyForm]![StartDate] And [Forms]![MyForm]![EndDate]

And then on the form that opens the report you don't have to set any parameters at all.

Upvotes: 1

Related Questions