Printing Subreport data not linked to Main Report

[Brett Barry: Go Get Geek!]

Hello,

I have a main report with a Record Source, a DateToday table, that has
the current Month and Year. I have about 60 queries, each pulling
different data via ODBC, that I am creating subreports that go on the
main report. The problem is that when I run the report the data shows
on the screen but not the print preview. I am aware that the main
report is not linked to the subreports because of the Link Master,
Child Fields issue. Unfortunately, only some of the queries are based
on the DateToday table used by the main report, but only as parameters
(nothing from that table is displayed)

Is there another way, hopefully more efficient, of linking all of my
subreports to the main report. I've tried adding another table and
incorporating the common field, but that field I don't want to print
on the main report. Plus, some of the queries are blank but I have the
nulls showing up as 0.00 on the subreports (and main) via formatting.
I'm hoping I can just add some code that "tells' the main report to
print all of the subreports. Is this possible?

Thanks,
Brett

 

[Larry Linson]

Do you have just one Record Detail printing on the main Report, or do you
have a number of Records printing on the main Report? If the latter, how do
you determine which of the 60 queries you run for each of the Subreports on
different Records on the main Report? Does the main Report contain "real"
data, or does it just serve as a "canvas" for the Subreports?

If you only print one Record, and it really is only a "canvas", put a dummy
field/text box on the main Report, and put a corresponding one in each
query, then use that as the LinkMasteFields and LinkChildFields. For
example, a numeric field containing a 1 in the data for the main report (or
a calculated control containing a 1), and be sure there's a corresponding 1
in the results of the Query for each Form embedded in a Subform Control.
The Controls used for this purpose need not have their Visible property set
to Yes.

I don't have any idea why your data would show up in Preview, but not in
Print Preview (unless it's taking approximately forever to format and your
patience runs out before the formatting is finished). But, if it shows on
screen, and on the hard-copy print, it would seem you have a problem that
I'd find it easy to ignore.

Larry Linson
Microsoft Office Access MVP

 

[Brett Barry: Go Get Geek!]

I would like to use the main report as a canvas for many subreports. I
figured out that I don't necessary have to have the subreports linked
to the main report; however, I am currently having a formatting
nightmare. I have all of these queries and Access just seems way too
difficult for me to just have them all lined up in columns. While it
says that I can put multiple queries in the subreport wizard, this is
not true. I just wish I could drag and drop each one of my queries
onto the main report and have it auto-format into columns.
Unfortunately, I'm thinking that what I am trying to do is beyond the
scope of Access.

 

[Larry Linson]

I'm sorry, Brett, but I'm having some difficulty understanding what you want
to do and what difficulty you are experiencing. Could you clarify in a
little more detail "way too difficult for me to just have them all lined up
in columns"? I'm also not sure what you mean by "it says that I can put
multiple queries in the subreport wizard" -- a Subreport is a Control, and
can display either datasheet, or a Report embedded in the Subreport Control.

I am also at a loss to remember a situation in which I needed anywhere near
that many Subreports on a single Report... perhaps if you'd explain what you
have and what you are trying to accomplish, rather than how you thought you
would accomplish it, someone could make a useful suggestion.

Larry Linson
Microsoft Office Access MVP

 

[Brett Barry: Go Get Geek!]

Larry,

Thanks for your patience and determination to help me out. I guess I
was just venting my frustration. And when I slept on it, I realized
the next day that I was going about it all wrong. Thus, I'm going to
post here what I did so that others won't have to go through the same
mistake:

I was trying to create a subreport (unlinked) for each query. I solved
the problem by consolidating each column's data (8 rows) into one
query, adding the "control" table field that is used on the main
report, or "canvas", which was just a date function that I used under
the Report title. Then, I made that date (month) not visible and
organized all my rows of data in the subreport. I lined them up and
made them flush right (for numbers to line up in the column). I made
the subreport capable of shrink and put it on the main report. While
it took longer for the report to generate because of the consolidated
queries using ODBC and summed data, the end result was a much easier
to format report that looks better. It will also be easier to modify
in the future, if necessary.

Thanks again for helping me "think" through this.

Brett