Printing report in booklet format

  • Thread starter Thread starter JBeskid
  • Start date Start date
J

JBeskid

I have created application to populate and print report cards. Recently the
school has purchased a new Xerox printer that allows them to print the report
cards in booklet format. In the current application, you select the home room
number that you want to print from a form and all the report cards are
printed for each student - i.e. the whole file is printed as 1 report / 1
booklet. With the new printer, I need to create a break so that the printer
recognizes each student as a seperate file and will print this as 1 booklet.

I am hoping that this problem can be solved with our writing code as that is
not my strength. Any help would be appreciated.
 
I can't think of a way other than to use VBA and loop through the students
printing one report per student.

That code should not be too difficult to do. I might take a stab at it this
weekend, if you will post the current query that you are using as the source
for your current report.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks John. Below is the Sql you requested. Any help would be greatly
appreciate.

SELECT T_Student.HR, T_Student.Lname, T_Student.Fname, T_Student!Fname & " "
& T_Student!Lname AS Name, T_Student.Grade
FROM T_Student
GROUP BY T_Student.HR, T_Student.Lname, T_Student.Fname, T_Student!Fname & "
" & T_Student!Lname, T_Student.Grade
HAVING (((T_Student.HR)=[Forms]![F_ReportsCards]![cmbHR]) AND
((T_Student.Lname) Like [Forms]![F_ReportsCards]![cmbStudent] & "*"))
ORDER BY T_Student.HR, T_Student.Lname;
 
Based on that query, you could probably use a sub like the following. Watch
out for line wrap in the newsreader inserting extra lines.

One thing to watch out for is students with the same First and Last name in
the same homeroom. A rare occurence, but it could happen. You would be safer
with a unique identifier (student id number) or just an autonumber to identify
each student record. That way there would be no confusion within the database.

You can put this sub into the form's code module or you could insert the code
minus the first and last lines into the click event of a button on your form
F_ReportsCards.

If you do put it into the form's module, you can call it from the click event
of the button

WARNING: The following code will compile but it has not been executed to run.

Also make sure you change the line
DoCmd.OpenReport "Name_Of_Your_Report", acViewNormal, , strWhere
and insert the actual name of your report between the quotes.

Good luck.

Sub sPrintReportCards()
Dim strWhere As String
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

'============================================================================
' Step 1 construct a recordset of all students meeting the criteria
'============================================================================
strSQL = "SELECT Distinct T_Student.HR, T_Student.Lname" & _
", T_Student.FName" & _
" FROM T_Student" & _
" WHERE T_Student.HR=""" & [Forms]![F_ReportsCards]![cmbHR] & """" & _
" AND T_Student.Lname Like """ & _
[Forms]![F_ReportsCards]![cmbStudent] & "*""" & _
" ORDER BY T_Student.HR, T_Student.Lname, T_Student.FName"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)

'============================================================================
' Step 2 loop through all the records that were returned in the recordset
'============================================================================
If rst.RecordCount < 1 Then
MsgBox "Sorry no match for that name and Homeroom combination"
Else

While Not rst.EOF
strWhere = "HR = """ & rst!HR & _
""" AND Lname = """ & rst!Lname & """" & _
""" AND FName = """ & rst!Fname & """"

DoCmd.OpenReport "NameOfYourReport", acViewNormal, , strWhere
DoEvents 'Allow break to process data
rst.MoveNext
Wend
End If
End Sub



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks John. Below is the Sql you requested. Any help would be greatly
appreciate.

SELECT T_Student.HR, T_Student.Lname, T_Student.Fname, T_Student!Fname & " "
& T_Student!Lname AS Name, T_Student.Grade
FROM T_Student
GROUP BY T_Student.HR, T_Student.Lname, T_Student.Fname, T_Student!Fname & "
" & T_Student!Lname, T_Student.Grade
HAVING (((T_Student.HR)=[Forms]![F_ReportsCards]![cmbHR]) AND
((T_Student.Lname) Like [Forms]![F_ReportsCards]![cmbStudent] & "*"))
ORDER BY T_Student.HR, T_Student.Lname;
Click to expand...
 
OH!!! I forgot.

You need to modify the reports source to something like the following.

SELECT DISTINCT T_Student.HR, T_Student.Lname, T_Student.Fname,
T_Student!Fname & " "
& T_Student!Lname AS Name, T_Student.Grade
FROM T_Student

By the way the order by clause of a query has no effect on the order a report
is sorted in. You should use the reports Sorting and Grouping (Menu: View:
Sorting and Grouping) to set the sort order of a report.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John said:
Based on that query, you could probably use a sub like the following.
Watch out for line wrap in the newsreader inserting extra lines.

One thing to watch out for is students with the same First and Last name
in the same homeroom. A rare occurence, but it could happen. You would
be safer with a unique identifier (student id number) or just an
autonumber to identify each student record. That way there would be no
confusion within the database.

You can put this sub into the form's code module or you could insert the
code minus the first and last lines into the click event of a button on
your form F_ReportsCards.

If you do put it into the form's module, you can call it from the click
event of the button

WARNING: The following code will compile but it has not been executed to
run.

Also make sure you change the line
DoCmd.OpenReport "Name_Of_Your_Report", acViewNormal, , strWhere
and insert the actual name of your report between the quotes.

Good luck.

Sub sPrintReportCards()
Dim strWhere As String
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

'============================================================================

' Step 1 construct a recordset of all students meeting the criteria
'============================================================================

strSQL = "SELECT Distinct T_Student.HR, T_Student.Lname" & _
", T_Student.FName" & _
" FROM T_Student" & _
" WHERE T_Student.HR=""" & [Forms]![F_ReportsCards]![cmbHR] & """" & _
" AND T_Student.Lname Like """ & _
[Forms]![F_ReportsCards]![cmbStudent] & "*""" & _
" ORDER BY T_Student.HR, T_Student.Lname, T_Student.FName"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)

'============================================================================

' Step 2 loop through all the records that were returned in the recordset
'============================================================================

If rst.RecordCount < 1 Then
MsgBox "Sorry no match for that name and Homeroom combination"
Else

While Not rst.EOF
strWhere = "HR = """ & rst!HR & _
""" AND Lname = """ & rst!Lname & """" & _
""" AND FName = """ & rst!Fname & """"

DoCmd.OpenReport "NameOfYourReport", acViewNormal, , strWhere
DoEvents 'Allow break to process data
rst.MoveNext
Wend
End If
End Sub



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks John. Below is the Sql you requested. Any help would be greatly
appreciate.

SELECT T_Student.HR, T_Student.Lname, T_Student.Fname, T_Student!Fname
& " " & T_Student!Lname AS Name, T_Student.Grade
FROM T_Student
GROUP BY T_Student.HR, T_Student.Lname, T_Student.Fname,
T_Student!Fname & " " & T_Student!Lname, T_Student.Grade
HAVING (((T_Student.HR)=[Forms]![F_ReportsCards]![cmbHR]) AND
((T_Student.Lname) Like [Forms]![F_ReportsCards]![cmbStudent] & "*"))
ORDER BY T_Student.HR, T_Student.Lname;
Click to expand...
Click to expand...
 
Thanks John. I will try this, but it might not be for a little while as I
will be out of town for a week or so. I will let you know what happens as
soon as I have a chance to spend some time on this. I am sure you are right
about using a unique identifier for the student. This was something I had
planned to change.
--
JBeskid


John Spencer said:
OH!!! I forgot.

You need to modify the reports source to something like the following.

SELECT DISTINCT T_Student.HR, T_Student.Lname, T_Student.Fname,
T_Student!Fname & " "
& T_Student!Lname AS Name, T_Student.Grade
FROM T_Student

By the way the order by clause of a query has no effect on the order a report
is sorted in. You should use the reports Sorting and Grouping (Menu: View:
Sorting and Grouping) to set the sort order of a report.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John said:
Based on that query, you could probably use a sub like the following.
Watch out for line wrap in the newsreader inserting extra lines.

One thing to watch out for is students with the same First and Last name
in the same homeroom. A rare occurence, but it could happen. You would
be safer with a unique identifier (student id number) or just an
autonumber to identify each student record. That way there would be no
confusion within the database.

You can put this sub into the form's code module or you could insert the
code minus the first and last lines into the click event of a button on
your form F_ReportsCards.

If you do put it into the form's module, you can call it from the click
event of the button

WARNING: The following code will compile but it has not been executed to
run.

Also make sure you change the line
DoCmd.OpenReport "Name_Of_Your_Report", acViewNormal, , strWhere
and insert the actual name of your report between the quotes.

Good luck.

Sub sPrintReportCards()
Dim strWhere As String
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

'============================================================================

' Step 1 construct a recordset of all students meeting the criteria
'============================================================================

strSQL = "SELECT Distinct T_Student.HR, T_Student.Lname" & _
", T_Student.FName" & _
" FROM T_Student" & _
" WHERE T_Student.HR=""" & [Forms]![F_ReportsCards]![cmbHR] & """" & _
" AND T_Student.Lname Like """ & _
[Forms]![F_ReportsCards]![cmbStudent] & "*""" & _
" ORDER BY T_Student.HR, T_Student.Lname, T_Student.FName"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)

'============================================================================

' Step 2 loop through all the records that were returned in the recordset
'============================================================================

If rst.RecordCount < 1 Then
MsgBox "Sorry no match for that name and Homeroom combination"
Else

While Not rst.EOF
strWhere = "HR = """ & rst!HR & _
""" AND Lname = """ & rst!Lname & """" & _
""" AND FName = """ & rst!Fname & """"

DoCmd.OpenReport "NameOfYourReport", acViewNormal, , strWhere
DoEvents 'Allow break to process data
rst.MoveNext
Wend
End If
End Sub



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks John. Below is the Sql you requested. Any help would be greatly
appreciate.

SELECT T_Student.HR, T_Student.Lname, T_Student.Fname, T_Student!Fname
& " " & T_Student!Lname AS Name, T_Student.Grade
FROM T_Student
GROUP BY T_Student.HR, T_Student.Lname, T_Student.Fname,
T_Student!Fname & " " & T_Student!Lname, T_Student.Grade
HAVING (((T_Student.HR)=[Forms]![F_ReportsCards]![cmbHR]) AND
((T_Student.Lname) Like [Forms]![F_ReportsCards]![cmbStudent] & "*"))
ORDER BY T_Student.HR, T_Student.Lname;
Click to expand...
Click to expand...
.
Click to expand...
 
--
JBeskid


John Spencer said:
OH!!! I forgot.

You need to modify the reports source to something like the following.

SELECT DISTINCT T_Student.HR, T_Student.Lname, T_Student.Fname,
T_Student!Fname & " "
& T_Student!Lname AS Name, T_Student.Grade
FROM T_Student

By the way the order by clause of a query has no effect on the order a report
is sorted in. You should use the reports Sorting and Grouping (Menu: View:
Sorting and Grouping) to set the sort order of a report.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John said:
Based on that query, you could probably use a sub like the following.
Watch out for line wrap in the newsreader inserting extra lines.

One thing to watch out for is students with the same First and Last name
in the same homeroom. A rare occurence, but it could happen. You would
be safer with a unique identifier (student id number) or just an
autonumber to identify each student record. That way there would be no
confusion within the database.

You can put this sub into the form's code module or you could insert the
code minus the first and last lines into the click event of a button on
your form F_ReportsCards.

If you do put it into the form's module, you can call it from the click
event of the button

WARNING: The following code will compile but it has not been executed to
run.

Also make sure you change the line
DoCmd.OpenReport "Name_Of_Your_Report", acViewNormal, , strWhere
and insert the actual name of your report between the quotes.

Good luck.

Sub sPrintReportCards()
Dim strWhere As String
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

'============================================================================

' Step 1 construct a recordset of all students meeting the criteria
'============================================================================

strSQL = "SELECT Distinct T_Student.HR, T_Student.Lname" & _
", T_Student.FName" & _
" FROM T_Student" & _
" WHERE T_Student.HR=""" & [Forms]![F_ReportsCards]![cmbHR] & """" & _
" AND T_Student.Lname Like """ & _
[Forms]![F_ReportsCards]![cmbStudent] & "*""" & _
" ORDER BY T_Student.HR, T_Student.Lname, T_Student.FName"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)

'============================================================================

' Step 2 loop through all the records that were returned in the recordset
'============================================================================

If rst.RecordCount < 1 Then
MsgBox "Sorry no match for that name and Homeroom combination"
Else

While Not rst.EOF
strWhere = "HR = """ & rst!HR & _
""" AND Lname = """ & rst!Lname & """" & _
""" AND FName = """ & rst!Fname & """"

DoCmd.OpenReport "NameOfYourReport", acViewNormal, , strWhere
DoEvents 'Allow break to process data
rst.MoveNext
Wend
End If
End Sub



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks John. Below is the Sql you requested. Any help would be greatly
appreciate.

SELECT T_Student.HR, T_Student.Lname, T_Student.Fname, T_Student!Fname
& " " & T_Student!Lname AS Name, T_Student.Grade
FROM T_Student
GROUP BY T_Student.HR, T_Student.Lname, T_Student.Fname,
T_Student!Fname & " " & T_Student!Lname, T_Student.Grade
HAVING (((T_Student.HR)=[Forms]![F_ReportsCards]![cmbHR]) AND
((T_Student.Lname) Like [Forms]![F_ReportsCards]![cmbStudent] & "*"))
ORDER BY T_Student.HR, T_Student.Lname;
Click to expand...
Click to expand...
.
Click to expand...
 
--
JBeskid


JBeskid said:
--
JBeskid


John Spencer said:
OH!!! I forgot.

You need to modify the reports source to something like the following.

SELECT DISTINCT T_Student.HR, T_Student.Lname, T_Student.Fname,
T_Student!Fname & " "
& T_Student!Lname AS Name, T_Student.Grade
FROM T_Student

By the way the order by clause of a query has no effect on the order a report
is sorted in. You should use the reports Sorting and Grouping (Menu: View:
Sorting and Grouping) to set the sort order of a report.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John said:
Based on that query, you could probably use a sub like the following.
Watch out for line wrap in the newsreader inserting extra lines.

One thing to watch out for is students with the same First and Last name
in the same homeroom. A rare occurence, but it could happen. You would
be safer with a unique identifier (student id number) or just an
autonumber to identify each student record. That way there would be no
confusion within the database.

You can put this sub into the form's code module or you could insert the
code minus the first and last lines into the click event of a button on
your form F_ReportsCards.

If you do put it into the form's module, you can call it from the click
event of the button

WARNING: The following code will compile but it has not been executed to
run.

Also make sure you change the line
DoCmd.OpenReport "Name_Of_Your_Report", acViewNormal, , strWhere
and insert the actual name of your report between the quotes.

Good luck.

Sub sPrintReportCards()
Dim strWhere As String
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

'============================================================================

' Step 1 construct a recordset of all students meeting the criteria
'============================================================================

strSQL = "SELECT Distinct T_Student.HR, T_Student.Lname" & _
", T_Student.FName" & _
" FROM T_Student" & _
" WHERE T_Student.HR=""" & [Forms]![F_ReportsCards]![cmbHR] & """" & _
" AND T_Student.Lname Like """ & _
[Forms]![F_ReportsCards]![cmbStudent] & "*""" & _
" ORDER BY T_Student.HR, T_Student.Lname, T_Student.FName"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)

'============================================================================

' Step 2 loop through all the records that were returned in the recordset
'============================================================================

If rst.RecordCount < 1 Then
MsgBox "Sorry no match for that name and Homeroom combination"
Else

While Not rst.EOF
strWhere = "HR = """ & rst!HR & _
""" AND Lname = """ & rst!Lname & """" & _
""" AND FName = """ & rst!Fname & """"

DoCmd.OpenReport "NameOfYourReport", acViewNormal, , strWhere
DoEvents 'Allow break to process data
rst.MoveNext
Wend
End If
End Sub



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

JBeskid wrote:
Thanks John. Below is the Sql you requested. Any help would be greatly
appreciate.

SELECT T_Student.HR, T_Student.Lname, T_Student.Fname, T_Student!Fname
& " " & T_Student!Lname AS Name, T_Student.Grade
FROM T_Student
GROUP BY T_Student.HR, T_Student.Lname, T_Student.Fname,
T_Student!Fname & " " & T_Student!Lname, T_Student.Grade
HAVING (((T_Student.HR)=[Forms]![F_ReportsCards]![cmbHR]) AND
((T_Student.Lname) Like [Forms]![F_ReportsCards]![cmbStudent] & "*"))
ORDER BY T_Student.HR, T_Student.Lname;
Click to expand...
.
Hi John
Click to expand...
Click to expand...
I have finally had a chance to test the code you provided and I am having a
problem in step 2. Syntax error (missing operator) in query expression
'(HR="700-01"" AND Lname ="Byren"" and Fname = "William")'.

I am sure it must have to do with the quotation marks, but I can't figure
out what I am doing wrong.

Private Sub PrintReportCardBooklets_Click()
Dim strWhere As String
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
'============================================================================
' Step 1 construct a recordset of all students meeting the criteria
'============================================================================
strSQL = "SELECT Distinct T_Student.HR, T_Student.Lname" & _
", T_Student.FName" & _
" FROM T_Student" & _
" WHERE T_Student.HR=""" & [Forms]![F_ReportsCards]![cmbHR] & """" & _
" AND T_Student.Lname Like """ & _
[Forms]![F_ReportsCards]![cmbStudent] & "*""" & _
" ORDER BY T_Student.HR, T_Student.Lname, T_Student.FName"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
'============================================================================
' Step 2 loop through all the records that were returned in the recordset
'============================================================================
If rst.RecordCount < 1 Then
MsgBox "Sorry no match for that name and Homeroom combination"
Else
While Not rst.EOF

'Debug.Print rst("HR"), rst("Lname"), rst("Fname")

strWhere = "HR = """ & rst!HR & """" & _
""" AND Lname = """ & rst!Lname & """" & _
""" AND FName = """ & rst!Fname & """"

DoCmd.OpenReport "R_6MarksPFE", acViewNormal, , strWhere

DoEvents 'Allow break to process data
rst.MoveNext
Wend
End If
End Sub

Also, I am not sure what you mean when you say I need to modify the report
source. I did drop the order by clause because it was already in the report.

Thanks much for your help.
 
Looks like too many quotes - Sorry I thought I tested this better than I did.

strWhere = "HR = """ & rst!HR & """" & _
" AND Lname = """ & rst!Lname & """" & _
" AND FName = """ & rst!Fname & """"

IF you use Debug.Print strWhere in your code you should see
HR="700-01" AND LName="Byren" And FName="William"

If you see something different, you will need to play with the strWhere to get
the results correct.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

JBeskid wrote:
 
Thanks John. I just found the problem and everything seems to be working as
planned. You saved my life. There is a special place for you in heaven.
 
Back
Top