Remove items from combo list when used

[Knowlton]

I have a combo box on a details subform with 8 items in the list. Each item should be used once for each record on the main form. Is there a way to remove an item from the available choices when it has been used and if so how?
Thanks!

[nico5038]

Looks to me you need to use the EXISTS clause in the combo's WHERE clause to detect or the combo's value has been used for the Mainform's record.
Assuming the Main form holds a Person with a PersonID it would look like:

select subcode from tblPersonSub where not exists (select subcode from tblPersonSub where PersonID = forms!main!personid)

getting the idea ?

Nic;o)

[Knowlton]

I think I understand what you're talking about. I've created the subquery which returns the correct records when ran independantly. When I transferred this query to the criteria of the combo's rowsource query and viewed these results, none of the items were eliminated.
Here is the subquery code:

SELECT tblTireService.TirePositionID
FROM tblTireService
WHERE (((tblTireService.ServiceDetailsID)=[me].[serviceDetailsID]));

This returns "1,2,3" which is correct.
Here is the rowsource query with the subquery criteria:

SELECT tblTirePosition.TirePositionID, tblTirePosition.TirePosition
FROM tblTirePosition
WHERE ((Not (tblTirePosition.TirePositionID)=Exists (SELECT tblTireService.TirePositionID
FROM tblTireService
WHERE (((tblTireService.ServiceDetailsID)=[me].[serviceDetailsID]));)));

This returns "1,2,3,4,5,6,7,8,9,10".
Why is it not eliminating "1,2,3"?
Thanks!

[nico5038]

The use of the "Me." doesn't work in the combo's rowsource, you should refer to the form by name like:

SELECT tblTirePosition.TirePositionID, tblTirePosition.TirePosition
FROM tblTirePosition
WHERE NOT Exists (SELECT tblTireService.TirePositionID
                  FROM tblTireService
WHERE tblTireService.ServiceDetailsID=[forms]![frmFormName]![serviceDetailsID]);
 

I've also removed the comparison, as the "NOT EXISTS" is enough.

The "Me." can be used when you "string" the SQL statement and assign it to the Me.combo in code.

Nic;o)

[Knowlton]

I pasted your SQL into the query window, supplied the variable and it returned no records.
Thanks!

[nico5038]

Hmm, confused, was this the result expected or not ?

Nic;o)

[Knowlton]

the result should have been "4,5,6,7,8,9,10"

[nico5038]

OK, guessed already that the used key was the wrong one, switch in the EXISTS clause ServiceDetailsID with TirePositionID as you need the ServiceDetails not found in the relation table for the "master" key.

Nic;o)

[Knowlton]

That's not sounding right to me. This ServiceDetailsID should have 10 TireServiceID (detail)records with one TirePositionID for each record. Currently there are 3 detail records for this ServiceDetailsID - "1,2,3". The EXISTS clause needs to find the TirePositionID's that have been used with this ServiceDetailsID to remove them from the complete list. Your suggestion, as I understand it would be looking for the ServiceDetailsID in tblTireService which would be the same for all the records.

[nico5038]

Hmm, it's a handicap not knowing the table structure, but the general idea is:
1) The combo's source table needs to hold all values (1 to 10)
2) The WHERE NOT EXISTS needs to find the used values from this range in the relation table.

So I would start with checking the SELECT in the WHERE NOT EXISTS clause to see or 1,2,3 are returned. Just use it as the combo's rowsource.

Nic;o)