filter combo1 based another combobox

[hedge]

I have 3 comboboxes that filter a subform on afterupdate and now I need to filter the second combo based on the value selected in the first combo.
I am not sure if i need to add code to the below. Or use another event and if so what i code i need.

Private Sub RunFilter()
Dim strFilter As String
Dim bFilter As Boolean
bFilter = False
strFilter = ""

If Nz(Me.Combo0, "<All>") > "<All>" Then 'Country
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "Country = '" & Me.Combo0 & "'"
bFilter = True
End If


If Nz(Me.Combo2, "<All>") > "<All>" Then 'Port
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "Port = '" & Me.Combo2 & "'"
bFilter = True
End If


If Nz(Me.Combo4, "<All>") > "<All>" Then 'Mode
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "Mode = '" & Me.Combo4 & "'"
bFilter = True
End If


If bFilter Then
Me.subformdata.Form.OrderBy = ""
Me.subformdata.Form.Filter = strFilter
Me.subformdata.Form.FilterOn = True
Else
Me.subformdata.Form.FilterOn = False
End If

End Sub

 

[John Big Booty]

What you are looking for is a Cascading Combo Box set.

Also you might want to consider the way you are naming your fields, in almost all of your tables you have a field called ID, this is going to cause you great confusion as a reference to ID could refer to anyone of a number of fields in various tables, consider CountryID, PortID etc. I also note that the structure of you Ports table for example is ID (PK), Country, Port. Consider PortID, CountryID, Port.

You might also want to consider a naming protocol such as FRM_FormName, TBL_TableName etc. to help differentiate you various DB objects

 

[John Big Booty]

I've normalised your table structure, and made some changes to your DB.