470,593 Members | 2,387 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,593 developers. It's quick & easy.

End a loop error

DJRhino1175
191 128KB
Expand|Select|Wrap|Line Numbers
  1. Private Sub ExportCP_Click()
  2.  
  3. On Error GoTo Proc_err
  4.  
  5.     Dim strSQL As String
  6.     Dim rs As DAO.Recordset
  7.  
  8.     Set db = CurrentDb()
  9.  
  10.     DoCmd.OpenForm "Web Bruce", acNormal, "CP_FEMA_PF_OutPut-QRY"
  11.  
  12.     strSQL = "CP_FEMA_PF_OutPut"
  13.  
  14.     Set rs = CurrentDb.OpenRecordset(strSQL)
  15.  
  16.     With rs
  17.  
  18.         If Not .BOF And Not .EOF Then
  19.             .MoveLast
  20.             .MoveFirst
  21.  
  22.     While (Not .EOF)
  23.  
  24.     With CodeContextObject
  25.  
  26.             DoCmd.OpenReport "rptFinalCPExport", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]"
  27.             DoCmd.OutputTo acOutputReport, "rptFinalCPExport", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output], False, "", , acExportQualityPrint
  28.             DoCmd.SetWarnings False
  29.             DoCmd.Close acReport, "rptFinalCPExport"
  30.             DoCmd.SetWarnings True
  31.             DoCmd.GoToRecord acForm, "Web Bruce", acNext
  32.  
  33.     End With
  34.          .MoveNext
  35.     Wend
  36.     End If
  37. End With
  38.  
  39.  
  40. Proc_err:
  41.  
  42. MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
  43. Error:
  44.  
  45. Select Case Err.Number
  46.     Case 2501:      'Cancel button error
  47.         GoTo Cancel_Error
  48.     Case 2105:
  49.         GoTo exitsub
  50.     Case 0
  51.         GoTo exitsub
  52.     Case 20
  53.         GoTo exitsub
  54.  
  55. End Select
  56.  
  57. Cancel_Error:   'What you need the handler to do
  58.     Err.Clear
  59.     Resume Next
  60. exitsub:
  61.     Set rs = Nothing
  62.     Set db = Nothing
  63.     DoCmd.SetWarnings True
  64. Exit Sub
  65.  
  66. 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
Mar 22 '22 #1

✓ answered by 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.

3 4065
NeoPa
32,298 Expert Mod 16PB
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.
Mar 22 '22 #2
DJRhino1175
191 128KB
Thanks NeoPa. That's what I needed. I even cleaned up the code some more. Here is what it looks like now. No errors.

Expand|Select|Wrap|Line Numbers
  1. Private Sub ExportCP_Click()
  2.  
  3. On Error GoTo Error
  4.  
  5.     Dim strSQL As String
  6.     Dim rs As DAO.Recordset
  7.  
  8.     DoCmd.OpenForm "Web Bruce", acNormal, "CP_FEMA_PF_OutPut-QRY"
  9.  
  10.     strSQL = "CP_FEMA_PF_OutPut"
  11.  
  12.     Set rs = CurrentDb.OpenRecordset(strSQL)
  13.  
  14. If Not (rs.BOF And Not rs.EOF) Then
  15.             rs.MoveFirst
  16.             Do Until rs.EOF
  17.  
  18.             DoCmd.OpenReport "rptFinalCPExport", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]"
  19.             DoCmd.OutputTo acOutputReport, "rptFinalCPExport", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output], False, "", , acExportQualityPrint
  20.             DoCmd.SetWarnings False
  21.             DoCmd.Close acReport, "rptFinalCPExport"
  22.             DoCmd.SetWarnings True
  23.             DoCmd.GoToRecord acForm, "Web Bruce", acNext
  24.  
  25.     rs.MoveNext
  26.     Loop
  27.  
  28. End If
  29.  
  30. Error:
  31. Select Case Err.Number
  32.     Case 2501:      'Cancel button error
  33.         GoTo Cancel_Error
  34.     Case 2105:
  35.         GoTo exitsub
  36.     Case 0
  37.         GoTo exitsub
  38.     Case 20
  39.         GoTo exitsub
  40.  
  41. End Select
  42.  
  43. Cancel_Error:   'What you need the handler to do
  44.     Err.Clear
  45.     Resume Next
  46. exitsub:
  47.     rs.Close
  48.     Set rs = Nothing
  49.     DoCmd.SetWarnings True
  50. Exit Sub
  51.  
  52. End Sub
Mar 23 '22 #3
NeoPa
32,298 Expert Mod 16PB
Always a pleasure Rhino :-)
Mar 24 '22 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by undercups | last post: by
1 post views Thread by SJ | last post: by
8 posts views Thread by Sheldon | last post: by
11 posts views Thread by orajit | last post: by
reply views Thread by if1467 | last post: by
2 posts views Thread by rando1000 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.