Linking SubReports Without LinkChild/LinkMaster

Question

I'm maintaining and occasionally modifying an Access 97 program that's still a crucial department tool for a very large US corporation.

A number of reports use a "totals" subreport that I cannot link using LinkChildFields \ LinkMasterFields. In each case, the main report can be filtered by numerous (or no) criteria via a "Reports Manager" form.

I've coped with this by using a generic function that opens any subreport in design view, and edits the .Filter property. Works 100% OK.

However, this prevents me from distributing the app as an .mde file, as Design view is unavailable in an mde.

I've tried every alternative I can think of:

• setting the subform filter during Open event to that of the Parent (error)
• using Docmd.ApplyFilter during Open event (does nothing at all in a subform)

Although this 'old' app suits the Department using it perfectly, their IT want to implement a 'big-picture solution', and I really don't want a competitor to have free access to a heck of a lot of complex business rules I've worked so hard on over the years.

Does anyone have any suggestions re the subform filtering, so I can use an mde?

MTIA

Answer 1

Why can't you link to the "totals" subreport using Link Child/Master? It should run off the same record source as the main report and aggregate over the records. In any case, if you can specify a filter criteria, you should be able to specify a domain aggregate criteria (dsum, dcount, dlookup etc) that returns the same values.

Dynamically editing the filter property in design view to make it work is a kludge. There is a reason that it difficult, not because the Access designers wanted to make it hard for you to embed subreports with dynamic criteria, but because it's a bad idea. Don't do it. There is something wrong with your report record source if you can't either join the subreport on record fields, or get rid of the subreport altogether and aggregate within the main report. You probably already know this, but you can aggregate (sum, count, etc) over the detail in a report in the report/page/group header/footer and give totals that way.

For example, if you were writing a report for a printable invoice, you could move everything above the line items into the report header, leave a line item as the report detail, and move everything below the line items into the report footer. Then you could do sum() over the detail fields to generate your subtotal then add tax, shipping etc. Another way to do this would be to use a subreport for the line items then try to calculate the totals externally, not as simple and way more fragile if the subreport changes.

Answer 2

Have you considered rewriting a query on which the subreport is based? The SQL string of a query is easy to change, and you will be able to make an mde using this method.