Show only unused data in combo box

[sponge]

Hi,

I'm wondering how to do the following:

I have two combo boxes that exist on a form record (i.e. two combo boxes for every record on the continuous form): mainCategory and subCategory.

Currently, I can choose a mainCategory and depending on the mainCategory, I have certain values available for the subCategory.

What I want to do is only show the values that have not been used for the subCategory (the mainCategory can always show its default value).

An example of what I mean is below...

The comboboxes and their row data:

mainCategory (combobox)
-------------
A (combobox rows)
B
C

subCategory (combobox)
-----------
for A: 1, 2, 3 (row for the selected mainCategory)
for B: I, II, III, IV
for C: red, blue

Sample scenario:
1. For the first record on the form, a user selects "A" from mainCategory and "1" from the subCategory for "A".
2. The user adds a new record to the form
3. For this new (2nd) record on the form, a user selects "A" from mainCategory and can only select "2" or "3" from the subCategory combobox. The "1" from the subCategory combobox is no longer available for choosing, unless the user changes the first record's "1" in subCategory to another value.

Any help would be much appreciated.

TIA!

 

[DataMiner]

Seems like this would work:
1. Set the rowsource for the subcategory combobox to a query that includes only values not already added to the underlying recordset. (You would need to have a table with all the allowable subcategories(1,2,3,4 etc.) to compare to the recordset you're entering into)

2. Requery the subcategory combobox on the OnCurrent event.

Hope that's clear... let me know if not.

 

[sponge]

How would you check if the value's already entered in the recordset?

 

[DataMiner]

Rowsource for the combobox would be something like:

SELECT SCTEMP.SubCat FROM SCTEMP LEFT JOIN RSTEMP ON SCTEMP.SubCat = RSTEMP.SubCat WHERE (((RSTEMP.SubCat) Is Null));

where SCTEMP contains all your possible subcategories, and RSTemp is the recordset you're adding records to.