ok i did it. might be a little hokey but it works. maybe with the code i wrote it will give you ideas so you can devise something else if you feel like it.
Ok here's the gist: you have to use DAO recordsets so you can count the number of records (so you can figure out how many pages of 4 postcards you'll have), and so you can loop thru the records and calculate a new Order.
You'll have to tweak the code as necessary to fill in your table and field names:
My table is called SEQTest.
I have a field called SeqNo (integer 1 thru the end)
This would be your table you talk about.
I made a second table called SEQTestTemp.
It has a field called SeqNoTemp (integer).
It has a 2nd field called OrderTemp (integer).
The table is blank.
So the code counts the number of records; if it's a multiple of 4, great. If not, it figures out the next higher number that IS a multiple of 4. Long story, but if you don't do this, it honks up the order.
Next, it divides the number of records figured above by 4 to get how many pages of 4 postcards you'll have.
Then it puts a list in the TEMP table: it puts the 'real' Seq Number in the SeqNoTemp field (1 thru whatever), and next to each SeqNoTemp it puts the newly-calculated ReportOrder. I'm not gonna explain how I figured that out cause I have 11 pieces of paper with chicken scratches all over them in front of me...
Anyhow, create a new MODULE and paste in this code, tweaking as necessary:
Code:
Function DetermineReportOrder()
Dim rs, rsTemp As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SeqTest")
Dim intRecordCount, intPageCount, i, j As Integer
'Determine Recordcount. If it's not a multiple of 4, make it be the next higher multiple of 4
'For example if there are only 26 records, make the recordcount we use be 28 (a multiple of 4)
If rs.RecordCount Mod 4 = 0 Then
intRecordCount = rs.RecordCount
Else
intRecordCount = Int((rs.RecordCount + 4) / 4) * 4
End If
intPageCount = intRecordCount / 4
CurrentDb.Execute ("Delete * from SeqTestTemp")
Set rs = Nothing
Set rs = CurrentDb.OpenRecordset("SeqTestTemp")
For j = 1 To intRecordCount
rs.AddNew
rs!SeqNoTemp = j
If ((j - 1) / intPageCount) = Int((j - 1) / intPageCount) Then
rs!OrderTemp = Int(j / intPageCount) + 1
i = Int(j / intPageCount) + 1
Else
rs!OrderTemp = i + 4
i = i + 4
End If
rs.Update
Next j
Set rs = Nothing
End Function
Make sure you have a reference to DAO 3.6 set in your REFERENCES.
Run the code (if you don't know how, just put your cursor in front of the "F" in Function and hit the RUN button). It will fill up the TEMP table with a list of Seq Numbers (as if there are a full multiple of 4 of them) and their cooresponding new Order.
Then in your report, go into the RecordSource. Bring in the two tables (your existing one plus the new TEMP one). Make a join between the two tables, on SeqNoTemp and SeqNo, with the arrow pointing to the SeqNo in the pre-existing table. In the query grid, bring down the OrderTemp field and sort ascending on it. Don't bring the 'real' sequence number into the query grid, or if you do, don't sort it in any way. You want the sort to be on the OrderTemp field.
So try that and let me know how it goes.
g
)