How do I use code to reliably filter the info in multiple sub-report?

[ShovelingFishHeads]

I am programming a db that tracks the efficiencies of people who work in a warehouse. They perform any number of tasks multiple times per day but there are only 12 different tasks that they could be asked to perfrom.



I have written a report with the intention that it will serve as one subreport to show data for one of the tasks. One query provides the record set for the reports so I am using Me.Filter = "[ItemTypeID] = 1" with a second line that switches the filter on: something like: Me.FilterOn = Yes.

ItemType is what we are calling a task.

Once I made this first report, I tested it and found that it functioned perfectly. Then I copied it 12 times and edited the Me.Filter = "[ItemTypeID] = 1" so that each report filtered on each ItemTypeID number 1 - 12.

With the subreports copied, edited, and saved with their own unique names, I slammed each one into a "Main" report. When I try to run the Main report, I get:

RunTime error 2101: The setting you entered isn't valid for this property

going into debug mode showed me that the line:

Me.Filter = "[ItemTypeID] = 1"

is highlighted.

Why? This report worked when it was a stand alone report. Any ideas what is wrong?

Thanks everyone!

[June7]

Did you copy the report object or the subreport container control holding the report?

This is code (VBA or macro)? Why use code? Not sure it can work with a report anyway, I never tried. I apply filter criteria in the report RecordSource.

Do you really need 12 subreports? Could report Grouping & Sorting functionality accomplish what you need?

[ShovelingFishHeads]

I copied the first report that I had made once I saw that it worked perfectly for my needs. Copies were then edited in the code editor to set the filter for each report to filter for each ItemTypeID. Then I put the copies of each of the reports into a Main/Parent report.

Can't figure out why this doesn't work. Am I not setting this up exactly the way a programmer should be doing it? This is the way this is supposed to be done, correct????

I am using code because I read in the forums where good programmers were saying that this is how they do this kind of filtering. Seems to make perfect sense, and yet it doesn't work.

One thing I am about to do for testing purposes is to see if I can get one report to work as a subreport without having 11 other subreports there to make thing so much more complecated.

[June7]

I have used code to apply filter to form, but never for report. I know a filter can be applied to report with menu tools so seems should be doable with VBA for the main report, subreports is another issue.

If you want to provide db for analysis, follow instructions at bottom of my post.

[ShovelingFishHeads]

I tried deleting all but one subreport on the main form to see if it would work with one report only and the same problem persists. This is really crazy!

Please help

[June7]

Can't do anything unless you want to provide db for analysis.

[ShovelingFishHeads]

I've got some things I am trying to do to see if I can fix this; if I can't get it to work, I'll post the db here. Want to try some things first.

[ShovelingFishHeads]

I tried doing the filter opperation in design mode and that solved the problem. Using code for some reason didn't work with the report functioning as a sub-report; can't explain why that is.