Combo box values dependent on record set in Listbox

[brharrii]

Access 2010
windows 7

The database I'm working on stores product records. To help the user narrow down which product they want to use, the navigation form has a listbox that looks to several combox values to filter the records it displays. As the user selects values for additional comboxes, the list of products from the listbox is refined. What I would like to do is set the comboxes up so that they also have to check the listbox to determine which records they should display.

For example:

Comboxes: Customer, Species

Starting out the listbox shows all products. The user wants to find a particular product that is sold to "Harly Quinn's Crab Imporium". They select "Harly Quinn's Crab Imporium" from the customer combobox. The listbox updates to show only products sold to Harly Quinn. The database currently has this functionality. What I want to add comes next:

The user determines that there are still too many records being displayed in the listbox, so he/she attempts to refine the search further by selecting a species from the species combobox. Currently all species from the species table are selectable from the species combobx, meaning that if the user selects a species that isn't sold to Harly Quinn, that the listbox will show no records. What I would like the combobox to do is refer first to the listbox and determine which species are still viable options based on the records available from the listbox.

Does anyone have any suggestions or sample database i could play with to help me figure this out?

Thanks!

 

[Phenomena]

I have a similar setup on my form, where the user selects items in a combobox which updates the contents of other comboboxes on the form. It's not exactly the same as you but it shouldn't be too difficult to modify it to fit your needs.

Essentially what I've done is for every ComboBox is have some code that gets executed after every ComboBox update.

Public Sub UpdateVendors()
    With Me.cboVendor
        If IsNull(Me!cboJob) Then
        .RowSource = ""
        Else
          .RowSource = "SELECT [ID], [Vendor]" & _
                   "FROM Vendor " & _
                   "WHERE [Job]=" & Me.cboJob
        End If
        Call .Requery
    End With
End Sub

This code gets executed when the cboJobs is updated. If the job selected is empty then the cboVendors RowSource is blanked, if it's not empty then the Vendor choices are updated to match the Job selection.