Automatically generating a series of reports

[Bobk]

I have a database containing supplier data, one record for each supplier for
each month the supplier delivered product. The data describes on time
delivery performance and product quality. At the end of each month a report
is generated for each supplier, graphically depicting their performance. The
report is generated by entering data through a form. The current month would
be selected from a drop down list of the availble months in the database.
Once the current month is selected, a list of all suppliers with activity in
that month is available in a combo box drop down list. I then select each
supplier in turn and a report is generated which is saved to be printed
later. I have to manually select each supplier on the list. What I want to do
is automate this task. I have the list of suppliers and just want to run each
one in sequence. Anyone have any suggestions on how to do this?

 

[Dale Fye]

Bob,

I assume that you are updating the combo with the list of suppliers in the
AfterUpdate event of the CurrentMonth combo box.

What I would do is add a command button to your form (cmd_Rpt_All) or
something like that. In the click event of that button, do something like
the following. You want strSQL to have the same select statement as your
cbo_Company combo box. If this is a query, just use the query name enclosed
in quotes. This also assumes that the bound column of cbo_Company is the
first column.

Private sub cmd_Rpt_All_Click

Dim rs as dao.recordset
Dim strSQL as string

strSQL = "SELECT ......"
set rs = currentdb.openrecordset (strSQL,,dbfailonerror)

While not rs.eof

me.cbo_Company = rs(0)

'this line is not needed if there is no code in the
cbo_Company_AfterUpdate event
Call cbo_Company_AfterUpdate

'assumes that in manual mode, there is another command
'button that you have to click
Call cmd_Rpt_Company_Click

rs.movenext

Wend

End Sub

Why are you saving the report to be printed later? You could print the
reports at the same time, or even email the report directly to the recipients
at that time. Several years ago (before Outlook security was enhanced), I
had an application that did this to over 300 organizations at the beginning
of each month, using the SendObjects method. Now that the Outlook security
patch exists, this is a little more difficult, but it can be bypassed (google
on Outlook +security +CDOMessage).

HTH
Dale

 

[Bobk]

Thanks for the help Dale. You understand what I am doing here. After I select
the supplier I hit a "Run" button which calls for the report.

I appreciate your suggestion to directly email the results. We are planning
on doing that. Currently we send out 150 reports each month so you can
appreciate how this will help. I will be working on implementing this code in
the next couple of days.