Good morning experts:
I need some assistance with tweaking a process uses dual listboxes. Attached database contains the following tables:
1. Table "T01_Billets" -- has 7 billets (jobs)
2. Table "T01_StaffMembers" -- has 5 staff members (employees)
4. Table "T11_WorkingGroups" -- has 3 working groups (WGs)
5. Table "T21_UniversalJointTaskList" -- has 3 'Universal Joint Tasks' (UJTL)
Queries:
- There are six (6) queries... 3 for process "Billets to WGs and 3 for process "Billets to UJTLs"
Form:
- Two forms "F50_BilletsToWorkingGroups" and "F51_BilletsToUniversalJointTaskList"
Existing process:
- When bringing up, e.g., "F50_BilletsToWorkingGroups" the user can select "Working Group A", "Working Group B", or "Working Group C" from the drop-down.
- Next, for any of the three working groups, all "available" billets (7 of them) are shown in the left listbox.
- User can select any number and click on 'right arrow' to assign billet(s) to the three working groups. So, potentially, WG #A may have all seven billets to be part of the working group.
- Now, when switching to "Working Group B" or "Working Group C", the up to seven billets are still available for selection.
- So, conceptually, 7 billets can be assigned to each of the 3 working groups, resulting in 21 records being stored in the junction table "T10_JunctionTable_BWG".
Required change to 2nd process (billets to UJTLs):
- Just like the "billets to working groups", the "billets to UJTLs" works exactly the same. Again, 7 billets times 3 UJTLs would result in 21 assignments in the "T20_JunctionTable_BUJTL" junction table.
- However, for the "billets to UJTLs", I'd like to change the process. That is, if any number of billets have been assigned to, e.g., UJTL "SN1: Conduct Deployment...", then I don't these particular
billets be **no longer** available in the other UJTLs such as "SN 2: Provide Strategic Intelligence" or "SN 3: Employ Forces".
- Therefore, the modified process would allow to only store a maximum of only seven (7) -- vs. 21 -- billets in the "T20_JunctionTable_BUJTL".
My question:
- How would either one of the three queries AND/OR listbox data sources AND/OR VBA in the form "F51_BilletsToUniversalJointTaskList" be rewritten so that no billets can be assigned to multiple UJTLs?
Thank you in advance for your help!
EEH