By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Use IF-THEN to change query parameters for a report

P: 34
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"

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

End If]
Mar 17 '17 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 1,430
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

Expand|Select|Wrap|Line Numbers
  1.    Dim WhereClause as String
  3.    WhereClause = "PumpDate = #" & ProductionDate & "#"
  4.    WhereClause = WhereClause & " AND "SluryTankValue = " & Chr$(34) & "Line 1" & Chr$(34)
  6.    DoCmd.OpenReport stReport, acViewPreview, , WhereClause 
Mar 19 '17 #2

P: 34
Would this work where I have to print the same report multiple times with different criteria based on the selections of the form?
Mar 20 '17 #3

Expert 100+
P: 1,430
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

Mar 20 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.