Filtering

[Steven19]

I have a main form. It has a sub form and a sub report.



The main form is a Single Form
The sub form is Continuous Form
The Sub Report is a Report.

The main form Primary Key links to the Sub Form Foreign Key.

The Sub Report is based on a query that creates the sub form.

I want a user to click a field on the sub form and the sub report displays filtered results.

Can someone walk me through the VBA to do this please?

[June7]

See if this example helps. If you need more help, provide your attempted code or attach your db for analysis.

Private Sub tbxUmpID_Click()
Me.Parent.ctrComp.Report.FilterOn = False
Me.Parent.ctrComp.Report.Filter = "UmpID = " & Me.UmpID
Me.Parent.ctrComp.Report.FilterOn = True
End Sub

[canyonraven]

Clarifying question: Are you trying to filter the table/query behind the sub form based on values in the parent form? If so, I would typically use an unbound field in the form and execute an event "On enter" that queries the data behind the sub form using the unbound field as a parameter in the query. There isn't much VBA required. Just a DoCmd.RunQuery "qry_myParameterQuery" and include the unbound control as a parameter in your query.

[canyonraven]

In the approach I described, the parent form is unbound. This technique is described in the detail here:
https://www.oreilly.com/library/view/access-cookbook/0596000847/ch01s02.html

[Steven19]

Hi June

I kept getting a runtime error with the code you provided as an example, so I amended it to just an on click even with the
Me.Parent.ctrComp.Report.Filter = "UmpID = " & Me.UmpID (customised)
This works and I can click record after record and have no issue and no runtime.

Thank you.