How do I use an OpenReport form to filter a report using fields in the query?

[jparker1954]

I have a query that shows a series of information that needs to be sorted for several report locations (not all locations get the same list of information).



I can currently select the LocA field in the query get only that series of data for LocA by putting "Yes" the query field criteria for the Loc A field. I can do the same for Loc B to get just that info, and so on. But that means either changing the query every time I need the report, or doing 5 separate queries where the Location field says "Yes" in the Location field I need to report on.

An open report form for the 5 locations I need that will modify the query in each case, to say "yes" in the appropriate location field to produce the location report want?

Can't be that difficult, but I'm stumped.

[June7]

Use VBA code to construct filter string and apply it in the WHERE CONDITION argument of OpenReport.

Can each record have only one location associated? If true, should be only one field, not 5.

[jparker1954]

Each record can apply to any one or all of the locations. Each record is identical, EXCEPT for the locations that use use or don't use it. When I select LocA for the report, I only want those products that show up with a "Yes" under LocA and so on. Again, I can do this manually in the query, but I want to do it with code when the report is generated. Unfortunately, I'm very not experienced with writing code and I need help coming up with one that will work.

This gives you an idea of what the table looks like that decides which products should show up in each location's report (there are 115 total products, however, and the list is growing)
Product Name | LocA | LocB | LocC| LocD | LocE |

Product 1 | Yes | Yes | No | Yes | No |
Product 2 | Yes | No | No | Yes | No |
Product 3 | No | No | No | No | No |
Product 4 | Yes | Yes | Yes |Yes | Yes |

[June7]

Would need a form to input choice. Think I would use an OptionGroup with radio buttons. OptionGroup control takes the value of the clicked radio button. Assign the buttons number values 1 - 5. Name the Option control something like optLoc. Then code like:

DoCmd.OpenReport "reportname", , , "Loc" & Choose(Me.optLoc, "A", "B", "C", "D", "E") & "=True"

[jparker1954]

OK, I've made a dialog box like the one below. The Group Box is OptLoc, each button is named as the label reads, with the option values set at 1,2,3, etc. How do I set up the Where Clause to choose to select the location based on this? I'm stumped regarding the "Loc" in your example. Again, each of the options below is a different field in the query. Any and all help is appreciated.

[June7]

Those are the actual field names? That complicates the code because the field names are not similar as shown in the first example.

Select Case Me.OptLoc
Case 1
strField = "Central Florida"
Case 2
strField = "New Jersey"
Case 3
strField = "North Carolina"
Case 4
strField = "North Florida"
Case 5
strField = "South Florida"
End Select

DoCmd.OpenReport "reportname", , , "[" & strField & "]=True"

[jparker1954]

OK, this is the whole code I have for the OpenReport Box. It's pretty cut and pasted from other ones I've done, so I apologize for that.

The message I'm getting now is that the Variable (strField) is not defined. How do I do that?

Option Compare Database ' Use database order for string comparisons.
Option Explicit ' Requires variables to be declared before they are used.

Sub PrintReports(PrintMode As Integer)
On Error GoTo Err_Preview_Click
' This procedure used in Preview_Click and Print_Click Sub procedures.
' Preview or print report selected in the ReportToPrint option group.
' Then close the Print Sales Reports Dialog form.


Select Case Me.OptLoc
Case 1
strField = "Central Florida"
Case 2
strField = "New Jersey"
Case 3
strField = "North Carolina"
Case 4
strField = "North Florida"
Case 5
strField = "South Florida"
End Select
DoCmd.OpenReport "AllChemInfo-Tomatoes", , , "[" & strField & "]=True"
End Select
DoCmd.Close acForm, "OpenReport"
Exit_Preview_Click:
Exit Sub
Err_Preview_Click:
Resume Exit_Preview_Click
End Sub
Private Sub Cancel_Click()
' This code created by Command Button Wizard.
On Error GoTo Err_Cancel_Click
' Close form.
DoCmd.Close

Exit_Cancel_Click:
Exit Sub

Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click
End Sub
Private Sub Preview_Click()
' Preview selected report. This procedure uses the PrintReports
' Sub procedure defined in (General) section of this module.

PrintReports acPreview
End Sub


Private Sub Print_Click()
' Print selected report. This procedure uses the PrintReports
' Sub procedure defined in (General) section of this module.
PrintReports acNormal

End Sub

[June7]

Since you have Option Explicit declared in the module header, requires all variables to be declared with a Dim statement. Can do this anytime after the Sub line before the first use of the variable.

Dim strField As String

[jparker1954]

Well DUH! I did that before you responded to me, but it actually worked when I put the correct REPORT NAME in the code! Thanks for all your help! This worked.

[jparker1954]

One last question. Say I want the name of the strField to appear in a text box on the report itself. How would I go about coding that to the correct Text box?

[June7]

One way is to use the OpenArgs argument of OpenReport:

DoCmd.OpenReport "AllChemInfo-Tomatoes", , , "[" & strField & "]=True", , strField

Then in a textbox on report:

=[OpenArgs]

[jparker1954]

PERFECT! Thanks again!