conditional value list - shouldn't this be simpler?
I've read several posts related to Conditional Value Lists, and I've also worked with the Knowledge Base article as well as a couple of sample files found on the internet. I'm understanding the concept of a CVL, but what I've found doesn't seem to address the extemely simple way in which I want to use one.
I have a table with 400 records representing the membership of an organization. There are different "types" of members as indicated by a field called "status." Members can be "active," "withdrawn," "out of town," or "deceased." I have a simple value list for the four status types. When working with this database, I'm almost always dealing with the "active" members. The other types are mainly for record keeping, but the "active" members are the ones that I'm constantly doing stuff with (sorting, finding, running reports, assigning to committees, sending emails to, etc.). When assigning members to committees, for example, I'd like to be able to have a pop-up menu only display the active members and not every single member (past and present). Right now I have a layout with a portal that works for establishing my many-to-many relationships, but the pop-up menu for choosing the members to populate a committee shows every single member name instead of just the active members. However, I don't want to have to first choose from a pop-up menu to indicate "active" before then choosing a member from a second pop-up menu. I just want my member pop-up menu field to ALWAYS only show my active members. Do I have to create some intermediate table first, or is there a way to simply have a pop-up menu only display records based on a found set, or something like that? Am I being clear about what I'm trying to do?
Thanks in advance for any help anyone can provide.