55467

Linking SubReports Without LinkChild/LinkMaster

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:

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

    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

    Answer1:

    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.

    Answer2:

    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.

Recommend

  • Z-Order of Forms in WinForms
  • Object variable or with block variable not set - Access 2013
  • How to close (unload) a form
  • Docmd.TransferText to update data
  • Auto-enable macro with digital cert?
  • Detecting data changes with Form events
  • C# crash when loading C++ dll
  • Remote Service as apk
  • adapt multiprocessing Pool to mpi4py
  • Making a basic web scrapper in Python with only built in libraries - Python newbie
  • bind checkbox with a bit column in a SQL Server linked table
  • VBA fails when Task Scheduler is set to “Run whether user logged on or not”
  • Is it possible to “shrink” a PdfPtable?
  • How to get the index of element in the List in c#
  • command line of process by name
  • Primefaces lazy datascroller calling load twice
  • Web.config system.webserver errors
  • Force show.bind execution
  • Thread safety of a fluent like class using clone() and non final fields
  • WPF - CanExecute dosn't fire when raising Commands from a UserControl
  • AJAX Html Editor Extender upload image appearing blank
  • d3 v4 drag and drop with TypeScript
  • JQuery Internet Explorer and ajaxstop
  • Bad request using file_get_contents for PUT request in PHP
  • dc-js disable selecting slices on click for pie chart
  • Disable Enter in editText android
  • Spring security and special characters
  • Opengl-es onTouchEvents problem or a draw problem? [closed]
  • How to redirect a user to a different server and include HTTP basic authentication credentials?
  • Can I make an Android app that runs a web view in Chrome 39?
  • 'TypeError' while using NSGA2 to solve Multi-objective prob. from pyopt-sparse in OpenMDAO
  • Is there a mandatory requirement to switch app.yaml?
  • retrieve vertices with no linked edge in arangodb
  • Hits per day in Google Big Query
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • LevelDB C iterator
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Easiest way to encapsulate a HTML5 webpage into an android app?
  • Python/Django TangoWithDjango Models and Databases