Loopy from Loops (1 Viewer)

[brucesilvers]

Unexpected Loop Results

I am attempting to create a form for the automated printing of a series of reports. The form is tied to a query that captures Territory ID, Sales Rep ID, # of Reps in that Territory, Total # of Territories. Once completed, the code behind the form should:
1) Print the cover page report (Salesrep Sales Analysis – Cover);
2) For each of the territories, print a report for each sales rep (Salesrep Sales Analysis - A Rep) and once all the reps for that territory have been printed, print a summary report for that territory (Salesrep Sales Analysis - A Territory;
3) Print a combined report for all sales reps (Salesrep Sales Analysis - ALL by Month); and finally
4) Print the final page report (Salesrep Sales Analysis - Territory Totals).

I created the following code to accomplish that, but the results are in error:
1) The first rep for the first territory is being repeated (doesn’t seem to move off that first record at the correct time);
2) The final rep for the first territory is being skipped, and the first summary report is printed;
3) The second territory ends 1 rep short and the code moves into the 3rd territory without printing the summary for the 2nd territory;
4) The 1st rep of the 3rd territory prints, then the territory summary prints, then the 3rd territory continues, again stopping short of the final rep before moving to the 4th territory;
5) The two reps of the 4th territory (only 2 for that one) print, then the summary report for the 5th territory prints without printing the 4th territory summary or the report for the single rep for the 5th territory;
6) All three of the reps for the 6th territory print, then the summary for the 7th territory prints;
7) Three of the four reps for the 7th territory print, then I get an error message “You can’t go to the specified record” which ends the routine before the summary for that 7th territory and the last two reports are printed.

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize

' Set Variables for Rep Count, Territory Count, Page Number and Date/Time
    TotalReps = Me.CountOfSO_Rep
    RemainingReps = TotalReps
    Me.RepCount = RemainingReps
    TotalTerritories = Me.CountOfTerritory
    RemainingTerritories = TotalTerritories
    Me.TerritoryCount = RemainingTerritories
    PageNumber = 2
    Me.PageNumber = PageNumber
    Me.myTime = Now()

' Turn off action/warning messages
    DoCmd.SetWarnings False

' Print Report Cover Page
    DoCmd.OpenReport "Salesrep Sales Analysis - Cover", acViewNormal
        
DoCmd.GoToRecord , , acNext
        
' Loop through all the Territories - 01, 02, 04, 05, 06, 08, 09
    Do While RemainingTerritories >= 1

    ' Loop through all Reps for the Current Territory
        Do While RemainingReps >= 1
            DoCmd.OpenReport "Salesrep Sales Analysis - A Rep", acViewNormal
            DoCmd.GoToRecord , , acNext
            RemainingReps = RemainingReps - 1
            Me.RepCount = RemainingReps
            Me.PageNumber = PageNumber + 1
            Me.Repaint
        Loop
        ' End of Rep Loop
    
        DoCmd.OpenReport "Salesrep Sales Analysis - A Territory", acViewNormal
        DoCmd.GoToRecord , , acNext
        TotalReps = Me.CountOfSO_Rep
        RemainingReps = TotalReps
        Me.RepCount = RemainingReps
        RemainingTerritories = RemainingTerritories - 1
        Me.TerritoryCount = RemainingTerritories
        Me.PageNumber = PageNumber + 1
        Me.Repaint
    Loop
' End of Territory Loop

' Print Territory Totals Report for the Final Territory
    DoCmd.OpenReport "Salesrep Sales Analysis - A Territory", acViewNormal

' Print Totals Report for All Reps Combined
    DoCmd.OpenReport "Salesrep Sales Analysis - ALL by Month", acViewNormal

' Print Totals by Territory Report - Final Report Page
    DoCmd.OpenReport "Salesrep Sales Analysis - Territory Totals", acViewNormal

' Notify User that All Reports Have Been Printed
    Beep
    MsgBox "Salesrep Sales Analysis Reports have been sent to the printer.", vbOKOnly, ""

End Sub

I’ve spent a couple of days looking at this, trying different things, and am not getting satisfactory results. Can anyone find what I’m doing wrong here? Thanks very much for any help you can provide!

 

[RuralGuy]

How are you passing the Rep Number to "Salesrep Sales Analysis - A Rep" report? I guess I'm a little confused, sorry. Is the report reaching back into this form for some data? I would think you would use the WhereCondition or the OpenArgs.

 

[brucesilvers]

The underlying query for the report is getting the Rep Number from this form:

[Reps List].[SO Rep]=[Forms]![Automation]![SO Rep]

 

[RuralGuy]

I think you are fighting a timing issue. Change all of your "DoCmd.OpenReport" to:

DoCmd.OpenReport "YourReportName", acViewNormal, , ,acDialog

That will halt processing in the "Automation" form until the report is complete.

 

[Pat Hartman]

There is no reason to do this with a code loop. All you need to do is add the correct sorting and grouping options for the report. Then you need only a single DoCmd.OpenReport to accomplish step 2. If you want the page numbers to reset for each sales person, you can do that in the Format section where the page number appears.

 

[brucesilvers]

First, thank you for your assistance on this!

I figured out that my Do counts were slightly wrong, which escaped the loop too early. So, I changed them to Do Until loops and then added an If statement to check to see whether the form had reached the absolute, final rep and, if so, to escape the loops. The code is now working as I had intended.

There are a few reasons why I did this with loops instead of placing the reps and their territory rollups into the same report. First, the reports had to exactly match some old ones that come out of the old unix-based system here, both visually and in terms of data. That forced me to separate the rep report from the territory rollup. Second, users wanted to be able to pull up a rep report for a single, prompted rep, as well as being able to pull up just a single territory's rollup. Finally, when running all these reports as a package, the dates and times displayed had to remain exactly the same and the page numbers had to flow consecutively from cover page to final report. Thanks again for your help!!!