Loop through query criteria?

[HenryAnthony]

Hi,

I am looking for a FAQ or some general direction on the following:

I have a query from which I need to export many spreadsheets of data based on criteria stored in another table. Currently, I am using a combo box on a form as criteria but this is labor intensive. Also, I am familiar with some coding using DAO, or is it ADO? Any hints?

Best regards,

Henry

 

[Frink]

Hallo,

You could write a bit of code to loop through each entry in the criteria, exporting the spreadsheets as you go.
How do you do your exports now?

I think DAO is easier, but then that's 'cos I'm used to it.

- Frink

 

[HenryAnthony]

Hi Frink,

To export I have a form with a combo box. Query gets criteria from combo box. Command button exports Excel file named according to the criteria.

 

[Frink]

Hallo,

dim rstCriteria as DAO.Recordset
set rstCriteria = currentdb.OpenRecordset("SELECT strCriteria FROM tblCriteria;")
with rstCriteria
  do while not .EOF
    'Insert Export code here, refer to criteria as !strCriteria
    .movenext
  loop
  .close
end with
set rstCriteria=nothing

Might work,

- Frink

 

[HenryAnthony]

Hallo,

Thanks. Even to me - a novice with code, this looks like it will work. It will take some time for me to give it a try but I will post back.

Thank you sooooooo much!