I have a listbox on my form with 30 options, referring to clinical specialties, cancer, diabetes etc. These options come from tblSpecialties. I have a command button which:
So it’s a only a few clicks per option, but each report takes around two minutes to run, as it retrieves data from a remote server and the formatting is quite complex. So running 30 reports takes about an hour, most of which is just watching the egg-timer, waiting until it’s time to click.
So how would I automate this? I would be happy to save each PDF in the same folder each time, overwriting the one from last time. I am envisaging code something like below, but I don’t know enough VBA to get the syntax right.
I hope this pidgin code explains what I’m trying to do, but how should I actually do it?
- Turns warnings off
- Runs various make-table queries, each of which refer to that listbox
- “Prints” a report to my PDF printer, which brings up a dialog box asking for a filename. I always choose the exact text in the listbox.
- Turns warnings back on.
So it’s a only a few clicks per option, but each report takes around two minutes to run, as it retrieves data from a remote server and the formatting is quite complex. So running 30 reports takes about an hour, most of which is just watching the egg-timer, waiting until it’s time to click.
So how would I automate this? I would be happy to save each PDF in the same folder each time, overwriting the one from last time. I am envisaging code something like below, but I don’t know enough VBA to get the syntax right.
Code:
Dim S as Integer ‘ for the record number in the table
Dim F as text ‘ for the filename
For S = 1 to 30
S = [tblSpecialties].[SpecialtyNo]
‘Run the existing code
Save to C:\Documents and Settings\Desktop\Specialty Reports
F = Me.lstSpecialties.Value
Save As F
Next SI hope this pidgin code explains what I’m trying to do, but how should I actually do it?
Last edited:
