program stops after openreport

[icccapital]

Access 2007 on Vista

I have a loop that loops through each client in a table that I get by using
the opendbrecordset function. Within the loop the openreport method is
called to send the report to the printer using this client.

What happens is the print job takes a few seconds, and once it is completed
the program is no longer running. I added a breakpoint just after the
openreport method and it doesn't even get to that breakpoint, the program
just is no longer running as if it timed out or something.

Any thoughts?

 

[OssieMac]

Could you post the code you are using otherwise only guessing in the dark.

 

[icccapital]

OssieMac, thanks for getting back on this. Below is the code with the issue.
So hopefully you can see that it gets a lits of clientcodes from the
database and loops through them. It then for each client gets a lit of that
clients contacts and loops through them. For each of these it will try to
print the report testapprep1. What happens is testapprep1 is called. It
works for a while, but a tsome point the process runs through the print and
then seems to just stop becuase it is on the movenext that I put a breakpoint
and it doesn't get there for some reason.

selClientQuery = "SELECT DISTINCT ClientCode FROM Appraisals WHERE
Appraisals.ReportDate = #" & Forms!AppraisalQueryForm!txtReportDate.value &
"# ORDER BY ClientCode"
Set rsClients = CurrentDb.OpenRecordset(selClientQuery,
dbOpenDynaset)
Do While (Not rsClients.EOF)
Forms!AppraisalQueryForm!txtClientCode.value =
rsClients.Fields(0)
selContactIDQuery = "SELECT Contacts.ID1,
Clients.ClientCode FROM Contacts INNER JOIN Clients ON Contacts.ID1 =
Clients.ContactID WHERE ClientCode = """ &
Forms!AppraisalQueryForm!txtClientCode.value & """;"
Set rsContactID =
CurrentDb.OpenRecordset(selContactIDQuery, dbOpenDynaset)
Do While (Not rsContactID.EOF)
Forms!AppraisalQueryForm!txtContactID.value =
rsContactID.Fields(0)
DoCmd.OpenReport "testAppRep1", acViewNormal
rsContactID.MoveNext
Loop
rsClients.MoveNext
Forms!AppraisalQueryForm!txtContactID.value = ""
Loop

 

[icccapital]

Ok, I was able to figure out the issue. And for those who are interested it
seems that when you call a report with open report, if the report would not
run normally with the parameters given the code that called the report will
also stop. So I was sending a parameter that meant that the query would
return empty and the report would normally just come back blank, but the
control wasn't returned to the calling function, it just stops running.
Seems to me that the control should come back tot he calling function it can
continue its run.

 

[OssieMac]

I don't know if it will solve your problem but you might like to try opening
the report as acHidden first then use HasData to test for an empty report.

Code should be something like this but it is untested.

DoCmd.OpenReport "testAppRep1", acPreview, , , acHidden

If Reports![testAppRep1].HasData = False Then
MsgBox "Report is empty" 'MsgBox will help you in testing
GoTo labelSkipPrint
End If

DoCmd.OpenReport "testAppRep1", acViewNormal
labelSkipPrint:
rsContactID.MoveNext
Loop

 

[icccapital]

Thanks for the continued help. I was actually able to figure out that
openrepor twill not return to the calling function if the report query is
null, so I solved the problem by making sure the report query is never null.
Seems strange to me that it wouldn't return control, but oh well.

I don't know if it will solve your problem but you might like to try opening
the report as acHidden first then use HasData to test for an empty report.

Code should be something like this but it is untested.

DoCmd.OpenReport "testAppRep1", acPreview, , , acHidden

If Reports![testAppRep1].HasData = False Then
MsgBox "Report is empty" 'MsgBox will help you in testing
GoTo labelSkipPrint
End If

DoCmd.OpenReport "testAppRep1", acViewNormal
labelSkipPrint:
rsContactID.MoveNext
Loop

--
Regards,

OssieMac

Ok, I was able to figure out the issue. And for those who are interested it
seems that when you call a report with open report, if the report would not
run normally with the parameters given the code that called the report will
also stop. So I was sending a parameter that meant that the query would
return empty and the report would normally just come back blank, but the
control wasn't returned to the calling function, it just stops running.
Seems to me that the control should come back tot he calling function it can
continue its run.