Printing multiple identical reports within a page

[efreet29]

How do you print out same reports within one page?
I need to print out 3 same slips on a pre-watermarked paper. Any input will be appreciated. Thanks!

[June7]

I do something similar. I print a set of cards that are duplicates of one record. I do this with VBA procedure that copies duplicate records into a 'temp' table. Each record is a card.

[efreet29]

I do something similar. I print a set of cards that are duplicates of one record. I do this with VBA procedure that copies duplicate records into a 'temp' table. Each record is a card.

Thanks June7. Could you please elaborate more on the VBA procedure? Also, how do you make sure each card does not go out of its limited range? (i.e. Each card space can hold 4 records on the watermarked paper. So if there are 5 records, it needs to create another 3 set of cards instead of putting 5 on each card and busting out of the print range.)

[June7]

Not sure I understand what you mean by 'go out of its limited range' and 'Each card space can hold 4 records'. Sounds like you might not be doing what I thought you were.

In my solution I want a set of 4, 12, or 24 (depends on where the procedure is initiated) cards for one record's data all printed on one sheet of paper - these cards (really labels) are about 1" by 2.5". This procedure makes duplicate records in temp table using SQL INSERT action. There would be x number identical records of the same record data. Then the report has column settings (I go across then down in landscape).

[efreet29]

Not sure I understand what you mean by 'go out of its limited range' and 'Each card space can hold 4 records'. Sounds like you might not be doing what I thought you were.

In my solution I want a set of 4, 12, or 24 (depends on where the procedure is initiated) cards for one record's data all printed on one sheet of paper - these cards (really labels) are about 1" by 2.5". This procedure makes duplicate records in temp table using SQL INSERT action. There would be x number identical records of the same record data. Then the report has column settings (I go across then down in landscape).

Thanks for your reply again June. However, we seem to be describing different tasks.
I didn't explain my problem clearly enough. Let me try again with specific details to my entire environment.

Each time I need to print, I have unknown number of 'records' to print. The number can vary from 1 to 30, but usually 3~4. And instead of usual blank A4 papers, I have watermarked papers which format I need to print everything within. The papers will have space for about 5 records, so it's not a problem for the usual cases. But, here is my problem and the original question. I need to print 3 copies of the same reports. The reports look exactly the same, but I can't just use the printer setting to 3 papers because 1) the watermarked papers come in groups of 3 on one paper, and 2) I need to print an extra 4th report at the end of the paper describing the records' info.

So, if I need to print 4 records of lables 'ABCD', I should have on the paper a printout of ABCD, ABCD, ABCD, ABCD's info. If I have 6 records, ABCDEF, then I should have two papers: ABCDE,ABCDE,ABCDE + F,F,F, ABCDEF's info.

I hope that gave you clearer view for my problem. I need to learn 1) how to print same copies of the reports on the same paper, and 2) keep them within the print range and send to a different paper if I get more records than allowed.

Thank you for your input so far.

[June7]

Still sounds like duplicates of record printed to one sheet of paper. Difference is you need the mulitiple records in a 'collated' sequence. I have a couple of procedures that use temp tables to organize unknown number of records. One is the multiple copy labels and another is to organize records left to right (horizontally) on report. Both involve VBA code using recordsets and INSERT sql action within loop. There might be a way to do this with a series of queries but I found the VBA solution easier.

In your case, the temp table (table is actually permanent, the records are temporary) would have an autonumber field to generate unique ID. Report would order records by ID. Include 'blank' records so the spacing will always accommodate the 5 lines for the 3 sections on page. This assumes each record requires same amount of space. Won't be a simple procedure to code.

Dataset for ABCD would look like:

ID	Record
1	A
2	B
3	C
4	D
5
6	A
7	B
8	C
9	D
10
11	A
12	B
13	C
14	D
15
16	ABCD info

And for ABCDEF:

ID	Record
1	A
2	B
3	C
4	D
5	E
6	A
7	B
8	C
9	D
10	E
11	A
12	B
13	C
14	D
15	E
16	F
17
18
19
20
21	F
22
23
24
25
26	F
27
28
29
30
31	ABCDEF info

[efreet29]

Still sounds like duplicates of record printed to one sheet of paper. Difference is you need the mulitiple records in a 'collated' sequence. I have a couple of procedures that use temp tables to organize unknown number of records. One is the multiple copy labels and another is to organize records left to right (horizontally) on report. Both involve VBA code using recordsets and INSERT sql action within loop. There might be a way to do this with a series of queries but I found the VBA solution easier.

In your case, the temp table (table is actually permanent, the records are temporary) would have an autonumber field to generate unique ID. Report would order records by ID. Include 'blank' records so the spacing will always accommodate the 5 lines for the 3 sections on page. This assumes each record requires same amount of space. Won't be a simple procedure to code.

Thank you so much for your solution, June7. The duplicating records on a temp table with 'blank' records for spacing is an excellent solution. I'll have some problem coding it, but I now know exactly how to start and end this. Thanks

[June7]

Maybe this code from my db will give you some ideas.
This prints 12 labels for 1 record:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    rs.Open "SELECT LabNum, FieldNum, ProjectName, SampType, MaterialType " & _
    "FROM Projects RIGHT JOIN Submit ON Projects.ProjRecID=Submit.ProjRecID " & _
    "WHERE LabNum='" & Me.cbxLabNum & "';", cn, adOpenStatic, adLockPessimistic
    For i = 1 To 12
        cn.Execute "INSERT INTO LabelsData(LabNum, FieldNum, ProjectName, SampleType, MaterialType, ReportType) " & _
        "VALUES('" & rs!Labnum & "', '" & rs!FieldNum & "', '" & Replace(rs!ProjectName, "'", "''") & "', '" & rs!SampType & "', '" & rs!MaterialType & "', 'all')"
    Next i
    If MsgBox("Printer and paper stock ready", vbOKCancel + vbQuestion, "Labels") = vbOK Then
        DoCmd.OpenReport "SampleLabels", acViewNormal
    End If
    cn.Execute "DELETE FROM LabelsData"

This version prints for a range of records

Public Sub SampleLabels(strStart As String, strEnd As String, strSource As String)
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Dim j As Integer, i As Integer
rs.Open "SELECT LabNum, FieldNum, SampType, MaterialType, nz(MiscInfo, 'NotGroup') As ReportType, ProjectName, DateEnter, DateOut " & _
"FROM Projects RIGHT JOIN Submit ON Projects.ProjRecID=Submit.ProjRecID " & _
"WHERE " & IIf(strSource = "Labels", "", "(Not IsNull(DateEnter) And IsNull(DateOut)) AND ") & "(LabNum BETWEEN '" & strStart & "' AND '" & strEnd & "') " & _
"ORDER BY LabNum;", cn, adOpenStatic, adLockPessimistic
rs.MoveLast
rs.MoveFirst
For j = 1 To 3
    'first loop appends non-group samples, second loop appends group samples so they print grouped together
    rs.MoveFirst
    While Not rs.EOF
        If (j = 1 And rs!MiscInfo <> "MO" And rs!MiscInfo <> "UW") Or (j = 2 And rs!ReportType = "MO") Or (j = 3 And rs!ReportType = "UW") Then
            For i = 1 To IIf(rs.RecordCount = 1, 24, 4)
                cn.Execute "INSERT INTO LabelsData(LabNum, FieldNum, ProjectName, SampleType, MaterialType, ReportType) " & _
                "VALUES('" & rs!Labnum & "', '" & rs!FieldNum & "', '" & Replace(rs!ProjectName, "'", "''") & "', '" & rs!SampType & "', '" & rs!MaterialType & "', '" & rs!ReportType & "')"
            Next i
        End If
        rs.MoveNext
    Wend
Next j
rs.Close
If MsgBox(rs.RecordCount & " records selected. " & IIf(rs.RecordCount = 1, 24, 4) & " labels will print for each record." & vbCrLf & "Printer and paper stock ready?", vbOKCancel + vbQuestion, "Labels") = vbOK Then
    DoCmd.OpenReport "SampleLabels", acViewNormal
End If
cn.Execute "DELETE FROM LabelsData"
End Sub