Run-Time error 2101

[Bill Stanton]

An aftermath of the "Sub-report problem" thread, I have not
been able to set the filter expression for the sub-report's
RecordSource. As you can see from the code, I attempted
to inherit the RecordSource and its filter properties from the
parent report. The three lines you see commented out DID
NOT work at all. Failing that, I put a global variable
"FltrDonSum" in a general module and set its value in the
OnOpen code of the main report. However, when I attempted
to execute the statement:
Me.Filter = "DOE = " & FltrDonSum
I get a Run-Time error 2101 "The setting you entered isn't
valid for this property". Yet, debug shows the filter to be
exactly what it should be.

What am I missing here?

Thanks,
Bill


Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
Static Initialized As Boolean

If Not Initialized Then
'==================================
' Inherit RecordSource and filter from parent report
'==================================
' Me.RecordSource = Me.Parent.RecordSource
' Me.Filter = Me.Parent.Filter
' Me.FilterOn = True

Initialized = True 'Only run this code once

Me.Filter = "DOE = " & FltrDonSum
End If

End Sub

 

[Kelvin]

Bill,

You are trying to set the record source in the open event. During the open
event, the recordsource is not yet available so you can't set it. Also, a
subreport is opened and loaded with data before the main report so by
putting your code in the on open event of the subreport, the parent does not
have any data yet.

Try changing your logic to have the main form change the record source of
the subform.

Kelvin

 

[Bill Stanton]

Kelvin,
I tried inserting the following code in the OnOpen event
of the main report and got a Run-time error 438, "Object
doesn't support this property or method". (1st of the four
statements caused the error.) I believed that the code
was what you were suggesting.

Me.Child22.RecordSource = Me.RecordSource
Me.Child22.Filter = Me.Filter
Me.Child22.FilterOn = True
Me.Child22.Requery

I did notice that when I was entering the code that "Child22"
came up in the object/property list one gets when the VBA
editor detects the "Me." but neither the RecordSource,
Filter or FilterOn were included in the list.

I'm still not clear on the sequence in which things occur when
a report and its sub-reports open. I.e., what's available and
when?

Bill

 

[Kelvin]

The OnOpen event is basically for opening the form. At this point there is
no data. Try the OnLoad event instead.

Kelvin

 

[Bill Stanton]

Kelvin,
To my knowledge, there isn't any OnLoad for reports,
only for forms... at least in A2k. Are we on two "different
pages" here?
Bill

 

[Kelvin]

Sorry for that. Forgot about it being a report. Try:

Me.Child22.Form.RecordSource = Me.RecordSource

When refering to a subform from the main form, you need to use the .Form
property before you can access the objects in the subform.

Kelvin

 

[Kelvin]

I meant .Report not .Form. It's late and I need to go home.

Kelvin

 

[Bill Stanton]

RATS! "Me.Child22.Report.RecordSource = Me.RecordSource"
fails as well.
Bill

 

[Kelvin]

OK. I was able to recreate the problem, however I get a different error
message number. For some reason Access won't allow me to change the
recordsource while the report is open. This must have been something I
forgot about. Sorry for wasting your time with that. You might want to
repost this question and get a better answer but here is what I found.

To change the recordsource of a report, it has to be in design view. I was
able to finally accomplish what you want by first opening the report in
design view, changeing the recordsource, closing the report, then reopening
the report. Replace the code that calls this report with the following.

DoCmd.SetWarnings False
DoCmd.OpenReport "rptTest", acViewDesign
Reports!rptTest!rptTest2.Report.RecordSource = Reports!rptTest.RecordSource
DoCmd.Close
DoCmd.SetWarnings True
DoCmd.OpenReport "rptTest", acViewPreview

Kelvin

 

[Bill Stanton]

Kelvin,
Given that the RecordSource issue is resolved, do the
same restrictions apply to the setting of the filter? With
the RecordSource set, I tried to set the filter:

Me.Filter = "DOE = " & FltrDonSum

where FltrDonSum is a global variable set by the main
report, but I get 2101 error on that statement. It would
seem un-reasonable to me to not be able to set a filter.
The main report's open code sets the filter without any
problem, it's only in the sub-report that the 2101 occurs.

Bill

 

[Kelvin]

The same would go for any properties of the subreport.

Kelvin

 

[Bill Stanton]

Kelvin,
The main report is launched from the Switchboard manager.
If the sub-report isn't open when the OnOpen event of the
main report runs, I can put the code there. Otherwise, I'll
have to put a general module between the main report and
the Switchboard... what do you suggest?
Bill

 

[Kelvin]

I would create a new switchboard option to run this code. If you are
calling from a switchboard I am ssuming that it is calling the same report
everytime, so why not just specify the record source and filters in the
reports themselves? Are you passing parameters through the switchbord form?
Also, why are you using a report/subreport if they are both based on the
same source? Understanding these things might find a better solution.

Kelvin

 

[Bill Stanton]

Kelvin,
Both the main and subreports use the same RecordSource.
The filter is not determined until run time. The reason I have
to use a sub-report is because the main groups one way and
the sub-report groups in an entirely different way, which IS NOT
a sub-group.

I tried to solve this problem by using REALLY simple functions
to accumulate summations, but was never able to find an event
in the main where I had access to the descrete records being
read and formatted by the main. If I could, I'd just collect a
running sum, much different than the main, in a Public variable
and put the results into a text-box in the report footer. It's been
several days, but I think I added invisible controls in the main
reports detail section to invoke the accumulation function, but
the values available at the point were already the group totals
reported in the main... I need to get record access before the
main does its grouping.

That help?

Bill

 

[Kelvin]

What I find easier is that when I need to have separate groups is that
instead of using 2 reports like you have it to actually have 3. The main
which is just a dummy that will have a record source of only the criteria
field. Then my 2 other reports are subreports to this main, linked by the
criteria field. Then setting the criteria for the main will carry through
to the subs.

Kelvin

 

[Bill Stanton]

Ooops! I clicked on wrong reply option, so the first reply went to klu.

Kelvin,
How is it then, that when I set the filter for the main
that the resulting Recordsource (same set of selected
records from the query) doesn't carry forward to the
sub-report?
Bill

 

[Bill Stanton]

Kelvin,
I wrote a module that queries the user for the criteria that
is to be used in setting the filter for both the main and sub-
reports. I open the sub-report and set its filter and pass
the filter value to the OnOpen of the main report via a
Public variable. Below is an excerpt from that module.
Everything works okay except that the sub-report "Design
View" flashes before the user as the filter is being set.
Is there some way to suppress that?

DoCmd.SetWarnings False
DoCmd.OpenReport "FundsRecentSundaySubRpt", acViewDesign
Reports!FundsRecentSundaySubRpt.Report.Filter = "DOE = " & FltrDonSum
DoCmd.Close
DoCmd.SetWarnings True

DoCmd.OpenReport "FundsRecentSunday", acViewPreview
End Sub
Bill

 

[Kelvin]

Glad things are working. You can minimize the report after you open it.
Remember, this will minimize future windows too so put the restore command
after the main report opens.

DoCmd.OpenReport "FundsRecentSundaySubRpt", acViewDesign
DoCmd.Minimize

DoCmd.OpenReport "FundsRecentSunday", acViewPreview
DoCmd.Restore

To answer you other question, how are the main and sub-reports linked. If
the sub-report is linked using the field that is the criteria, then
filtering the main report should limit what is shown for the sub-report. Is
the record source for the reports, a table or a query. If it is a query it
would probably have been easier to set the filter in the query instead of
the report.

Kelvin

 

[Bill Stanton]

Kelvin,
Both the report and sub-report are based on a Union Select
query. Both the main and sub-report are filtered on the field
DOE, e.g., "DOE = value". (same value used for each incident
of the report.)

In my view, it's really straight forward, which is why I was so
surprised to learn that settnig the filter in the main didn't carry
forward to the sub-report.

If I understand the SQL syntax in the Union query, the Where
clause would have to be added for each of the three sections
in the querey, e.g., "Where DOE = DOEvalue" and the
current value for DOE substituted in each of the three occurrances
of the "DOEvalue".

Here's the querey:

SELECT ALL
[DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];

Bill

 

[Bill Stanton]

Kelvin,
I think it would be great if I could simply change the
query. Can one do something like a "change all" in
the query? Referencing my post of a few minutes
ago, like: "change DOEvalue to " & value & " ALL"

If that or something like it is possible, I could invoke
it in the OnOpen code of the main, or is that too late
in the process?

It wouldn't break my heart to eliminate the general
"bridge" module and the Public variable used in the
current scheme... make for convoluted code that's
not intuitively obvious.

Bill