End a loop error

[DJRhino1175]

Private Sub ExportCP_Click()

On Error GoTo Proc_err
    
    Dim strSQL As String
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb()
            
    DoCmd.OpenForm "Web Bruce", acNormal, "CP_FEMA_PF_OutPut-QRY"
    
    strSQL = "CP_FEMA_PF_OutPut"
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
           
    With rs
        
        If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
            
    While (Not .EOF)
    
    With CodeContextObject
        
            DoCmd.OpenReport "rptFinalCPExport", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]"
            DoCmd.OutputTo acOutputReport, "rptFinalCPExport", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output], False, "", , acExportQualityPrint
            DoCmd.SetWarnings False
            DoCmd.Close acReport, "rptFinalCPExport"
            DoCmd.SetWarnings True
            DoCmd.GoToRecord acForm, "Web Bruce", acNext

    End With
         .MoveNext
    Wend
    End If
End With


Proc_err:

MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
Error:

Select Case Err.Number
    Case 2501:      'Cancel button error
        GoTo Cancel_Error
    Case 2105:
        GoTo exitsub
    Case 0
        GoTo exitsub
    Case 20
        GoTo exitsub

End Select

Cancel_Error:   'What you need the handler to do
    Err.Clear
    Resume Next
exitsub:
    Set rs = Nothing
    Set db = Nothing
    DoCmd.SetWarnings True
Exit Sub

End Sub

The above code works fairly well, the only Issue I have is when it goes through all the records needing exported it pops up an error #0 but does not state what its for. After some research its says not ending a loop can cause this error. I thought "Wend" was supposed to do this. So I researched more and found "Wend" is no longer supported. It says to use "End While". So I did this and it throws a syntax error when I try to compile it. Any one have a solution for me to try?

Thanks,

Rhino

[NeoPa]

Hi Rhino.

I'm not sure if/when While ... Wend ever got discontinued but the replacement for it is Do While ... Loop, or really Do ... Loop, which has optional While or Until modifiers that can be used at either the Do line or the Loop one.

In your case I would use Do Until .EOF ... Loop.

[DJRhino1175]

Thanks NeoPa. That's what I needed. I even cleaned up the code some more. Here is what it looks like now. No errors.

Private Sub ExportCP_Click()

On Error GoTo Error
    
    Dim strSQL As String
    Dim rs As DAO.Recordset
                
    DoCmd.OpenForm "Web Bruce", acNormal, "CP_FEMA_PF_OutPut-QRY"
    
    strSQL = "CP_FEMA_PF_OutPut"
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
           
If Not (rs.BOF And Not rs.EOF) Then
            rs.MoveFirst
            Do Until rs.EOF
        
            DoCmd.OpenReport "rptFinalCPExport", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]"
            DoCmd.OutputTo acOutputReport, "rptFinalCPExport", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output], False, "", , acExportQualityPrint
            DoCmd.SetWarnings False
            DoCmd.Close acReport, "rptFinalCPExport"
            DoCmd.SetWarnings True
            DoCmd.GoToRecord acForm, "Web Bruce", acNext

    rs.MoveNext
    Loop
    
End If

Error:
Select Case Err.Number
    Case 2501:      'Cancel button error
        GoTo Cancel_Error
    Case 2105:
        GoTo exitsub
    Case 0
        GoTo exitsub
    Case 20
        GoTo exitsub

End Select

Cancel_Error:   'What you need the handler to do
    Err.Clear
    Resume Next
exitsub:
    rs.Close
    Set rs = Nothing
    DoCmd.SetWarnings True
Exit Sub

End Sub

[NeoPa]

Always a pleasure Rhino :-)