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

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

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.

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!

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

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.

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.

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

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

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.

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 = 1Make it look like this: rs.MoveNext 'move to next person
x = x + 1
z = z + 1
If z > 12 Then Exit Do
LoopThe z = 1 does not have to be here because that is the first thing done within the While/Wend loop

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

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