Setting the Sourceobject for a subreport at runtime

[Dennis Snelgrove]

I seem to have developed a mental block regarding the proper code to set a
Sourceobject for a subreport container at runtime. What is the proper syntax
for a report named "rptMain", subreport container "srptChild", and a
subreport named "srptDetail"?

I can do this with a subform with no problems, but not with a subreport.

Thanks... <hanging my head in shame>

 

[Marshall Barton]

I seem to have developed a mental block regarding the proper code to set a
Sourceobject for a subreport container at runtime. What is the proper syntax
for a report named "rptMain", subreport container "srptChild", and a
subreport named "srptDetail"?

I can do this with a subform with no problems, but not with a subreport.

Thanks... <hanging my head in shame>

No shame with this topic, it's a tricky subject.

Access reports do not permit you to change lots of things
once the report starts formatting. This means you can only
set this kind of property in a report's Open event.

It's even more complicated in subreports since several of
this kind of property can only be set the first time a
subreport's Open event fires.

You didn't explain what you're trying to do, so I don't know
if you can accomplish anything with the above information or
not.

 

[Dennis Snelgrove]

Okay, thanks for the help.

Essentially, I've got a report that is a Service Request form, and the main
body is a shell where the contents vary depending on the value of one field.
This field states what department the work was done for, and the main body's
fields change depending on the department. I decided to create a number of
subreports that would be the main body of the Service Request form for the
different departments, and create an unbound subreport container and have
the Report set the subreport to the appropriate subform to give me my
appropriate Service Request layout.

I just can't figure out the syntax to have the report set the container at
runtime.

Does this help make my problem clearer?

 

[Marshall Barton]

Essentially, I've got a report that is a Service Request form, and the main
body is a shell where the contents vary depending on the value of one field.
This field states what department the work was done for, and the main body's
fields change depending on the department. I decided to create a number of
subreports that would be the main body of the Service Request form for the
different departments, and create an unbound subreport container and have
the Report set the subreport to the appropriate subform to give me my
appropriate Service Request layout.

I just can't figure out the syntax to have the report set the container at
runtime.

Does this help make my problem clearer?

Yes, that helps. Unfortunatly, according to the reasons I
explained in my previous reply, you can't change the
subreport's SourceObject after the the main report's Open
event, so you'll have to come up with a different way.

I can think of two approaches. One is to place all the
subreport on the main report, all on on top of each other.
Then the main report determines which one to make visible
and the others invisible. This might be inefficient, but in
many cases is pretty straightforward.

The other way can get complicated and is certainly tedious
to code. Here, you would use one subreport and use code to
make columns visible or not depending on the dept field in
the main report. You'll probably have to set the text boxes
Left, Width, etc properties as well. If the subreports have
different Sorting and Grouping settings, then this approach
will not work (for the same reasons I cited before).

 

[Dennis Snelgrove]

Now, I know I'm picking nits here, but you're stating that I can't change
the subreport after the main report's Open Event has been run. Briefly, I've
written a subroutine that accepts one or more Request numbers as a string
(e.g. "12000 12002 12005"), and will run each Service Request separately.
It's a guarantee that each Request will only be one page long. The
subroutine calls the Request shell report and passes along the Request
number as an OpenArg. I've got the Report's Open Event doing the work of
finding out what department the Request is for, and trying to set the
subform's SourceObject. This is the point I'm at now. At this point, what
I'm trying to do is not going to work?

Sorry to be picky, but I was thinking that I was following the requirements
of when a subreport could be set. Maybe I'm not, after all...

Dennis

 

[Marshall Barton]

Now, I know I'm picking nits here, but you're stating that I can't change
the subreport after the main report's Open Event has been run. Briefly, I've
written a subroutine that accepts one or more Request numbers as a string
(e.g. "12000 12002 12005"), and will run each Service Request separately.
It's a guarantee that each Request will only be one page long. The
subroutine calls the Request shell report and passes along the Request
number as an OpenArg. I've got the Report's Open Event doing the work of
finding out what department the Request is for, and trying to set the
subform's SourceObject. This is the point I'm at now. At this point, what
I'm trying to do is not going to work?

That sounds like you can get by with setting the subreport
control's SourceObject in the main report's open event. The
situation I was trying to explain won't work is where you
want to change the SourceObject to a different subreport for
each detail/group in the main report.

Did you try this? If it didn't work, what went wrong?

 

[Dennis Snelgrove]

I did try this, but I'm not getting the correct syntax to reference the
subreport's Sourceobject properly. I've tried the following with no luck.
srptDetail is the subreport container, and rs![ReportFormName] is a single
record that retrieves the appropriate report name from a table of
departments and their matching subreport names.

srptDetail.SourceObject = rs![ReportFormName]
Me.srptDetail.Report.SourceObject = rs![ReportFormName]
srptDetail.Report.RecordSource = rs![ReportFormName]
srptDetail.Form.RecordSource=rs![ReportFormName]

I think I've got the right general idea, but I've got the feeling that I'm
speaking VBA's syntax very goodly not. ;-)

 

[Marshall Barton]

I did try this, but I'm not getting the correct syntax to reference the
subreport's Sourceobject properly. I've tried the following with no luck.
srptDetail is the subreport container, and rs![ReportFormName] is a single
record that retrieves the appropriate report name from a table of
departments and their matching subreport names.

srptDetail.SourceObject = rs![ReportFormName]

This one is the right syntax. Did you double check that
srptDetail is the name of the subreport control?

Did you place a break point in the code to see what the
value of rs![ReportFormName] is at runtime? Are you sure
it's the correct name of a report object?

Please explain the result of that attempt with a little more
information than just a "no luck" ;-)

 

[Dennis Snelgrove]

Good morning...

Yes, I have double checked the name of the subreport control, and it is
srptDetail. I'd already tried the breakpoiont idea, and the record value
returned is exactly what I need. The error I'm getting is as follows:

Run-time error 3011:

The Microsoft Jet database engine could not find the object
'~sq_drptRequestShell~sq_dsrptDetail'.

I understand that this means it can't find the subreport container itself,
yet it shows up in the Object browser, and I get the object's name showing
up when I hit Ctrl-J during coding. I've backed up and done a complete
decompile and recompile just in case there was any corruption, and the
problem still persists.

What are your thoughts at this point?

 

[Marshall Barton]

Yes, I have double checked the name of the subreport control, and it is
srptDetail. I'd already tried the breakpoiont idea, and the record value
returned is exactly what I need. The error I'm getting is as follows:

Run-time error 3011:

The Microsoft Jet database engine could not find the object
'~sq_drptRequestShell~sq_dsrptDetail'.

I understand that this means it can't find the subreport container itself,
yet it shows up in the Object browser, and I get the object's name showing
up when I hit Ctrl-J during coding. I've backed up and done a complete
decompile and recompile just in case there was any corruption, and the
problem still persists.

Now, that is an interesting(?) error message!?

I don't think that it's complaining about not finding your
report object. It looks a lot more like it's looking for a
internally saved query. Let's focus our attention on the
two report's RecordSource. I have no idea how it could
happen, but I think the error message might indicate that
one or the other reports has an SQL statement in its record
source, which Access saved under that funky name, but now it
can't be found. At this point, I am inclined to lean
towards corruption and I suggest that you do a
Compact/Repair and, if that doesn't help, import everything
into a new blank mdb.

One other off the wall thing to try is shaking it until
something loose falls out ;-)
What I mean is try retyping the record source query to see
if that will force Access to resave it under another name
and maybe it will be able to keep track of it this time???

Keeping my fingers crossed,