Perform Actions for Each 15 records a query produces

[padlocked17]

All -

I have a query that generates info that I'm using to create a powerpoint presentation (Automated that is).

As you can see below I'm using some info in the query to determine how many people fit the criteria. I then check to see if anyone meets the requirements. If they do, a slide is created.

What I need to do is say, if records exist, for the first 12, create a slide and if there are still more people that meet the criteria, create another slide where we left off for the next 12 people and so on.

I could really use some help on this one. Should be pretty basic, but I'm drawing a blank.

'define data for slide
        Set rs = CurrentDb.OpenRecordset("SELECT * from qryPermPartyExport WHERE Months <= 1")
        'rs.MoveFirst
        If rs.RecordCount > 0 Then
        
        'Now that we know records exist, we need to limit it to 12 records and then create a new slide
        
        rs.MoveFirst
        'there are persons for this slide
            'create slide
            InsertSlide ppPres, x, "PP"
             
            With .Slides(x)
                'layout titles
                ' [REMOVED ALL SLIDE CODE]
                'insert data
                ' [REMOVED ALL SLIDE CODE]
                y = 3
                Do While Not rs.EOF 'as long as there is another person
                y = y + 1  'move to the next row
                    rs.MoveNext  'move to next person
                Loop
            End With
        x = x + 1
        End If
        'close dataset for 1st slide
        rs.Close

 

[petehilljnr]

If (y-3) Mod 12 = 0 then move to the next slide.

 

[CraigDolphin]

Set rs = CurrentDb.OpenRecordset("SELECT * from qryPermPartyExport WHERE Months <= 1")
'rs.MoveFirst
If rs.RecordCount > 0 Then

You may want to perform a rs.MoveLast before testing the recordcount since this property doesn't work without traversing the recordset first.

Alternatively, you could use a Dcount() function to test for records before opening the recordset etc.

 

[padlocked17]

If (y-3) Mod 12 = 0 then move to the next slide.

Could you help me place that in the code. I'm looking and to be honest I'm not sure what this is doing or where it's to be placed.

Thanks!

 

[padlocked17]

You may want to perform a rs.MoveLast before testing the recordcount since this property doesn't work without traversing the recordset first.

Are we just talking about:

Set rs = CurrentDb.OpenRecordset("SELECT * from qryPermPartyExport WHERE Months <= 1")
rs.MoveLast
If rs.RecordCount > 0 Then

 

[CraigDolphin]

Yes. That's it. I don't know anything about the rest of your code but that was one potential for error that I could see.

 

[petehilljnr]

Without writing all of the code, the concept is to use the MOD function.
This will give you the whole remainder of a division.

e.g. 7 / 2 = 3 remainder 1 -> (3 * 2) + 1 = 7
or
7 MOD 2 = 1

If I've confused you, look it up on Help.

Anyway - the concept is that every 12th record, you have to trigger a new slide.
i.e. if you divide the record number you are at in your loop by 12 and it has 0 remainder, then you should add a new slide.

if X denotes what record number I am at then:

If X MOD 12 = 0 Then 'we are at a 12th record
Add a new Slide and then add this row to the slide
The new slide is now the current slide for further records
Else
Add this row to the current slide
End If

Sorry - am at work and can't write the whole code out for you at the moment.

Pete.

 

[padlocked17]

I'm struggling. I can't for the life of me figure this out. I've got the code posted and I can't figure out how to tie in your code to make it work for me.

' Setup the set of slides
    With ppPres
        
        'define data for slide
        Set rs = CurrentDb.OpenRecordset("SELECT * from qryPermPartyExport WHERE Months <= 1")
        'rs.MoveLast
        If rs.RecordCount > 0 Then
        
        'Now that we know records exist, we need to limit it to 12 records and then create a new slide

'============
'This is what I can't figure out
'============
        
        rs.MoveFirst
        'there are persons for this slide
            'create slide
            'calls a sub function
            InsertSlide ppPres, x, "PP"
             
            With .Slides(x)
                'layout titles
                .Shapes(1).TextFrame.TextRange.Text = "Permanent Party"
                .Shapes(2).TextFrame.TextRange.Text = "1 Month from Expiration"
                .Shapes(4).TextFrame.TextRange.Text = footerData1
                .Shapes(5).TextFrame.TextRange.Text = footerData2
                .FollowMasterBackground = msoFalse
                .Background.Fill.ForeColor.RGB = RGB(255, 0, 0)
                'insert data
                'Starts us on the third row
                y = 3
                Do While Not rs.EOF 'as long as there is another person
                    .Shapes(3).Table.Cell(y, 1).Shape.TextFrame.TextRange.Text = rs!Name
                    .Shapes(3).Table.Cell(y, 2).Shape.TextFrame.TextRange.Text = rs!ZoneEnter
                    .Shapes(3).Table.Cell(y, 3).Shape.TextFrame.TextRange.Text = rs!ZoneExit
                    .Shapes(3).Table.Cell(y, 4).Shape.TextFrame.TextRange.Text = rs!LastCheck
                    .Shapes(3).Table.Cell(y, 5).Shape.TextFrame.TextRange.Text = rs!LastNN
                    .Shapes(3).Table.Cell(y, 6).Shape.TextFrame.TextRange.Text = rs!LastSpot
                    .Shapes(3).Table.Cell(y, 7).Shape.TextFrame.TextRange.Text = rs!IRC
                    .Shapes(3).Table.Cell(y, 8).Shape.TextFrame.TextRange.Text = rs!Tactics
                    .Shapes(3).Table.Cell(y, 9).Shape.TextFrame.TextRange.Text = rs!Open
                    .Shapes(3).Table.Cell(y, 10).Shape.TextFrame.TextRange.Text = rs!Closed
                    y = y + 1  'move to the next row
                    

                    
                    rs.MoveNext  'move to next person
                Loop
            End With
        x = x + 1
        End If
        'close dataset for 1st slide
        rs.Close

 

[GolferGuy]

Russ, I really think this should do it. Basically this is what is happening.
Open the recordset, then While the recordset is not at EOF, do the code until hitting the WEnd, which is really the bottom of the While loop. This means that we will just keep reading records and creating slides until we run out of records. The Do While loop that you already have in your code takes care of reading the 12 record to build one slide, then we just exit the Do loop just after processing the 12th person in the recordset. By reseting the "person" counter (x) to 1, we are ready to do the next slide. The next person (record) has already been read into the VBA code, so we just needed to add the method to get back to the top of your code. The While Not rs.EOF has done that for us now. Here is the code:

' Setup the set of slides
    With ppPres
        
        'define data for slide
        Set rs = CurrentDb.OpenRecordset("SELECT * from qryPermPartyExport WHERE Months <= 1")
' *** -->  Remmove this:        'rs.MoveLast
'** Change this:        If rs.RecordCount > 0 Then
        While Not rs.EOF Then   'this lets you know if there are any records here. Quickest way possible        
        'Now that we know records exist, we need to limit it to 12 records and then create a new slide

'============
'This is what I can't figure out
'============
        
        rs.MoveFirst                    'You really are already there, but this is still OK to do.
        'there are persons for this slide
            'create slide
            'calls a sub function
            InsertSlide ppPres, x, "PP"             'Where does "x" come from?  I don't see it initialized.
             
            With .Slides(x)
                'layout titles
                .Shapes(1).TextFrame.TextRange.Text = "Permanent Party"
                .Shapes(2).TextFrame.TextRange.Text = "1 Month from Expiration"
                .Shapes(4).TextFrame.TextRange.Text = footerData1
                .Shapes(5).TextFrame.TextRange.Text = footerData2
                .FollowMasterBackground = msoFalse
                .Background.Fill.ForeColor.RGB = RGB(255, 0, 0)
                'insert data
                'Starts us on the third row
                y = 3
                Do While Not rs.EOF 'as long as there is another person
                    .Shapes(3).Table.Cell(y, 1).Shape.TextFrame.TextRange.Text = rs!Name
                    .Shapes(3).Table.Cell(y, 2).Shape.TextFrame.TextRange.Text = rs!ZoneEnter
                    .Shapes(3).Table.Cell(y, 3).Shape.TextFrame.TextRange.Text = rs!ZoneExit
                    .Shapes(3).Table.Cell(y, 4).Shape.TextFrame.TextRange.Text = rs!LastCheck
                    .Shapes(3).Table.Cell(y, 5).Shape.TextFrame.TextRange.Text = rs!LastNN
                    .Shapes(3).Table.Cell(y, 6).Shape.TextFrame.TextRange.Text = rs!LastSpot
                    .Shapes(3).Table.Cell(y, 7).Shape.TextFrame.TextRange.Text = rs!IRC
                    .Shapes(3).Table.Cell(y, 8).Shape.TextFrame.TextRange.Text = rs!Tactics
                    .Shapes(3).Table.Cell(y, 9).Shape.TextFrame.TextRange.Text = rs!Open
                    .Shapes(3).Table.Cell(y, 10).Shape.TextFrame.TextRange.Text = rs!Closed
                    y = y + 1  'move to the next row

                    rs.MoveNext  'move to next person
                    x = x + 1
                    If x > 12 Then Exit Do
                Loop
                x = 1
            End With
        Wend
'close dataset when finished.
        rs.Close

 

[padlocked17]

The x is actually a variable for the slide index number. I have that initizlized a little further up in the code:

    'create new powerpoint presentation
    Set ppPres = ppObj.Presentations.Add
    x = 1

This way each time a slide is added, it add's one to the variable allowing me to create additional slides.

 

[GolferGuy]

Then where ever I used x, change that to a z. But z will have to be initialized right after the start of the While/Wend loop. Initialize z to be equal to 1. Here is how to change the code near the bottom.

                    rs.MoveNext  'move to next person
                    x = x + 1
                    If x > 12 Then Exit Do
                Loop
                x = 1

Make it look like this:

                    rs.MoveNext  'move to next person
                    x = x + 1
                    z = z + 1
                    If z > 12 Then Exit Do
                Loop

The z = 1 does not have to be here because that is the first thing done within the While/Wend loop

 

[padlocked17]

Got it. I did basically the same thing.

I cleaned it up and I got the following that seems to work:

I've got to get my head away from code for a little bit, so I'm going to head out to lunch, but I truly do appreciate the help!

        'define data for slide
        Set rs = CurrentDb.OpenRecordset("SELECT * from qryPPPilotExportFINAL WHERE Months <= 1")
        
        rs.MoveFirst
        While Not rs.EOF

        'create slide
'==========================
'Below Part of my solution
'==========================
'CreateSlidePP1:
'==========================
'Above Part of my solution
'==========================

        'This calls a subfunction that creates the slide with x as the index number, and slide template as "PP"
        InsertSlide ppPres, x, "PP"
            
            'Customize the Slide's Text
            With .Slides(x)
                'layout titles
                .Shapes(1).TextFrame.TextRange.Text = "Permanent Party"
                .Shapes(2).TextFrame.TextRange.Text = "1 Month from Expiration"
                .Shapes(4).TextFrame.TextRange.Text = footerData1
                .Shapes(5).TextFrame.TextRange.Text = footerData2
                .FollowMasterBackground = msoFalse
                'Red background fill
                .Background.Fill.ForeColor.RGB = RGB(255, 0, 0)
                
                'insert data
                y = 3 'Starts us on the third row of that table on the slide
                record = 1 'Sets the record count to 1
                
                Do While Not rs.EOF 'as long as there is another person
                    .Shapes(3).Table.Cell(y, 1).Shape.TextFrame.TextRange.Text = IfNull(rs.Fields("Name"), "-")
                    .Shapes(3).Table.Cell(y, 2).Shape.TextFrame.TextRange.Text = IfNull(rs.Fields("ZoneEnter"), "-")
                    .Shapes(3).Table.Cell(y, 3).Shape.TextFrame.TextRange.Text = IfNull(rs.Fields("ZoneExit"), "-")
                    .Shapes(3).Table.Cell(y, 4).Shape.TextFrame.TextRange.Text = IfNull(rs.Fields("Check"), "-")
                    .Shapes(3).Table.Cell(y, 5).Shape.TextFrame.TextRange.Text = IfNull(rs.Fields("N/N"), "-")
                    .Shapes(3).Table.Cell(y, 6).Shape.TextFrame.TextRange.Text = IfNull(rs.Fields("Spot"), "-")
                    .Shapes(3).Table.Cell(y, 7).Shape.TextFrame.TextRange.Text = IfNull(rs.Fields("IRCTest"), "-")
                    .Shapes(3).Table.Cell(y, 8).Shape.TextFrame.TextRange.Text = IfNull(rs.Fields("TacticsTest"), "-")
                    .Shapes(3).Table.Cell(y, 9).Shape.TextFrame.TextRange.Text = IfNull(rs.Fields("OpenTest"), "-")
                    .Shapes(3).Table.Cell(y, 10).Shape.TextFrame.TextRange.Text = IfNull(rs.Fields("ClosedTest"), "-")
                    y = y + 1
                    
                    'move to next person
                    rs.MoveNext
                    
                    'add one to the record count
                    record = record + 1
                    
                    If record > 10 Then Exit Do
                    
                    '==========================
                    'Below Part of my solution
                    '==========================
                    'If record Mod 3 = 0 Then
                        'Create a new slide
                        'GoTo CreateSlidePP1
                    'Else
                        'rs.MoveNext  'move to next person
                    'End If
                    '==========================
                    'Above Part of my solution
                    '==========================
                Loop
                
                
            End With
        x = x + 1
        Wend
        'close dataset for 1st slide
        rs.Close

 

[GolferGuy]

Russ,
You still need to initialize record = 1 right after the start of the While Not rs.EOF