Macro to run report n times

  • Thread starter Thread starter nesster13
  • Start date Start date
N

nesster13

Dear all,

Thanks for all the help I have received from this board. Anyway here is
another obstacle I have. I have form that when click a button on it a
report will be generated. Now is there a why to create another button
that will run the report more than 1 time. What I mean is before I have
a certain month I want the report to generate (Ex: January). Now I
would like it to a couple of months at the same time (Ex: January,
February, and March). Is there a way to do something like this?

Thanks in advances
 
Nesster,

I would generally approach this from the point of view of the report
design, or the query that the report is based on, rather than macro
manipulation. I can't really be specific without knowing more detail.
But, for example, here's a concept that might be adaptable...

- on your form, put a couple of unbound textboxes to define the date
range for the report.
- reference these textboxes in the Criteria of the query that the
report is based on.
- design your report with a Month Header, such that each month's
results are printed on a separate page.
- run the report, you will gert a report for each month.
 
Thank you for your reply. The problem is that the original report was
designed to generate a report for a certain month only. I don't think
my supervisor would want me to go into the design of it and manipulate
all the queries and such. All he wanted was to have an alternate option
that will allow the user to view a couple of months in a row if choose
to. I think the only way to do this is to redesign everything. Thank
you for your help. I will probably have to talk to my supervisor and
tell him that it will take more than a couple of hours to fix this.
Thanks Steve
 
This is how I plan to tackle the problem. I will have a FromDate and
ToDate dropdown boxes on the form. When the user input these data the
report will gather the two date and look up the (date to preview) from
a date table on the database.
For example: 9/1/2005 - 10/1/2005

DateTable:
7/1/2005
8/1/2005
9/1/2005
10/1/2005
11/1/2005
....

The query will select the two months from the table and feed it to the
report one at a time. It will open -> print -> close the report with
9/1/2005 then open -> print -> close the report with 10/1/2005.

What I need:
1: VB code that will look up the date from the DateTable.
2: VB code for a while loop to control the date (The date in the table
is for the first of every month)
3: BV code that will open -> print -> close the report.

Thank you for reading my post. I am not very familiar with VB. Any help
would be greatly appricated.

Khoa
 
Nesster,

It really sounds like you didn't understand what I said. Unless I am
missing something here, my suggestion would take less that 5 minutes to
implement.
 
Nesster,

I would not do it like this, as it is more complicated than it needs to be.

If yu really want to use VBA code, however, there would be this type of
approach...

1. In the query that the report is based on, put your Criteria in the
date field like this...
Between [Forms]![YourForm]![FromDate] And [Forms]![YourForm]![ToDate]
2. Put a calculated field in the query like this...
ReportDate: Month([YourDateField])
3. Put code like this on the event that you want to trigger the
printing of your report(s)

Dim rst as DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT ReportMonth FROM
YourQuery")
With rst
Do Until .EOF
DoComd.OpenReport "YourReport", , , "Month([YourDateField])=" &
!ReportMonth
.MoveNext
Loop
.Close
End With
Set rst = Nothing
 
Back
Top