Access filter main form by an entry on linked subform

Question

A table "items" contains records of lesson worksheets.

On the server, it is accessed by numerous teachers, who each have their own Access front end.

Teachers need to index their personal favourite lessons, so a local table is linked to the remota data table to do this.

A form shows the data table, and a subform on it shows the local data. The box "favourite" when checked in the subform changes as records in the master are scrolled. In other words, the link works, and I can also print a a list of favourites. What I cannot do is filter the subform to see all the favourite lessons. One can of course only filter the Main form.

I want to be able to filter the subform and see the matching records in the masterform. I assume this should be easy once you know how, but no explanation I have seen could ever be described as easy and I have not undersood any of them, even though I am reasonably competent in Access.

Answer 1

> One can of course only filter the Main form.

That is not so. This is a filtered subform in the Northwind sample application. Note the filter icon.

Answer 2

Thanks for the prompt response. Unfortunately, I cannot make it work. On the Northwind example, if you click the filter sign it allows you to filter the  orders table, not the main salesman table (as far as I can see).

For most of my fields on my main form, which is linked directly to the main table, I just put the cursor in the field and use the large filter sign at the top left of the screen. This filters the main table. What I need to do is to be able to do the SAME for the subform but see all the records of the main table for which there is an entry in the field of the subform.

I appreciate that I may be being dense, but the Northwind sample does not work for me. I click on the filter sign on the orders subform and this filters the orders, not the salesmen who have PLACED an order for the product I want to filter.

I suppose one could attach a query to a button to select those salesmen who have placed an order for a particular product and then open a duplicate form using that query - I haven't tried this but suppose it might work. However, it is obviously clunky and a bit weird. There must be an easier way!

Answer 3

You might like to take a look at FindRecord.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes illustrations of how to filter a parent on the basis of a value in subform's recordset.  Two approaches are demonstrated; one filters the parent form only, the other filters both the parent and subform.  The code for the former is:

Private Sub cboLastname_AfterUpdate()

    Const MESSAGETEXT = "No matching records found."

    Dim ctrl As Control

    Dim strFilter As String

    Set ctrl = Me.ActiveControl

    strFilter = "ProjectID IN(SELECT ProjectID " & _

"FROM ContactProjects WHERE ContactID = " & ctrl & ")"

    If Nz(ctrl, 0) = 0 Then

        ' turn off filter

        Me.FilterOn = False

    Else

        If Not IsNull(DLookup("ContactID", "ContactProjects", "ContactID = " & ctrl)) Then

            ' filter form to name selected in combo box

            Me.Filter = strFilter

            Me.FilterOn = True

        Else

            ' inform user if no matching records found and show all records

            MsgBox MESSAGETEXT, vbInformation, "Warning"

            Me.FilterOn = False

            Me.Requery

        End If

    End If

End Sub

As you can see, the essence of this is that a subquery to which the IN operator is applied is used to filter the parent form to those rows where the primary key is in the set of values returned by the subquery, which is restricted on the value selected in the combo box.

The second example, which filters both the parent and the subform, does the same with regard to the parent form but also includes additional code to filter the subform:

Private Sub cboLastname_AfterUpdate()

    Const MESSAGETEXT = "No matching records found."

    Dim ctrl As Control

    Dim strFilter As String

    Set ctrl = Me.ActiveControl

    strFilter = "ProjectID IN(SELECT ProjectID " & _

        "FROM ContactProjects WHERE ContactID = " & ctrl & ")"

    If Nz(ctrl, 0) = 0 Then

        ' turn off filter

        Me.FilterOn = False

        Me.fsubContacts.Form.FilterOn = False

    Else

        If Not IsNull(DLookup("ContactID", "ContactProjects", "ContactID = " & ctrl)) Then

            ' filter form to name selected in combo box

            Me.Filter = strFilter

            Me.FilterOn = True

            ' filter subform to selected contact

 Me.fsubContacts.Form.Filter = "ContactID = " & ctrl

Me.fsubContacts.Form.FilterOn = True

        Else

            ' inform user if no matching records found and show all records

            MsgBox MESSAGETEXT, vbInformation, "Warning"

            Me.FilterOn = False

            Me.Requery

            Me.fsubContacts.Form.FilterOn = False

        End If

    End If

End Sub

Answer 4

Thanks. Trying option A), do I assume the following?

1. This code is put in the After Update section of the "event" box in the "Properties" form for my SUBform?

B) My subform is based on a table called "links", the field of which that I want to filter is named "FAV" and the MAIN form is entitled just "2". It is not clear to me how to substitute my names in the code below, where there are three elements: ProjectID, ContactÜrojects and ConntactID

strFilter = "ProjectID IN(SELECT ProjectID " & _

"FROM ContactProjects WHERE ContactID = " & ctrl & ")"

I tried looking at your zip file FindRecord.zip, but on opening the form in design mode I could not open "Properties", and so the relevant code was hidden.

Thanks for your patience!

Answer 5

1.  The code goes in the AfterUpdate event procedure of an unbound control in the parent form, usually a combo box, analogous to the 'Filter by Contact' combo box in my form.  In my case the BoundColumn of the combo box is ContactID, but the combo box is set up to hide this column and show the contact names.

If you are unfamiliar with entering code into a form's or control's event procedures, this is how it's done in form design view:

    Select the form or control as appropriate and open its properties sheet if it's not already open.

    Select the relevant event property and select the 'build' button (the one on the right with 3 dots).

    Select Code Builder in the dialogue and click OK.  This step won't be necessary if you've set up Access to use event procedures by default.

    The VBA editor window will open at the event procedure with the first and last lines already in place.  Enter or paste in the code as new lines between these.

2.  In my case ProjectID is the name of the primary key of the parent form's table, and the corresponding foreign key in the subform's table.  ContactProjects is the name of the subform's table.  ContactID is the column in ContactProjects on which the filtering is to be applied.

Note that in my case The ContactID column is a long integer number data type.  If the column on which the filtering is to be applied is a text data type it would be necessary to wrap the value in literal quotes characters, represented by a contiguous pair of quotes characters in each case, as follows:

    strFilter = "ProjectID IN(SELECT ProjectID " & _

        "FROM ContactProjects WHERE ContactID = """ & ctrl & """)"

And similarly elsewhere in the code where necessary.