Microsoft Access - Need to use VBA to change parameters in a subquery..........

Asked By Heidi Boucher on 14-May-13 03:36 PM
Thanks for reading my post!

I have a 4 subquery series built up, all left joins so all invoices from tblinvoices are returned-
qryMR1/customer invoices
qryMR2/customer invoices with payments
qryMR3/customer invoices with payments that have been purged
then
qryMR4/a sums query by customer = customer number and 5 sum fields

qryMR4 is record source for a subreport
The subreport is used in different main reports that have date, age, value criteria which I have written with VBA querydefs.

I need my subreport to match the main report querydefs, except, since it is a sum query, the field I need in my where clause is in QryMR3

How can I write VBA querydef to execute qryMR3 where clause so that it is the resulting qryMR4 I need when the main report opens???

Any feedback is greatly appreciated!
Bill B replied to Heidi Boucher on 14-Jun-13 05:34 AM
You could do each step with a select into query where you dump the results into a table and then query that instead.
One benefit is that any complex work will not get repeated and it will run much faster.

e.g.
INSERT INTO ReportTable1 SELECT whatever FROM whereverTableOrQuery WHERE something=something
ORDER BY whatever

One drawback is that it will ask you if you want to overwrite. I think you can silence that in code...
IF you're doing VBA, then I think there was an option for the DoCmd to not ask.

Or you could add a reference to ADO instead of DAO to get your report work written to a table
(menu, tools, references, add, find the ADO 2.8 or whatever you have)

dim sSQL as string
sSQL="INSERT INTO ReportTable1 SELECT whatever FROM whereverTableOrQuery WHERE something=something
ORDER BY whatever"

CurrentDb.Execute sSQL, dbFailOnError



What you are doing seems like it could be done much simpler that way.


http://support.microsoft.com/kb/147739

http://www.blueclaw-db.com/access_createquerydef.htm