Filtering form by joined look-up field in a subform

[Sam_NY]

Can't quite wrap my head around this. I'd like to filter a form based on a field in the subform that is itself a lookup field from a third table. I was trying to use the combo box / AfterUpdate method described here: http://allenbrowne.com/ser-28.html

Which suggests the following SQL in the After Update field for the combo box:

Dim strSQL As String
If IsNull(Me.cboShowSup) Then
    ' If the combo is Null, use the whole table as the RecordSource.
    Me.RecordSource = "tblProduct"
Else
    strSQL = "SELECT DISTINCTROW tblProduct.* FROM tblProduct " & _
        "INNER JOIN tblProductSupplier ON " & _
        "tblProduct.ProductID = tblProductSupplier.ProductID " & _
        "WHERE tblProductSupplier.SupplierID = " & Me.cboShowSup & ";"
    Me.RecordSource = strSQL
End If

But I'm not sure how to write in the other join. So, using this example, it would be like if tblProductSupplier.SupplierID was joined to a third table--let's say tblOtherTable.SupplierIDs



Also the above example specifies tblProductSupplier as Row Source in Properties for the Combo Box, but I think I should put tblOtherTable for my situation (yes?).

The result I'm looking for is when I enter or choose the SupplierID from the combo box, it filters the records in the main form to show only those with subform records matching that ID. Hopefully I'm asking this clearly but let me know if not. Thanks.

[Gicu]

I suggest you build a query using your three tables that gives you the right records (look up query by form to see how to build one that uses the subform controls as parameters). Once you have it return the right records save it and use that in the Else part of the code you posted,

If IsNull(Me.cboShowSup) Then
    ' If the combo is Null, use the whole table as the RecordSource.
    Me.RecordSource = "tblProduct"
Else
    'strSQL = "SELECT DISTINCTROW tblProduct.* FROM tblProduct " & _
        "INNER JOIN tblProductSupplier ON " & _
        "tblProduct.ProductID = tblProductSupplier.ProductID " & _
        "WHERE tblProductSupplier.SupplierID = " & Me.cboShowSup & ";"
    Me.RecordSource = "qryMyNewQueryBySupplierID"
End If

Cheers,

[ArviLaanemets]

This feels backward and awkward! Much more simple is to have main form (bound or unbound) where you select supplier, and a continuous subform with form based on tblProductSupplier as source. You link subform with main form through supplier ID in both tables (bound main form) or through supplier combo box in main form, and supplier ID in tblProductSupplier (unbound main form).

As result:
Subform displays only and all products registered as supply by supplier active in main form;
Any product added into subform will be automatically linked with supplier active in main form (NB! This means you have to hide the control linked to supplier ID in subform to disable any possibility for users to mess up with it. At same time, the control must exist. as supplier ID is entered into control, but is stored in table only, when the record is saved. Btw, this allows user to cancel the new unsaved entry into subform at will pressing Esc - without creating any orphan entries!).

With this solution there is no need for VBA.

[Sam_NY]

Thanks, Vlad. I'm giving this a shot. I do already have a query that returns the right records, but was having trouble figuring out how to put that into the Else statement. Will revisit though!

[Sam_NY]

Thanks ArvilLannemets. I get what you're saying and will likely create some version of what you're saying (it does make a lot of sense). And I know it seems backward, but I guess the point of the filter I'm trying to create is for when a user is navigating the main form and decides they want to see, in that form, only the products that have a certain supplier. For the purpose of my post, I used the example from the site I linked to, but in my actual use it makes sense for the users' workflows. I guess I could open the main form to the relevant record from the new form you suggest (which...also new to me!).

[Gicu]

Hi Sam,

If the combo is on the subform I think you will need to change the code to Me.Parent.Form.RecordSource = "qryMyNewQueryBySupplierID". And be aware that changing the recordsource will do an automatic requery so the main form will jump to the first record returned by the new query.

Cheers,