Filter a ComboBox (1 Viewer)

[ddrew]

Im having trouble filtering a combobox on a form. I have two Comboboxes, one is called (FleaTickWorming) depending on what is elected in this cobo, will deside on what is available in the other combo which is called (Product). The form is a continuous form. The filtering works, in as far as it changes the list each time, but it also deletes the item that I have chosen on previous records if the list is different. Hope I have explained this so that you understand!

My code is as follows:

Private Sub FleaTickWorming_BeforeUpdate(Cancel As Integer)
    If Me.FleaTickWorming.Column(0) = 1 Then
        Me.Product.RowSource = "qry_FleaProducts"
    ElseIf Me.FleaTickWorming.Column(0) = 2 Then
        Me.Product.RowSource = "qry_TickProducts"
    ElseIf Me.FleaTickWorming.Column(0) = 3 Then
        Me.Product.RowSource = "qry_FleaTickProducts"
    ElseIf Me.FleaTickWorming.Column(0) = 4 Then
        Me.Product.RowSource = "qry_WormingProducts"
    End If

End Sub

 

[MarkK]

You can't really make cascading combos work in a continuous form. If you look at that form in design view, there is only one detail section, so if you set a property of a control and there are multiple instances of that section, then all instances of that control are affected.

 

[ddrew]

Thtas a bit of a blow, really wanted to cascade this, have to have a rethink!

 

[Keith Tedbury]

Something I have done before is use the combo book to order the selections in the next one. You end up with the most relevant selections at the top of the list but the list still contains everything so doesn't blank for the other records. In the second combo box dropdown list I show the field from the first dropdown as a column to help people not select the wrong thing.