Report Output Errors

[Simms]

From the db, I can run any one of 60 reports individually and the report
outputs are error-free. I set up a macro to automate the process and the
reports are coming back with missing information. Seems like a very simple
process but I cannot locate any errors in syntax or with the tables/queries
generating the reports. Any help is GREATLY appreciated.

 

[Duane Hookom]

can you provide any information about the process used to run the reports
individually? Are there parameters, criteria, user interaction,...?

 

[Marshall Barton]

From the db, I can run any one of 60 reports individually and the report
outputs are error-free. I set up a macro to automate the process and the
reports are coming back with missing information. Seems like a very simple
process but I cannot locate any errors in syntax or with the tables/queries
generating the reports.

I've seen that effect when trying to open the same report
object repeatedly.

 

[Simms]

I have a form using a combo box with a drop down menu. After selecting an
item in the drop down, The output is built from a report and sub report
running off two separate queries. The subreport uses 4 union queries. The
data used to populate the queries is based on the selection in the drop down
and a cross reference between 4 tables.

 

[Duane Hookom]

This sounds fairly complex. Are all 60 reports different or are you printing
some reports more than once with different criteria? If you are using
different criteria selected from a combo box, how did you automate this?

It seems you are not telling us nearly enough significant information.
Please be more complete.

 

[Simms]

It is essentially the same report ran 60 times using different criteria. We
incorporated two text boxes onto the form to display data depending on what
was selected in the combo box.

Here is a little more of the nuts and bolts:

A button executes the function below. The button is not on the form with the
combo box.

Here is the code that automates the report execution and saving:

-----------------------------------------------------------------------------------

On Error GoTo EOQ_Test3_Err
Dim location As String
Dim rsSVP As New ADODB.Recordset
Dim SVP As String
Dim conn As New ADODB.Connection
'*** CONNECT TO DATABASE ***
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=X:\XXX"
'Open the query and get the SVPs
rsSVP.Open "SELECT * FROM qrySVPList", conn, adOpenDynamic '***This
returns 60 users****
If rsSVP.EOF Then
MsgBox rsSVP.RecordCount
MsgBox "No SVPs Selected"
Else
while rsSVP.EOF = False

DoCmd.OpenForm "frmReports", acNormal

Forms!frmReports!cmboApprover = rsSVP.Fields("Approver")

Forms!frmReports!txtFirstName =
Forms!frmReports!cmboApprover.Column(1)

Forms!frmReports!txtLastName =
Forms!frmReports!cmboApprover.Column(0)

location = "X:\XXX" & Forms!frmReports!txtLastName & " " &
Forms!frmReports!txtFirstName & ".snp"

DoCmd.OutputTo acOutputReport, "rpt_Approver_Summary",
acFormatSNP, location, False

DoCmd.Close acForm, "frmReports"

rsSVP.MoveNext

Wend

End If

EOQ_Test3_Exit:
Exit Function

EOQ_Test3_Err:
MsgBox Error$
Resume EOQ_Test3_Exit

End Function
-----------------------------------------------------------------------------------------------

When we put a where clause to limit the rsSVp recordset, the reports that
had issues come back correct. The weird part is when we run the macro without
a where clause, the reports come back with missing records no matter where in
the recordset they occur (ie: the first report to come back has missing
records in it).

Again, the report is made up of main section and a subreport both executed
by different queries. The queries of both reports pass the first name and
last name parameters from the form. The missing records are NOT occuring in
the main report but in the subreport.

The report is successful whenever we limit the recordset in the code or run
it manually from the form.

 

[Duane Hookom]

I think Marsh hit the nail on the head. I think the complexity of the report
with the criteria from the form is causing a timing issue.

I would probably try to base the report on a saved query and use your basic
looping code to change the SQL property of the saved query. Then print the
report without any reference to frmReports.