Dynamically Updated Combobox based upon previous selections

[deadtrees]

I have a criminal justice database that is tracking cases. Cases have Evidence and Subjects. I have a subdata sheet that is a Junction table between Evidence and Subjects (both also tables) from which I can link evidence to subjects along with a conclusion. (match, no match, can't tell).

The way this relationship is made is by selecting the evidence from a dropdown (which is a populated by a query of the evidence table for that case) and then choosing a decision of match, no match or can't tell in a subsequent field.

Using this format can be tedious for big cases and here's what I'd like to accomplish:

1) Once a piece of evidence is matched to a person, I want it to drop off the list of available evidence from the dropdown for everyone. A match of a person to the evidence will negate a decision to anyone else for that item.

2) Once a piece of evidence has a decision made for an individual (eg item1 doesn't match person1) I'd like for that piece of evidence to drop off the list for that person so that someone doesn't accidentally select the item erroneously later on and give it a different decision.

What would be the best way to accomplish this.

Here's my code for the query of the evidence if that helps:

SELECT Evidence.ID, [ItemNumber] & "," & [Lab Number]  AS EvidenceDetails
FROM Evidence
WHERE (((Evidence.CaseID)=[Forms]![CaseDetails]![SubjectsContainer].[Form]![CaseID]));

 

[John Big Booty]

Do a search on Cascading Combo Boxes, what you are trying to achieve would seem to be a variation on the theme.

Here are a couple of links that may also be useful.

1. Access Tips
2. Utter Angel DB Demos She has two cascading combo demos.