sub combobox for record only shows unused values in form

[sponge7]

Hi,

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.

Through the help of some people, I have been able to limit the subCategory combo box to what hasn't been used in the form yet. Unfortunately, the solution I am using breaks when the subCategory value is the same for two mainCategories.

mainCategory and subCategory are based on one table:

tblCategories
---------------
main | sub (fields)
A 1
A 2
A 3
B I
B II
B III
C 1
C 2

The Row Source for the mainCategory combobox uses the following SQL statement:
SELECT DISTINCT tblCategories.main
FROM tblCategories;

The Row Source for the subCategory combobox uses the following SQL statement:

SELECT tblCategories.Task
FROM tblCategories LEFT JOIN qryForm ON tblCategories.sub = qryForm.sub
WHERE (((qryForm.sub) Is Null) AND (([mainCategory].[Value])=[tblCategories].[main]));

An example of when this solution breaks is the following scenario:

1. The user selects "A" in mainCategory and then "1" in subCategory on the first record.
2. The user creates a new record.
3. The user selects "A" in mainCategory and only has "2" and "3" available for selection in subCategory, so the user selects "3".
4. The user creates another new record (the third)
5. The user selects "C" in mainCategory and then notices that only "2" available for selection.

For the selection of "C" of mainCateogry, the user should have "1" and "2" available, but the solution I used only shows "2". "1" isn't available because it was already "non-null"...

Is there a way to fix this? I was thinking along the lines of somehow tying the subCategory to the mainCategory for the subCategory's SQL statement, but wasn't sure how.

Any help would be much, much appreciated!

 

[PHV]

And this ?
SELECT tblCategories.Task
FROM tblCategories LEFT JOIN qryForm
ON tblCategories.main=qryForm.main AND tblCategories.sub=qryForm.sub
WHERE qryForm.sub Is Null AND tblCategories.main=[mainCategory];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sponge7]

Thanks very much! Works like a charm