Arthur Fuller
fuller.artful at gmail.com
Mon Nov 12 16:10:48 CST 2012
Happy to be of service, Brad. A. On Mon, Nov 12, 2012 at 2:16 PM, Brad Marks <BradM at blackforestltd.com>wrote: > 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 > -- Arthur Cell: 647.710.1314 Prediction is difficult, especially of the future. -- Niels Bohr