Using subform to provide select critera for a further subform

[Widge]

I have a form which I have set up which uses a combo box to provide supplier names. When you select a supplier name, it runs a small piece of VBA SQL which populates a subform with Product Codes and descriptions.

What I am hoping to do is this, I would like the user to be able to select a record within this subform and a 2nd subform populates with a date and prices for that particular product.

The data is coming from 3 tables:

Supplier Details
Product Details
Product Prices (over time)

The first part of my attempt is fine, I can get the 1st subform to narrow down by supplier name ok. Yet I can't figure out for the life of me how I can get from clicking between records in the subform to populating the 2nd subform on the sheet. Things like AfterUpdate, SelectionChange etc don't appear to be doing anything.

In case it helps, here is the code that is populating the first subform:

 
Option Compare Database
Private Sub Form_Open(Cancel As Integer)
 
'    Call subroutine to set filter based on selected Supplier Name
    SetFilter
 
End Sub
 
Sub SetFilter()
 
    Dim LSQL  As String
 
    Me.Refresh
 
    LSQL = "SELECT * FROM SupplierDetails"
    LSQL = LSQL & " WHERE SupplierDetails.SupplierName = '" & Combo87 & "'"
 
'    Debug.Print LSQL
    Form_Maintain_SuppDet.RecordSource = LSQL
 
End Sub
 
Private Sub Combo87_AfterUpdate()
 
   ' Call subroutine to set filter based on selected Supplier
    SetFilter
 
 
End Sub
 
 

[Rabbit]

If you are on the main form, the syntax is:

Me.Subform_Name.Form.RecordSource

If you are on the subform, the syntax is:

Me.RecordSource

If you are on a different subform, the syntax is:

Me.Parent.Subform_Name.Form.RecordSource

The absolute path is:

[Forms]![Main Form]![Sub Form Object].Form.RecordSource

[Widge]

Precise and to the point! Thank you very much, I shall try that today. Much appreciated.

Making this thing is a new learning experience each day!

[Rabbit]

Precise and to the point! Thank you very much, I shall try that today. Much appreciated.

Making this thing is a new learning experience each day!

Not a problem, good luck.