Biss said:
Rick thanks so much for your advice, so obvious that I didn't think of it.
How would you suggest automating this process as some week there are more
or less groups than the week before..
There are almost as many strategies for that as there are Access developers.
Suggesting one for someone else to use is mostly determined by their skill
level and specific requirements.
Are you using SendObject to send the Emails?
Are you familiar with writing VBA code?
One of the headaches with Access is that reports only allow an on-the-fly
filter when using the OpenReport method of DoCmd. You cannot easily apply
such a filter when using OutputTo or SendObject. Working around that is the
first thing to solve for something like this.
One of the simplest work-arounds is to exploit a behavior Access has which
is that processing a report that is already opened in preview mode (with a
filter applied) will cause that same filter to be applied to the new
process. So if you open a report with...
DoCmd.OpenReport "SalesReport",acViewPreview,,"SalesGroup = 'GroupName'"
....and then call SendObject on that same report, the report attached to your
Email will also be filtered on that same group name.
Presumably you would be pulling the value for the "GroupName" filter from
somewhere rather than hard-coding it. One way to do that would be to create
a Recordset in your code that would return all the group names that had
activity on the particular week being processed. If possible that same
Recordset could include the Email address to use for that group. You would
then loop through that Recordset processing each report.
(overly simplified pseudo-example)
dim db as Database
dim rs as DAO.Recordset
dim sql as String
Set db = CurrentDB
sql = "SQL that returns list of Group Names and Email addresses"
Set rs = db.OpenRecordset(sql,dbOpenSnapshot)
Do Until rs.EOF
DoCmd.OpenReport "SalesReport",acViewPreview,,"SalesGroup = '" &
rs!GroupName & "'"
DoCmd.SendObject acSendReport,"SalesReport",acFormatSNP,rs!EmailAddress
DoCmd.Close acReport,"SalesReport"
Loop
The above should be enough to get you started (or at least produce some more
questions). My overall advice is to map out all the steps that need to
happen for the entire process and then work out how to do each piece in
isolation. Stitching the steps together into a single process that does
everything is fairly simple once you have them all worked out.
