Use IF-THEN to change query parameters for a report

[Pookaroo85]

I have a form with a series of check boxes and combo boxes to change report criteria. Based on the user's selection, I need 1 - 17 reports to print with different criteria from the same 5 queries. Each query has been made into a sub-report. I cannot seem to figure out how to get the scenarios to influence the query parameters. For sake of space, I have only included 2 queries worth below.

[Private Sub cmdPrint_Click()
Dim dbs As DAO.Database
Dim qdf1 As DAO.QueryDef
Dim qdf2 As DAO.QueryDef

Dim stReport As String
Set dbs = CurrentDb
Set qdf1 = dbs.QueryDefs("qrySlurryPumps_Pump")
Set qdf2 = dbs.QueryDefs("qrySlurryPumps_HandAdd")

stReport = "rptPumpingRecord"

If Me.Check_L1 = True And Me.Check_L1HAS = False And Me.Check_L1HAP = False And Me.Check_L1OTC = True Then
qdf1.Parameters("PumpDate").Value = Forms![frmPrint]![ProductionDate]
qdf1.Parameters("SlurryTank.Value").Value = "Line 1"
qdf1.Execute
qdf1.Close

qdf2.Parameters("PumpDate").Value = Forms![frmPrint]![ProductionDate]
qdf2.Parameters("Hand_Add.Value").Value = "0"
qdf2.Execute
qdf2.Close
DoCmd.OpenReport stReport, acViewPreview

End If]

[PhilOfWalton]

I suggest a totally different approach.

Use the DoCmd.OpenReport method and build the WHERE filter from the criteria found in your form.

Assuming the print button is on your form, you would have things like

   Dim WhereClause as String
 
   WhereClause = "PumpDate = #" & ProductionDate & "#"
   WhereClause = WhereClause & " AND "SluryTankValue = " & Chr$(34) & "Line 1" & Chr$(34)
 
   DoCmd.OpenReport stReport, acViewPreview, , WhereClause 
 

Phil

[Pookaroo85]

Would this work where I have to print the same report multiple times with different criteria based on the selections of the form?

[PhilOfWalton]

Yes, because you build different WHERE clauses depending on the information required in your reports. The report isn't "Created" until it has been supplied with the WHERE information.

It won't work if you need different grouping & sorting in your reports though

Phil