Printing Current Records in a Form with Sub-form

  • Thread starter Thread starter xpnovice
  • Start date Start date
X

xpnovice

Hi,

I have a form with a sub-form which may have more than a single record.

When I print the form using the Selected record option, the main form prints
fine, however all the related records in the sub-form are also printed.

Both the main and sub-forms are set to display Single form view.

Can anyone please tell me what setting I have to set to ensure only the
current record on both the main form and sub-form are printed.

Thanks

JohnH
 
Presumably the subform is bound to a table that has a primary key? If so,
you can use that key value in the WhereCondition of the OpenReport so it
only prints the one subform record.

The example assumes:
- a subform control named "Sub1";
- the primary key of the subform's table is a Number field named "ID";
- the code goes into the Click event procedure of a command button on the
main form.

Private Sub cmdPrint_Click()
With Me.[Sub1].Form
Dim strWhere As String
If .NewRecord Then
MsgBox "no record selected in the subform."
Else
strWhere = "[ID] = " & ![ID]
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End If
End With
End Sub
 
John:

Instead of printing the form open a report based on a query joining the
tables on which the main and subforms are based. In this query reference the
primary keys of the main and subform's tables as parameters:

SELECT <field list goes here>
FROM YourMainTable INNER JOIN YourSubTable
ON YourMainTable.SomeField = YourSubTable.SomeField
WHERE YourMainTable.PrimaryKey = Forms!YourMainForm!PrimaryKeyField
AND YourSubTable.PrimaryKeyField
= Forms!YourMainForm!YourSubformControl.Form!PrimaryKeyField;
 
Hi,

I should have mentioned that both the form and sub-form are drawn from
queries which draw the data from multiple tables.

Regards
JohnH
 
Back
Top