Problem with batch report printing

[es330td]

Env: XPSP2, Access 2003, 3 Ghz CPU, 1.5 GB RAM

Note: 100% of this code was written by someone else; I have been asked
by my supervisor to solve the problem.

We have a project tracking system based in SQL Server 2005 that tracks
a moderate numbers of projects that has an Access database front end
for data manipulation and report printing. One of the actions
programmed into the database is the ability to print out a single page
report for each project. To do this, Access runs the following code:
(rst is a recordset populated by the database query: Select ProjectID
from vwBulkPrintList)

If Not (rst.EOF) Then
rst.MoveFirst

While Not (rst.EOF)
iRecNo = rst!ProjectID
sLinkCriteria = "ProjectID = " & rst!ProjectID
DoCmd.OpenForm sFormName, , , sLinkCriteria
DoCmd.OpenReport sDocName
DoCmd.Close acForm, sFormName
rst.MoveNext
Wend
End If

So it opens the Access form, populates it and then prints out a report
based on that form. When this runs correctly, the result will be
roughly 300 sheets of paper on the printer. Unfortunately, it has not
run all the way through in some time as something happens part way
through the process and it just quits, presumably because the act of
repeatedly opening the form overwhelms the system's resources. When I
run this, it just stops. It doesn't report any kind of error and
Access stays open and operational but it quits after around 30
reports.

I really have nothing to debug because it doesn't report any errors.

Does anyone have any suggestions as to how I can even investigate what
is going on to find out what I need to do to get it to print all the
reports?

 

[Duane Hookom]

Why open the form? Just open the report. Or why not just open the report with
a where condition that includes all projects and place a page break between
projects?

 

[es330td]

Why open the form? Just open the report. Or why not just open the report with
a where condition that includes all projects and place a page break between
projects?
--
Duane Hookom
Microsoft Access MVP












- Show quoted text -

When I try to open the report directly and skip the form open line it
gives me the error:
Must Open Dialog Box
To preview or print this report, you must open Project Editing in Form
view.

 

[Duane Hookom]

Change your solution so the form doesn't have to open. Did you consider my
other suggestion? Would that work for you?

 

[es330td]

Change your solution so the form doesn't have to open. Did you consider my
other suggestion? Would that work for you?

--
Duane Hookom
Microsoft Access MVP






- Show quoted text -

I don't know how to do either of those yet. My knowledge of Access is
still somewhat limited as my 14 year development career has been
almost completely web oriented. While I have used Access as a web
datastore in the past, I never made it down to the Form/Reports tabs
on the left. Once I moved up to SQL Server my Access education kind
of stopped.

 

[es330td]

Change your solution so the form doesn't have to open. Did you consider my
other suggestion? Would that work for you?

--
Duane Hookom
Microsoft Access MVP






- Show quoted text -

Okay, I've opened the report in Design Mode. I see an awful lot of
fields that look like =Forms!ProjectEditing!PrjNum. I am sure I could
just reference a query directly to populate these instead of
referencing a form. In addition, there are subsections for budget and
expenses that I can't yet determine how they get onto the report.
I'll poke around in this some more and post back what I learn.

 

[Duane Hookom]

Apparently you have some code that runs when your report opens. You would
need to find and possibly comment out that code. Then find your report's
record source query and reply back with the SQL view.