Repeating values in combo boxes (1 Viewer)

[daveUK]

I've set up a database that allows users to view, store and categorise digital photographs. I'm creating a search form that allows users to search the database by Category/Sub-Category or both.
On the search form the user is presented with 3 checkboxes labeled Category, Sub-Category and Both. When the user selects an option the relevant combo box is displayed and they can select the category/sub-Category/both that they wish to view.
They can then press a cmd button that displays ANOTHER 3 checkboxes, again labeled Category, Sub-Category and Both. After they select an option the relevant combo box is displayed an the user makes their selection from the drop-down menu. This allows users to search multiple categories/sub-categories or a mixture of the two at once.
My problem is that if a user, for example, selects the category 'Family' from the 1st set of combo boxes, if they choose to search a category from the 2nd set of combo boxes, the value 'Family' is still available!
Is there anyway of "deleting" the value chosen from the 1st combo box so it doesn't appear in the 2nd???

Dave

 

[SimonC]

For each of the combo boxes, change the row source to be a query which includes a WHERE clause along the lines of

WHERE Category <> Forms!MyForm!ComboBox

The query behind the first combo would reference the value in the second combo and vice versa. In the AfterUpdate event of the first combo you would need to requery the second combo and vice versa.

Simon

 

[daveUK]

Simon
Thanks for the reply, unforunately that code only makes ALL of the selections/categories disappear in the 2nd combo after a value has been chosen in the first.