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 :-)