Using VBA to automate a reporting process

[Porr via AccessMonster.com]

Hello, thanks in advance for your help.

I have seven sub-queries that feed into one master query. With my current
process I manually alter the criteria in each of the sub-queries and then run
the master query. Once that query runs I copy and paste the results into an
Excel spreadsheet. I "loop" this process 10 times for different sets of
criteria.

I would like to automate this process, but am having trouble writing code to
set the criteria in the sub-queries. Could anyone please share some sample
code on how I can set the criteria with a VBA module? As of right now I
would be happy to hard code the criteria.

Thanks

 

[PieterLinden via AccessMonster.com]

Hello, thanks in advance for your help.

I have seven sub-queries that feed into one master query. With my current
process I manually alter the criteria in each of the sub-queries and then run
the master query. Once that query runs I copy and paste the results into an
Excel spreadsheet. I "loop" this process 10 times for different sets of
criteria.

I would like to automate this process, but am having trouble writing code to
set the criteria in the sub-queries. Could anyone please share some sample
code on how I can set the criteria with a VBA module? As of right now I
would be happy to hard code the criteria.

Thanks

How extensive are your filters? Could you post a couple of examples of
varying complexity? (It's just hard to come up with solution without seeing
how complex this is.) ONE way to do it would be to create the base query
with all the joins but no filters and no order by clause and then modify that
on the fly. (Consider how you would filter a report on the fly - basically
pass a filter at runtime).

Say the source for your final query is just
SELECT ....
FROM .... JOIN ....
WHERE....
ORDER BY

essentially what you want to do is leave the WHERE and ORDER BY clauses out
of this query and then append them later. Then you could easily get all of
this from controls on your form.

If your final query always has the same columns, you could use a temporary
table and append all the data you need to and then use TransferSpreadsheet to
write the data to Excel...

HTH,
Pieter

 

[Porr via AccessMonster.com]

The criteria is relatively simple for three fields, L2_ID, L3_ID, L5_ID. For
some of the queries I will only use one or two of the fields, while I leave
the otehr blank. I don't know if this matters or not but for almost all of
my criteria I either us the "OR" operator or the IN("X","Y","Z") function.

Hello, thanks in advance for your help.

[quoted text clipped - 10 lines]

Thanks

How extensive are your filters? Could you post a couple of examples of
varying complexity? (It's just hard to come up with solution without seeing
how complex this is.) ONE way to do it would be to create the base query
with all the joins but no filters and no order by clause and then modify that
on the fly. (Consider how you would filter a report on the fly - basically
pass a filter at runtime).

Say the source for your final query is just
SELECT ....
FROM .... JOIN ....
WHERE....
ORDER BY

essentially what you want to do is leave the WHERE and ORDER BY clauses out
of this query and then append them later. Then you could easily get all of
this from controls on your form.

If your final query always has the same columns, you could use a temporary
table and append all the data you need to and then use TransferSpreadsheet to
write the data to Excel...

HTH,
Pieter

 

[ryguy7272]

So you just want to automate the process of running queries? This is what I
use:

Option Compare Database

'------------------------------------------------------------
' Update_Queries
'------------------------------------------------------------
Function UpdateQueries()
On Error GoTo Update_Queries_Err

DoCmd.SetWarnings False


'DoCmd.OpenQuery "NameOfQuery1", acViewNormal, acEdit
DoCmd.OpenQuery " NameOfQuery2", acViewNormal, acEdit
DoCmd.OpenQuery " NameOfQuery3", acViewNormal, acEdit
DoCmd.Close acQuery, " NameOfQuery1"
DoCmd.Close acQuery, " NameOfQuery2"
DoCmd.Close acQuery, " NameOfQuery3"
Update_Queries_Exit:
Exit Function

Update_Queries_Err:
MsgBox Error$
Resume Update_Queries_Exit

End Function


If you want to learn how to pass parameters into your queries, take a look
at this:
http://www.fontstuff.com/access/acctut17.htm
http://www.fontstuff.com/access/acctut17a.htm

At the bottom of the second page you can download a sample DB:
'AccessAndSQL4_2000.zip'

This is similar, with a slight twist on the last concept (ListBoxes instead
of ComboBoxes):
http://www.fontstuff.com/access/acctut18.htm