0

I am using Access 2007. I want to use VBA code that will programmatically run an Access Report a series of times. Something like this:

Dim eid$(5)
eid$(1)="001":eid$(2)="021":eid$(3)="043":eid$(4)="052":eid$(5)="067"
for i=1 to 5
   Forms![frmWork]!txtEid=eid$(i)
   DoCmd.OpenReport "rptEmployees", acViewPreview
next i

The report uses a query that contains:

WHERE (employee.eid)=Forms![frmWork]!txtEid

After the report appears on the screen I want it to immediately close - without the user clicking Close Print Preview - and return to the VBA loop so another report can be generated.

You may wonder why I'm doing this? Let's just say I'm experimenting.

My question is...how can I get the report to close? Is there an event that occurs when a report has finished printing?

If I knew how many records (n) were output by the query I could put a counter in the report's Detail_Print() code. When the counter reached n I could have the report close. Can I determine how many records are in the query results when the report opens?

Thanks very much.

CurtisD
  • 135
  • 8
  • If you open a multi-page report in print preview, it is never finished, until you go to the last page. -- I'd simply use [Sleep](https://stackoverflow.com/questions/469347/is-there-an-equivalent-to-thread-sleep-in-vba). – Andre Jul 18 '18 at 07:06
  • I don't really want to open the report in print preview. It could be hidden or in the background. I simply want the report to close after the last item is printed. Is there a way to do that? – CurtisD Jul 18 '18 at 07:22
  • 1
    Then print to [NUL](https://stackoverflow.com/questions/313111/is-there-a-dev-null-on-windows) or a PDF printer driver. -- Perhaps if you'd explain *why* you want to do this, we could give better ideas. – Andre Jul 18 '18 at 07:52
  • OK. The report contains VBA code that writes data to a file (in addition to printing a report). This report has been run hundreds of times and has created a very large file, because each time it runs more data gets added to the file. That file was accidentally deleted. I would now like to programmatically run that report so that it will run all those previous reports again. I can do that in a loop using some VBA code, but I need to return to that VBA code after each running of the report. So I need to close the report as soon as it has printed. – CurtisD Jul 18 '18 at 08:07
  • 2
    Then NUL should work perfectly. Set up a dummy printer in Windows that prints to NUL, and assign this printer to the report. And open the report with `acViewNormal`. – Andre Jul 18 '18 at 08:14
  • All right! That was the solution. I did not realize that acViewNormal resulted in the closing of the report. Thank you so much! – CurtisD Jul 18 '18 at 08:29

0 Answers0