Using list box on form to limit a report

[Andy83]

Hello,

This is my first big project with Access that wasn't just linking some fields in tables to create a query so please stick with me if I have a lot of questions. FYI I'm using Windows 7 with Access 2013

I'm trying to generate a report that identifies the Top 5 ID's based off labor/cost/order and some other fields. I have individual queries that successfully build this, with the exception of some duplicate issues I'll deal with later. I then have reports associated with each category (labor/cost/order) a user may want to list the Top 5 ID's for.

I've got a form that lets a user enter their code to run the queries specifically on their stuff. For example, if someone enters "235" in only ID's associated with code 235 shows up. I used a report called "total" and copied all individual reports from labor/cost/order into it as a sub report. When entering the user code and clicking the button on my form to generate the report, it shows the report with everything as I would expect.

To go one step further, however. I have a listbox on the form with:


Labor
Cost
Order
etc.

Any ideas on a way that I can assign the associated sub report to be displayed on the total report only if selected in this listbox? I hope I explained myself well enough without rambling on.

Thanks in advanced!

[June7]

I use code in the report Detail section Format event to set visibility of controls. Example:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.lblOU.Visible = Not Me.ctrOU.Report.HasData
Me.lblCO.Visible = Not Me.ctrCO.Report.HasData
Me.lblRC.Visible = Not Me.ctrRC.Report.HasData
End Sub

My code displays labels with caption like "no Change Orders reported" if the subreport has no data. I do this because the report is set with a page break between each subreport. This means there would be a blank page if the subreport has no data.

You could have code that sets visibility based on value of listbox.

Me.ctrLabor.Visible = (Forms!formname!listboxname = "Labor")
Me.ctrCost.Visible = (Forms!formname!listboxname = "Cost")
Me.ctrOrder.Visible = (Forms!formname!listboxname = "Order")

Keep in mind that even if controls are not visible they still occupy space. Set their CanGrow and CanShrink properties to Yes, maybe also set Height to 0.

[Andy83]

Thanks for the idea. I put the following Sub on the Report that contains all the sub reports.

Private Sub FilterReport()


Me.Cost.Visible = (Forms!Parameter!ReportSelect = "Cost")
Me.Labor.Visible = (Forms!Parameter!ReportSelect = "Labor")
Me.WO.Visible = (Forms!Parameter!ReportSelect = "Total Work Orders")
Me.PM01.Visible = (Forms!Parameter!ReportSelect = "PM01 Only")
Me.PM02.Visible = (Forms!Parameter!ReportSelect = "PM02 Only")


End Sub

When I run the report from the Parameter form I get each sub report regardless of what's selected. Did I put the code in the wrong spot or mess up the syntax somehow? My subreport names are "cost, labor, WO, PM01, and PM02" and the values in quotes is what the options are on my list box. This is the first time I've tried VBA with access. I've done it plenty in excel but I'm in new waters here.

Thanks again,
Andy

[June7]

Your code is in a general Sub. Are you calling that sub from somewhere?

My code is in the main report Detail section Format event. See my example again. Suggest you try Detail_Format.

[Andy83]

Ah, I thought Detail_Format was just the name of your sub. I see the difference now and changed my sub to match, however it still doesn't update based on what's selected in the listbox.

[June7]

Format event only runs for Print Preview or direct to printer.

[Andy83]

Oh. I wasn't intending on printing the report, just displaying it. Any other options?

[June7]

Can display in Print Preview, otherwise, I have nothing for using a report.

Forms are intended for screen display.

[Andy83]

Thanks for sticking with me on this June.

I can live with print preview but when I run the report with the code below in it I get:

Compile Error: Method or data member not found

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


Me.ctrCost.Visible = (Forms!Parameter!ReportSelector = "Cost")
Me.ctrLabor.Visible = (Forms!Parameter!ReportSelector = "Labor")
Me.ctrWO.Visible = (Forms!Parameter!ReportSelector = "Total Work Orders")
Me.ctrPM01.Visible = (Forms!Parameter!ReportSelector = "PM01 Only")
Me.ctrPM02.Visible = (Forms!Parameter!ReportSelector = "PM02 Only")


End Sub

I'm guessing that means I'm calling a variable or function that I shouldn't be?

[June7]

Did you actually name the container controls with the ctr prefix? I don't know your db. Make sure all names used are correct.

Could try:
Me.ctrCost.Visible = (Parent.ReportSelector = "Cost")

[amrut]

Have you tried -
1. Set the sub-report's control source based on the listbox's selected item in report's Load event . Use one unbound subreport and change source on fly.
Me.SubReportControlName.SourceObject = "Report.SubReportName"
2. I do not know about the fields you have in different sub-reports. If you can add a field to the main report (ListBoxSelectedItem) and each of the subreports based on the listbox on form (ex IIF(ListBoxItem="Cost","Cost","") you can create the Master-Child relationship between the main and sub reports based on that field. This one is not tested, just a thought.

[Andy83]

I corrected the names and now get

Runtime error 94
"Invalid use of Null"

In the VBA window I can see the .Visible value remains true while the other side: Forms!Parameter!ReportSelector = "Labor" is equal to Null.

[June7]

Do you want to allow no selection in the combobox and to allow all subforms to display?

If Not IsNull(Parent.ReportSelector)
Me.ctrCost.Visible = (Parent.ReportSelector = "Cost")
...
End If