Help! Report Printing Multiple Records from Subform

[Guest]

I have a button to print a check request for the record that is displayed in
the form. The main form contains all the information about the employee and
the subform contains all the school information for tutition reimburstment.

I actually have 2 questions.

1. If the subform has 2 records in it, the report is printing 4 records. If
it has 4 records it is printing 16 records. The report is squaring itself
then printing. How can I prevent this.

2. How can I have the report print only the record of the subform that the
user is on. i.e. if there are 3 records in the subform and the user wants to
print just the 1st record, how can I make that happen instead of all 3
records printing?

 

[Allen Browne]

Michael, the answer to this will depend on how your tables are connected.

Normally a main form is bound to one table (such as Orders), and the subform
is bound to a related table (such as OrderDetails) so it shows the line
items for the order in the main form. If your form is like that, you can
create a query that uses both tables, and use the query as the RecordSource
for your report.

If you are getting more records than you should open the query the report is
based on, and see if the records are doubled up there also. If they are, you
probably have another table in the query that is giving the extra records.
For example, if you had a 3rd table connected to the OrderDetails, and the
3rd table had 2 records for the order detail entry, they the query will end
up with 2 rows for that order detail.

If you don't have a 3rd table in your query, post back for other
suggestions.

To limit the report to just the record in the subform, use the subform's
primary key value in the WhereCondition of OpenReport. This example assumes
a subform control named "Sub1", containing a primary key field named
OrderDetailID:

Private Sub cmdPrint_Click()
Dim strWhere As String

With Me.[Sub1].Form
If .NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[OrderDetailID] = " & .[OrderDetailID]
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End With
End Sub

 

[Guest]

Thanks for your response Allen.
I have 4 tables. 3 of the tables are in the main form and 1 table is
referenced in the subform. The report's control source is a SQL statement
that is pulling information from 3 of the tables and a query that pulls a
semester total for reimburstment.

I will write another query for the report and have the control source just
for that query and see if that works.

Once I get that working properly, then I will work on your suggestion to
limit it to just the one record of choice.

Thank you for your suggestions. I will let you know if that works.

Michael, the answer to this will depend on how your tables are connected.

Normally a main form is bound to one table (such as Orders), and the subform
is bound to a related table (such as OrderDetails) so it shows the line
items for the order in the main form. If your form is like that, you can
create a query that uses both tables, and use the query as the RecordSource
for your report.

If you are getting more records than you should open the query the report is
based on, and see if the records are doubled up there also. If they are, you
probably have another table in the query that is giving the extra records.
For example, if you had a 3rd table connected to the OrderDetails, and the
3rd table had 2 records for the order detail entry, they the query will end
up with 2 rows for that order detail.

If you don't have a 3rd table in your query, post back for other
suggestions.

To limit the report to just the record in the subform, use the subform's
primary key value in the WhereCondition of OpenReport. This example assumes
a subform control named "Sub1", containing a primary key field named
OrderDetailID:

Private Sub cmdPrint_Click()
Dim strWhere As String

With Me.[Sub1].Form
If .NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[OrderDetailID] = " & .[OrderDetailID]
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End With
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a button to print a check request for the record that is displayed
in
the form. The main form contains all the information about the employee
and
the subform contains all the school information for tutition
reimburstment.

I actually have 2 questions.

1. If the subform has 2 records in it, the report is printing 4 records.
If
it has 4 records it is printing 16 records. The report is squaring itself
then printing. How can I prevent this.

2. How can I have the report print only the record of the subform that the
user is on. i.e. if there are 3 records in the subform and the user wants
to
print just the 1st record, how can I make that happen instead of all 3
records printing?