Solved Confused why loop isn't getting all records

[chuckcoleman]

Hi, I have a VBA loop that cycles through records, creates a PDF file and outputs the report to a folder. I'm testing on this database and there are only two records in the tables. i have several MsgBox lines in the code below to help me figure out what's going on. Each MsgBox begins with 100, 150, 200 or 300. If I remark-out all of the MsgBoxes, only get ONE PDF's is created. If within the loop MsgBox "100-" is the only one that "runs", two PDF's are created. If within the loop MsgBox "150" is the only one that "runs", two PDF's are created. If within the loop MsgBox "200" is the only one that "runs", ONE PDF is created.

The right behavior is that two PDF's get created. I don't understand why having a MsgBox displayed allows the two PDF's to be created. Why having something like a MsgBox displayed results in any different output is confusing. Any help please.

Do Until rs.EOF
      MsgBox ("100-Start of Loop and record count is: " & lngCount)
      strBind = CStr(rs!BindNbr)
      lngCount = lngCount + 1
      lblStatus = "Creating invoice " & CStr(lngCount) & " of " & CStr(lngRSCount)
      TempVars.Add "strBindCriteria", CStr(strBind)
      MsgBox ("150-TempVars is: " & TempVars.Item(StrBindCriteria))
      DoCmd.OpenReport strReport, acViewPreview, , "[BindNbr] = " & TempVars.Item(StrBindCriteria)
      Reports![Report for emailing dues PDF].Visible = False
      DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, "C:\HOA\Bulk Invoices\" & strBind & "_Annual Dues.pdf"
      rs.MoveNext
      DoCmd.Close acReport, strReport
   '   MsgBox ("200-End of Loop and Record count is: " & lngCount)
    Loop
      MsgBox ("300-After 'Loop' before 'End if' and Record Count is: " & lngCount)
    lblStatus = ""
    End If
     rs.Close
     MyDB.Close
     Set rs = Nothing
     Set MyDB = Nothing
     Close

 

[plog]

Do the two records have the same BindNbr value? If so you are creating a .pdf then creating another .pdf with the exact same name which just overwrites the first.

 

[chuckcoleman]

Do the two records have the same BindNbr value? If so you are creating a .pdf then creating another .pdf with the exact same name which just overwrites the first.

Hi, they have different BindNbr's. The first one created is 14 and the second one created is 15. If only one is created, it's 15.

 

[jdraw]

Can you show us all the code ( I see an orphaned End If) or a copy of the database with enough records to show the issue?

 

[chuckcoleman]

Can you show us all the code ( I see an orphaned End If) or a copy of the database with enough records to show the issue?

Hi, here is all of the code.

Private Sub Command9_Click()
On Local Error GoTo Some_Err
    
    Me.MoveToX.SetFocus
    Me.Command9.Visible = False
    Me.ClsFrm.Visible = False
    
    Dim MyDB As Database, rs As Recordset
    Dim strBody As String, lngCount As Long, lngRSCount As Long
    Dim strReport As String
    Dim strBind As Long
    Dim rstFiltered As DAO.Recordset
    
    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    strReport = "Report for emailing dues PDF"
 '   Set rs = MyDB.OpenRecordset("Query for mailing dues-New-2A_PDF")
    Set rs = MyDB.OpenRecordset("1_Email_to_owners_who_have_outstanding_balances_2")
    lngRSCount = rs.RecordCount
    If lngRSCount = 0 Then
      MsgBox "There aren't any invoices to create attachments for.", vbInformation
      Exit Sub
    Else
    DoCmd.RunMacro "Run Check for Invoices Folder"
    DoCmd.RunMacro "DeleteInvoices"
      rs.MoveLast
      rs.MoveFirst
    lblStatus = ""
    
    DoCmd.SetWarnings (WarningsOff)
    DoCmd.OpenQuery "Delete Temp of Orders and Revenue records"
    DoCmd.Close acQuery = acDefault, "Delete Temp of Orders and Revenue records"
    DoCmd.OpenQuery "Make Table- Lots with balances"
    DoCmd.Close acQuery = acDefault, "Make Table- Lots with balances"
    DoCmd.OpenQuery "Append Order totals by Lot-Not New"
    DoCmd.Close acQuery = acDefault, "Append Order totals by Lot-Not New"
    DoCmd.OpenQuery "Append Revene totals by Lot"
    DoCmd.Close acQuery = acDefault, "Append Revene totals by Lot"
    DoCmd.SetWarnings (WarningsOn)
  ' Delete above 10 lines
    
    Do Until rs.EOF
   '   MsgBox ("100-Start of Loop and record count is: " & lngCount)
      strBind = CStr(rs!BindNbr)
      lngCount = lngCount + 1
      lblStatus = "Creating invoice " & CStr(lngCount) & " of " & CStr(lngRSCount)
      TempVars.Add "strBindCriteria", CStr(strBind)
   '   MsgBox ("150-TempVars is: " & TempVars.Item(StrBindCriteria))
      DoCmd.OpenReport strReport, acViewPreview, , "[BindNbr] = " & TempVars.Item(StrBindCriteria)
      Reports![Report for emailing dues PDF].Visible = False
      DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, "C:\HOA\Bulk Invoices\" & strBind & "_Annual Dues.pdf"
      rs.MoveNext
      DoCmd.Close acReport, strReport
      MsgBox ("200-End of Loop and Record count is: " & lngCount)
    Loop
      MsgBox ("300-After 'Loop' before 'End if' and Record Count is: " & lngCount)
    lblStatus = ""
    End If
     rs.Close
     MyDB.Close
     Set rs = Nothing
     Set MyDB = Nothing
     Close
      
    MsgBox "I've finished creating the Invoice PDF attachments.  Open Outlook and then use EmailMerge PRO to send your emails.", vbInformation, "Done"
    DoCmd.Close acForm, "Create Attachments Form"
    Exit Sub
    
Some_Err:
    MsgBox "Property Owners Plus, the Error (" & CStr(Err.Number) & ") " & Err.Description, _
        vbExclamation, "Error!"
End Sub

 

[chuckcoleman]

One update/observation. I can see in Windows Explorer when the first PDF is created and then I can see when the second PDF is created. Then, the first PDF is deleted. I can't figure out what would be deleting it!

 

[Cronk]

To get an accurate count of the number in the recordset, move to the end of the recordset first

Set rs = MyDB.OpenRecordset("1_Email_to_owners_who_have_outstanding_balances_2")
    rs.movelast      '---Add this line
    lngRSCount = rs.RecordCount
    rs.movefirst     '---add this line also

 

[Gasman]

One update/observation. I can see in Windows Explorer when the first PDF is created and then I can see when the second PDF is created. Then, the first PDF is deleted. I can't figure out what would be deleting it!

Never mind windows explorer. Walk through your code line by line after setting a breakpoint.
That way you will see exactly what is happening.

 

[Minty]

I'm confused by the creation of a TempVar in each loop, and why you would change the number to a string, when it's actually a number?
Actually, you do it twice both strBind is actually dimmed as a long number and strBindCriteria are the same thing as a string?

lngBind would make more sense, and just use that.

Access isn't fussy about stuffing a number into a concatenated string, simply use the number.

 

[Cronk]

@ Minty, tempVar is redundant code to be able to generate the progress message

 

[jdraw]

A couple of observations: (neither affecting your current issue)
-On Local Error is an old VB technique for backward compatibility
-Recommendation now is to explicitly Dim Database and recordsets with DAO.

As suggested, use some breakpoints, and/or add msgboxes/debug.prints to see exactly what is happening.

 

[chuckcoleman]

I appreciate everyone's input. For sake of clarity, I've removed all of the MsgBox entries except the one that begins with 110. If I leave that MsgBox in the code, (not remarked out), I get both records. If I remark it out, I only get the second record. I have a hard time believing it's a timing problem, but I'm not sure. It just doesn't make sense why having a MsgBox entry would product the right result.

 Do Until rs.EOF
      strBind = CStr(rs!BindNbr)
      MsgBox ("110-The BindNbr is: " & strBind)
      lblStatus = "Creating invoice " & CStr(lngCount) & " of " & CStr(lngRSCount)
      TempVars.Add "strBindCriteria", CStr(strBind)
      DoCmd.OpenReport strReport, acViewPreview, , "[BindNbr] = " & TempVars.Item(StrBindCriteria)
      Reports![Report for emailing dues PDF].Visible = False
      DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, OutputLocation & strBind & "_Annual Dues.pdf"
      rs.MoveNext
      DoCmd.Close acReport, strReport
      lngCount = lngCount + 1
     Loop

    lblStatus = ""
    End If
     rs.Close
     MyDB.Close
     Set rs = Nothing
     Set MyDB = Nothing
     Close
      
    MsgBox "I've finished creating the Invoice PDF attachments.  Open Outlook and then use EmailMerge PRO to send your emails.", vbInformation, "Done"
    DoCmd.Close acForm, "Create Attachments Form"
    Exit Sub

 

[jdraw]

I suggest you post a copy of the database with just enough info to highlight/produce the error,
preferably zip format, for readers to delve further.

 

[chuckcoleman]

I figured it out. I don't know why but early in the code I run a Macro that runs a batch file called, "delete_Invoices.bat". That batch file deletes pdf files in a folder. I had previously added, "Dim OutputLocation As String" and set it to "C:\HOA\Bulk Invoices". I remarked the macro batch line out that runs the batch file and replaced it with: Kill OutputLocation & "\*.pdf". For some reasons the batch program was deleting it seems like the first pdf files that was created by the DoCmd.OutputTo........If felt like a timing problem and I think there must have been a delay in the batch file running which caused the first PDF that was created to be erased. Yikes!

 

[jdraw]

Glad you have found the issue and resolved it.