[AccessD] Question on Refreshing Data on Access 2007 Report (withSub-Report)

Thanks to Jim's reply, I had another thought, Brad. There's no real reason
why the queries for the header and footer cannot be combined into a single
query. I frequently assemble queries from other queries rather than tables.
To keep it brief, let's say you have query A, whose source in Pervasive,
and query B, whose source is Firebird. Then just create query C, whose
source is the joined queries A and B. Of course you will get multiple
occurrences of the values of the "parent" but so what? Just ignore them
when creating the subform.

I just whipped up a little tester combining data from Access and SQL 2012
and it worked. I created a simple report based on query C and called it
rptParent, then created another also based on query C and called it
rptChild. Finally I went into design mode and dropped rptChild onto
rptParent as a subform. I ran it, then requeried the parent and it worked
fine.

Perhaps that approach might work in your case. It can't hurt to give it a
shot.

HTH,
Arthur



On Sat, Nov 10, 2012 at 12:46 PM, Jim Lawrence <accessd at shaw.ca> wrote:

> Hi Brad:
>
> Have done a similar thing with two (multiple) data sources; MS SQL, MySQL,
> Dbase, Spreadsheet Text and Access for example.
>
> A dummy or empty table that sadisfies the report's field requirement is
> first designed. That will be used as a template.
>
> When the data is brought back from the two sources, the data is dumped in a
> couple of static recordsets and folded together into one recordset. In some
> cases a the process can be done via a union query and in others via a
> program. When that process is completed the report's record source property
> is refereshed (replaced in code) and the new report then repopulates. This
> process works great even when back to back reports are required.
>
> The following link is not exactly what you requested but it is an example
> of
> program bases report repopulations that works in a multi-user environment
> using a function that repopulates the report that is called from within the
> report itself.
>
>
> http://www.databaseadvisors.com/newsletters/newsletter112003/0311UnboundRepo
> rts.asp
>
> The code sample may help and be able to be adapted to your requirements.
>
> Jim
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
> Sent: Saturday, November 10, 2012 5:24 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Question on Refreshing Data on Access 2007 Report
> (withSub-Report)
>
> All,
>
> We have a report that is pulling data from two separate databases via ODBC
> (Pervasive and Firebird).
>
> The "parent" report is pulling data from the Pervasive database and a
> sub-report is pulling data from the Firebird database.  This is
> accomplished
> via two separate queries.  This works very nicely.
>
> Recently there has been a request to add a feature that would enable our
> Sales Staff and Customer Service Reps to specify data that would "filter"
> the information that they are shown on the report.
>
> To provide this feature, I have added several buttons to the report.  Each
> of these buttons opens up a little form which is used to obtain the string
> that the users would like to use for their filter.  When the button on the
> "Filter Form" is pushed, a VBA routine is invoked.  This VBA routine
> changes
> the query-defs of the two underlying queries.  If I look at the underlying
> queries, I can see that they are being changed correctly and they are
> returning the data correctly.
>
> The catch is that the data shown on the report is not automatically
> changed.
> I thought that invoking a "Requery" would refresh the data on the report
> after the data returned by the changed queries has been updated but it does
> not do so.
>
> The only way that I have been able to get this to work is to close and
> re-open the report after the two query defs have been changed by the VBA
> code.  This works, but I am curious if there is a better solution.
>
> Thanks,
> Brad
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



-- 
Arthur
Cell: 647.710.1314

Prediction is difficult, especially of the future.
  -- Niels Bohr