Multiple snapshot outputs

[tedmillerx]

I've got a sales analysis database used for outputting several report snapshots. It makes a snapshot for each of 25 sales reps (the report is actually a combination of 12 subreports--fed by several hundred thousand records).

Right now I select each rep (Kathy W for example) from a drop down menu on a form, then fire off a macro to output the results into a file called Kathy_W.snp.

As you can imagine, after running this for 25 reps the better part of a day is used up. I'm a great form/report designer, but I'm not much of a programmer. Can somebody come up with some code so that I can hit one button and I can leave my computer for several hours to process all the sales rep snapshots without user involvement?

 

[nomus1]

I do something similar - maybe it will work for you.
Let me see if I can explain how I do it.

I have a button that launches the report.
The code behind the button first opens a sql statement where I select the reps names.
Then I loop through each rep name, opening the report with a filter where the rep name = name I am holding in loop.
Then I send the e-mail and loop to the next name.
My code looks like:

strsql = "SELECT DISTINCT open_wo.PM_Email FROM open_wo"

Set acctrep = CurrentDb.OpenRecordset(strsql)

If Not acctrep.BOF And Not acctrep.EOF Then
While Not acctrep.EOF
emailto = ""

If (IsNull(acctrep![PM_Email])) Or (acctrep![PM_Email] = "TBD&quot Then
msgbox "No Project Manager E-mail address available", vbCritical
acctrep.MoveNext
Else
emailto = acctrep![PM_Email]
where = "[PM_Email] IS NULL"
If Not IsNull(acctrep![PM_Email]) Then
where = "[PM_Email] = '" & acctrep![PM_Email] & "'"
End If

strdocname = "open_wo"
subject = "Missing Billing ???"
msg = "Please review this information"

DoCmd.OpenReport strdocname,acViewPreview,where, where

If Not IsNull(acctrep![ProjMgrEmail]) Then
msg = msg
End If

DoCmd.SendObject acSendReport, strdocname, "snapshotformat(*.snp)", emailto, , , subject, msg, False

DoCmd.close acReport, strdocname, acSaveNo

acctrep.MoveNext
End If
Wend
End If