Pass parameter to report (1 Viewer)

[Danny]

Greetings,

I created a blank form ‘frm_CASES_All’ and added a button to open my report ‘rpt_CASES_All ‘ which is based on my query ‘qry_CASES_All’

On the form I used the following for my button to open the report
DoCmd.OpenReport " rpt_CASES_All ", acViewPreview

However, my goal is to use the form so that users can select parameter value from a dropdown, dates from calendar and/or manually enter desired date etc. and click ‘Run Report’ to pass the parameter value to the report.

When users click the ‘Run Report’ button on the form, I would like selection made on the form to reflect on the report

I tried the following in my query, but didn’t give me what I expected.

[Forms]![frm_CASES_All]![cboStatus]
[Forms]![frm_CASES_All]![cboAgency]
[Forms]![frm_CASES_All]![txtCloseDT]
[Forms]![frm_CASES_All]![txtAssignDT]

Any suggestions?

Regards,

 

[Ranman256]

If you have all these possible filters, then you'll need some vb to assemble the Where statement....

If not IsNull(cboState) then sWhere = " And [state]='" & cboState & "'"
If not IsNull(cboClient) then sWhere = sWhere & " And [ClientID]=" & cboClient
   'Remove 1st AND
If sWhere<> "" then sWhere = mid(sWhere, 5)

DoCmd.OpenReport " rpt_CASES_All ", acViewPreview,,sWhere

 

[Danny]

Thanks, is vba code the only option here?

can you please explain how the code works, whre to insert it etc.?

TIA,