Error handling with loop (1 Viewer)

[Thicko]

Hi All,

I've got a problem with a loop for producing worksheets (reports). When everything works there's no problem but when one of the reports namely rptChemoOralWorksheet doesn't have data available to produce the report the loop finishes and then all remaining worksheets don't print.

I've put in a standard error handler with MsgBox, but it's never triggered. I've also tried

On Error Resume Next

but again rather than skip the DoCmd.OpenReport "rptChemoOralWorksheet" (when the error is present) and move to the next one to continue printing through the list the loop stops and the subsequent code starts to run.

Dim mydatabase As DAO.Database
Dim rst As DAO.Recordset
Set mydatabase = CurrentDb
Set rst = mydatabase.OpenRecordset("atblChemoWSLoop", dbOpenDynaset)    
Dim rsBN As String
Dim rsDose As Double
Dim rsBS As Double
Dim rsPrintNumber As Double
Dim rsPreparation As String
Dim rsRoute As String
Dim rsClinicalTrial As Boolean
Dim rsPatient As String
i = 0
rst.MoveFirst
 
Do While Not rst.EOF
 
rsBN = rst!BatchNumber
rsPreparation = rst!Preparation
rsRoute = rst!Route
rsDose = rst!Dose
rsBS = rst![Batch Size]
rsPrintNumber = rsBS + 1
rsClinicalTrial = rst!ClinicalTrial
 
If rsRoute = "Oral" Then
    DoCmd.OpenReport "rptChemoOralWorksheet", , , "BatchNumber = '" & rsBN & "' And Dose=" & rsDose
 
Else
    DoCmd.OpenReport "rptChemoWorksheet", , , "BatchNumber = '" & rsBN & "' And Dose=" & rsDose 'Need to remove acViewPreview
 
    DoCmd.OpenReport "rptChemoLabel", acViewPreview, , "BatchNumber = '" & rsBN & "' And Dose=" & rsDose
'    DoCmd.PrintOut , , , , rsPrintNumber
    DoCmd.Close acReport, "rptChemoLabel"
 
    If rsPreparation = "Epirubicin" And rsDose > 80 Or rsPreparation = "Doxorubicin" And rsDose > 80 Or rsPreparation = "Azacitidine" And rsDose > 100 Then
        DoCmd.OpenReport "rptChemoLabel1", acViewPreview, , "BatchNumber = '" & rsBN & "' And Dose=" & rsDose
'        DoCmd.PrintOut , , , , rsPrintNumber
        DoCmd.Close acReport, "rptChemoLabel1"
        Else
    End If
 
    If rsPreparation = "Vincristine" Or rsPreparation = "Vinblastine" Or rsPreparation = "Vinorelbine" Then
        DoCmd.OpenReport "rptChemoVincLabel", acViewPreview
'        DoCmd.PrintOut , , , , 2
        DoCmd.Close acReport, "rptChemoVincLabel"
        Else
    End If
 
    If rsPreparation = "Ganciclovir" Then
        DoCmd.OpenReport "rptChemoGanLabel", acViewPreview
'        DoCmd.PrintOut , , , , 2
        DoCmd.Close acReport, "rptChemoGanLabel"
        Else
    End If
End If
If rsClinicalTrial = -1 Then
    DoCmd.OpenReport "rptChemoCTLabel", acViewPreview
'    DoCmd.PrintOut , , , , 2
    DoCmd.Close acReport, "rptChemoCTLabel"
    Else
End If
    i = i + 1
 
    rst.MoveNext
Loop

Any insight on what is happening and how to resolve would be greatly appreciated.

 

[Ranman256]

dont use
On Error Resume Next

after the dim's put

On error goto ErrHere

'... your code here


  'then right before the END SUB

exit sub
ErrHere:
msgbox err.description,,err
end sub

 

[GinaWhipp]

You can specifically check to make sure the Report has data to print and, if not, skip it. Use a DCount() on the Recordsource for the Report, i.e.

If DCount("FieldInQueryTable", "ReportRecordSource") > 0 Then
     If rsPreparation = "Vincristine" Or rsPreparation = "Vinblastine" Or rsPreparation = "Vinorelbine" Then
          DoCmd.OpenReport "rptChemoVincLabel", acViewPreview
          'DoCmd.PrintOut , , , , 2
          DoCmd.Close acReport, "rptChemoVincLabel"
     End if
Else
     DoCmd.CancelEvent
End If

 

[Thicko]

Thanks for the replies, I haven't been able to get the error handler to see this as an issue it just comes out of the loop.

I've gone with GinaWhipp's solution which has done the job.

Many Thanks

 

[GinaWhipp]

Great! Side note... that is what Error Handler does so in your case while you should have an Error Handler it would not have solved your problem.