Choose multiple options for a Report

[ShellyL]

Hi. I am trying to simplify the process for running some reports. Currently, the report runs off of a query with multiple parameter prompts. There are start/end dates. We also have four regions, so there are four "Or" statements under Region, and the user is prompted "Enter North to include North" then "Enter South to include South", etc.

From reading other posts, it sounds like the best thing to do is build a Form for the selection criteria. For the dates, I will have a "Start" and "End" date on the form. That sounds pretty straightforward.

But I'm not sure what to do about regions. Ideally, the user should be able to select which regions they want to include (maybe one, maybe more... maybe "all"). Since there's always a chance the number of regions or region names will change, I'd like this to be as flexible as possible.

How can I best represent the "Region" selection on a form? I'm I correct to assume both the Date and Region criteria can be on the same form?

I'm a novice - - and greatly appreciate your help!

 

[BSman]

Have fields on a form that correspond to the different fields the user can optionally select. Also have a "Print" button. When the user clicks the Print button, in VBA code you can build the appropriate filter string and then open the report using that filter string.

As an example:
Take one control: chkRegion1 (I'm calling it a check box)

The code for on click for the Print button:

Dim strWhere as string
strWhere = ""
If me.chkRegional = True then
strWhere = "[Region]='North'"
end if
docmd.OpenReport ("myReport",acViewNormal,,strWhere)


For multiple regions you just build on the filter string. You could even make the criteria something like this:

"[Region] IN ('North','West')"

 

[ShellyL]

Thanks. I can see how this will work, but need a little more help! (Like I said, I'm a novice).

When I build the form, I assume I have the Region table as the source. So how do I make all four regions appear on the form with check boxes alongside? If I add check boxes, what is the control source?

More background if needed: I have four regions as records on my "Region" table (field name = [Reg_Region]. The query for the report relates to this region table, and has [Reg_Region] as a field; a second report brings in another query which uses the alias [Sls_Region] for field name.

Thanks so much...

 

[randysmid]

Shelley,
I wrote an FAQ on how to use a multi-select list box and filter to send to a report. It is FAQ703-2657. It shows how to loop through the selected items, build a string that is then passed to the report as a filter.

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[ShellyL]

Thanks Randy. I like using the list box, and assumed that what I need to do is type my list of regions in the "row source" list box vs. linking it to my Region table, and set multi-select to "simple". If I do that, then I should be able to paste the code into the command. But I don't know how to "continue the loop" and finish out the code as you instruct. Also, when I try this as noted below, I get a bug on the line: Me![lstRegions].ItemData...

Can you help?

Dim strFilter As String
Dim varItem As Variant
For Each varItem In Me!lstRegions.ItemsSelected
strFilter = strFilter & "[Reg_Region] = '" &_
Me![lstRegions].ItemData (varItem) & "' OR "
Next
If strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
Else
MsgBox "You did not select any region(s)."
lstRegions.SetFocus
Exit Sub
End If
DoCmd.OpenReport "SurveyCalendar", acPreview, , strFilter
End Sub

 

[randysmid]

Hi,
I have a sample database (FREE) that includes this as a sample. If you can send me an email directly to rsmith@cta.org, I can send the database to you. It's about 2.2MB in zipped format, and includes a readme file with user id and password. The database also has a security login form, a homemade menu, and alot of utilities in the modules section. It also has a unique form technique where it is very easy for the user to jump to a specific record, based on two different list boxes (such as SSN AND FullName).

The code you showed me goes into the clicked event of a command button to run the report. There are two key techniques with the Regions. One is to have a table with them, or you can have a Value List. If the Regions aren't likely to change in the near future, then the Value List is a good choice. If the user wants to add and change these Region settings, then it is best to base the listbox on a query. Obviously, you will then need a form to allow user maintenance of the Regions. AND, whenever a Region is to be deleted, you will need code to search through all your database records to insure that something is not affected by it (yes, you can use Referential Integrity)

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[Poobear1929]

I was wondering if anyone could help me with a listbox problem. I am trying to write an event to a command button to open a report based on a query based on the user selection from a list box. I want the user to be able to select more than one item off the list box. Actually it is a date, dd/mm/yyyy. Here is my code, which I found from an old thread from last year. However at one point I get a compile error.

Private Sub RunReport_Click()


Dim Repto As String
Dim frm As Form, ctl As Control
Dim varitem As Variant
Dim str As String

Set frm = Forms!ind_fit_test_report
Repto = "fitness_individual"
str = "date" in (" ((this is the point where I get a prompt saying compile error expected: end of statement))
Set ctl = frm.Date
If ctl.ItemsSelected.Count = 0 Then
MsgBox "No date was selected," & (Chr(13)) & (Chr(13)) & "please select at least one date from list", vbExclamation, "selection Error"
Else
For Each varitem In ctl.ItemsSelected
str = str & ctl.ItemData(varitem) & ","
Next varitem
str = Left$(str, Len(str) - 2)
str = str & ")"
DoCmd.OpenReport Repto, acViewPreview, , str

End If
End Sub

Thank you

Poobear1929

 

[ShellyL]

FYI - As a non-programmer, I decided to give up on using vba and creating the filter string. Since the value options (my "regions") won't change often, I simply created a form with multiple Text Boxes. The text box Names are Reg_North, Reg_South, etc. The default value of each box is one of the regions ("North", "South", etc.)

The underlying query for the report has the criteria "[Query.Region]=Forms!RegionFrm!Reg_North) Or [Query.Region]=Forms!RegionFrm!Reg_South" etc.

On the Region Form, the user can simply delete or "clear-out" any regions they DON'T want for the report. Much easier than how I initially had them type in any regions they DID want to include. Then, the form has the command button to open the report.

I know hard-coding the values in isn't preferred, but it's working for me...

Thanks all for your assistance!