can't select from list

[captain1974]

I have created a form and subform based on 2 tables. I have filtered the selection on the subform based on the entry on the main form, using the code:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

    sBlockSource = "SELECT [tblBlock].[VineyardIDb], [tblBlock].[BlockName], [tblBlock].[Variety] " & _
                "FROM tblBlock " & _
                "WHERE [VineyardIDb] = " & Me.Parent.Combo5.Value
    Me.Combo6.RowSource = sBlockSource
    Me.Combo6.Requery
End Sub

I have 2 combo boxes, 1 on the main form, 1 on the subform. By selecting from the main form combo box, the subform selection is filtered.
This all works fine except for the subform record actually selected will not alter from the first record on the combo box. i.e I cannot select from the list. I am going to be building additional parts to the form, but wanted to get this part working first. Can anyone help?
I've attached the relevant part of the database.

 

[captain1974]

I'm still stuck with this.
Any ideas?

 

[MarkK]

Here're some ideas.
Also, search for Cascading Combos in this forum for more solutions.

 

[captain1974]

Here're some ideas.
Also, search for Cascading Combos in this forum for more solutions.

Thank you very much lagbolt, your example does exactly what I am trying to achieve. I shall do some more searching and study your example so that I fully understand how to achieve this.