Access 97 Click button in form and view the report of a selected record in sub-form

[Céline Brien]

Hi everybody,
I would like to open a report from a form.
I create a bouton, see below codes ONE.
These codes show all records in report.
I would like to view only the selected record in the sub-form.
I tried to add a filter to my codes. See below codes TWO.
I get an error on Me.NoServices.
NoServices is a field of the sub-form NOT the form.
The button is create in the form.
Access can't find the NoServices.
What do you suggest ?
Many thanks,
Céline

Codes ONE
-----------------------------------------------------------
Private Sub cmdFacture_Click()
On Error GoTo Err_cmdFacture_Click
Dim stDocName As String
stDocName = "EFacture"
DoCmd.OpenReport stDocName, acPreview
Exit_cmdFacture_Click:
Exit Sub
Err_cmdFacture_Click:
MsgBox Err.Description
Resume Exit_cmdFacture_Click
End Sub
Codes TWO
-----------------------------------------------------------
Private Sub cmdFacture_Click()
On Error GoTo Err_cmdFacture_Click
Dim stDocName As String
Dim strFiltre As String
strFiltre = "noServices= " & Me.NoServices
stDocName = "EFacture"
DoCmd.OpenReport stDocName, acPreview, , strFiltre
Exit_cmdFacture_Click:
Exit Sub
Err_cmdFacture_Click:
MsgBox Err.Description
Resume Exit_cmdFacture_Click
End Sub

 

[Céline Brien]

Hi everybody,
I found the answer.
I only add to add the name of the subform.
strFiltre = "NoServices= " &
Me.[SousFormulaireServices].Form![NoServices]
Have a good day !
Céline

 

[PC Datasheet]

Make a copy of the query your form is based on. Put the following expression in
the criteria of the primary key:
Forms!NameOfYourForm!NameOfPrimaryKeyField
Base your report on this query.

 

[Céline Brien]

Hi PC Datasheet,
Many thanks for your answer.
I would be please to follow your suggestion, but would you be kind
enough to explain why I should follow this procedure ?
I like to understand and, here, at first site, I don't.
Many thanks again,
Céline

 

[Mark]

Your form is based on a query that restricts the data from the data source to
only the records that match whatever criteria you set in the query. If you have
the form's navigation buttons turned on, you will see there are multiple records
and that the record on the screen is only one of the records. The record on the
screen has a unique primary key.

So to print the record on the screen you need to draw a record from the same
query that the form is based on. That's why you use the same query as the form.
Then to limit the report to the record on the screen, your report query needs to
be restricted (set criteria) to the record that has the same primary key as the
record on the screen. That's why you set the criteria as I specified for the
primary key.

 

[Céline Brien]

Hi Mark,
Thank you for your answer.
I will study your it and come back if I have any question.
But actually, my new codes do the job perfectly.
Céline
-----------------------------------
Option Compare Database
Private Sub cmdFacture_Click()
On Error GoTo Err_cmdFacture_Click
Dim stDocName As String
Dim strFiltre As String
strFiltre = "NoServices= " &
Me.[SousFormulaireServices].Form![NoServices]
stDocName = "EFactureAuto"
DoCmd.OpenReport stDocName, acPreview, , strFiltre
Exit_cmdFacture_Click:
Exit Sub
Err_cmdFacture_Click:
MsgBox Err.Description
Resume Exit_cmdFacture_Click
End Sub