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

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