Restrict Choices

[dkmansion]

I have a table of details (tblPDS) holding the status (statusID) of a device. When one of the records in this table for the associated device detail(PagerDetailID) is the status "Active" I would like to not allow another status record to be chosen with the same Value. This one value "Active" is the only one out of 8 status values that this is the case with.

All other values, cancelled, upgraded etc can and will have multiple records associated with the detail (tblPD)

tblPDS
PagerDetailStatusID
PagerDetailID [fkey to tblPD]
StatusID [fkey to tblStatuses]

Related to this table is the Details Table
tblPD
PagerDetailID [pkey to tblPDS]
PagerID [fkey to tblP]
Capcode
ServiceType etc

and

tblP - main table others above are related to
PagerID
...other fields not needed for this explanation

I would have excluded a value already listed by adding an index for tblPDS.PagerDetailID and tblPDS.StatusID not allowing the info to be input twice, but this will not work since other statuses can be listed more that once.

Anyone have an Idea on this? Thanks


Donald M

 

[BoxHead]

Are you using a form when you talk about the "record to be chosen"? If there is a form, what is the SQL for the query on which it is based?

If there's no form and you're letting people access the tables or queries directly, than there's very little control you have - which is why that's not recommended.

Let us know.



When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.