Problem with comboboxes

[kilou]

Hi,

newbie question:

I have a form that stores dates when a document has been created, reviewed and approved and next to each date, I have a combobox where I can choose the creator, reviewer or approver. People are managed T_People table with checkmarks to determine who's able to create, review or approve a document (some people can do everything). All the dates, document ID etc are managed in another table: T_Doc table.

Now obviously overtime there is some change in people. Some people will leave for example or simply skills will be modified (a creator will become reviewer etc). The problem is that for instance if a reviewer leave, I don't want it to appear anymore in the combobox....but it must remain the reviewer of docs he reviewed!! For now if I remove a reviewer in T_People, all documents that were reviewed by this person will show a blank "Reviewed by" combobox

I have checked referential integrity between T_People and T_Doc but I didn't check "Cascade Delete" or "Cascade Update". Is it the right thing to do here? Also, I tried to set the combobox attribute "Limit To List" to No but it doesn't help.

Any idea how I can keep old records unaffected if I modify/remove a person from T_People??? I'm sure there is exactly the same issue in Employee databases but I cannot find an answer to the problem, probably because I cannot clearly put correct words on it. So any help would be appreciated.

Thanks

 

[MarkK]

Your tPeople table could contain a field called 'Active' of boolean datatype. If the reviewer leaves, you set Active = false.
Now, your ComboBox.RowSource needs to be ammended to only include Active tPeople...

cbo.RowSource = "SELECT id, name FROM tPeople WHERE Active;"

So your historical people are preserved in the table, but you can no longer select them.

 

[kilou]

Thanks for the suggestion lagbolt. However I still get the same result: if a doc had been reviewed by an active person and now this person is no more active, then opening the form will show a blank combobox. You're right that this person still remains mentioned in the table but it's no more display in the combobox when opening that record in the form. What I'm looking for is a way to display this person name in the combobox but not allow it to be selected (basically I'd like the combobox to display that name but that name shouldn't appear in the dropdown list). Should be a common problem no? The key point is that even if a person leaves, it should still be possible (via that combobox on the form) to know that he or she was the reviewer of a document.

 

[kilou]

No solution to be able to see people that left while preventing them being selected?

 

[TLEnder]

You could do a lookup on the AfterUpdate event of the combobox to see if they are an active employee. If they aren't throw up a message box to say they are no longer active and then blank the combobox value, so that another option has to be selected.

If you can post a copy of your DB i will try and write a sample for you.

Tlender

 

[LPurvis]

Indeed rather than a lookup, you could just include your "Active" column in the combo's source and refer to that value in your AfterUpdate code.
Fewer hits on the data is all the better since you're loading data from that table anyway.