Jim Lawrence
accessd at shaw.ca
Mon Nov 12 16:33:06 CST 2012
You are welcome of course. :-)
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Monday, November 12, 2012 11:16 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Question on Refreshing Data on Access
2007Report(withSub-Report)
Jim and Authur,
Thanks for your help and insights.
As a result of the ideas that you posted, I have built a small test
application that has a "selection form". This selection form gives the
users the ability to specify the criteria for about 10 fields. They can
enter as little or as much as they want. The selection criteria that is
entered is used by a VBA routine to build the SQL statement for the
report's underlying query. I have shown this little test application to
5 people and they seem to like it.
Originally, all report filters were on single fields on the reports
themselves (Access 2007 "Report View").
The users liked this approach, because they could see "all" the data on
a report and then trim down the amount of data on the report by using
one of the filters. I think that this approach was better than nothing,
but the new approach with the use of the "selection form" appears to be
much better.
Thanks again. Your ideas have prompted me to look at things from a
different angle and the end result is far superior to what I had
provided to our users previously.
Brad
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Sunday, November 11, 2012 10:40 AM
To: Access Developers discussion and problem solving
Subject: Re: [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/0311Unbound
Repo
> 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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com