Experts:
I need some assistance/recommendations with modifying queries and/or VBA to tweak an existing process which allows to "assign billets to working groups".
Attached are two databases which further illustrate the process. The first one "Version 1" (v01) works great; the second one "Version 2" (v02) is a copy of v01 with added tables.
Process of first database (v01):
- There are 15 billets (jobs) that can be assigned to any of the five working groups (A:E).
- The included ERD illustrates how the tables are joined together.
- Once form "F50_BilletsToWorkingGroups" is opened, the user can select any of the five working groups (Working Group A-E) from the combo.
- User then can add billets from the left list box to the right listbox (highlight one or more billets and click on "right-arrow").
- There are no constraints on the billet assignments. That is, conceptually, all available billets can be assigned (if desired) for all of the five working groups (A:E).
- Any "billet-to-working group" assignements are stored in table "T10_JunctionTable"BWG".
- Again, v01 works great!
Process of second database (v02):
- Again, v02 is a copy of v01 but includes two additional tables: "T01_StaffMembers" and "T00_JunctionTable_OBS".
- The updated ERD illustrates how the added tables were linked to the existing architecture.
Additional background:
- The difference between v01 and v02 is that I now also show the staff member's last name in the form.
- Please be aware that, e.g., two staff members (employees) may be assigned to the same billet (job). Such scenario is possible when a new hire is brought into the organization and there's an overlap between incumbant and new-hire.
- And herein lies the problem... in other words, based on v01's design, "T10_JunctionTable" stores the auto IDs of both WorkingGroupIDfk and BilletIDfk.
- In the attached example (v02) I assigned employee "Clint Eastwood" and "Henry Fonda" (through another form not included in the example DB) to the same billet/job (i.e., 1888 | Data Analyst IV).
- Now, in the form, when selecting let's say Working Group A and then "1888 with employee Eastwood" **alone** both employees Eastwood AND Fonda are moved from the left to right listbox.
- Conceptually, I have no problems with this... that is in junction table, only the BilletIDfk (214) is linked to WorkingGroupIDfk (1).
Below, however, appears to be causing a problem though:
- When I select four displayed billets (1888, 1888, 4006, and 4008) altogether and then click on the right arrow, an error message is thrown.
- I added the dialogue box to circumvent the VBA error to pop-up. Ultimately though, when selecting all members I now cannot add both Eastwood and Fonda (in addition to Bronson and Johnson) to the same working group.
My question:
Does anyone have a recommendation for modifying the VBA so that I don't end up with the less smooth billet assignments? Ultimately, I don't want to get the message box to see the message box that tells user
than "2 billets in same WG" cannot be added. I want v02 work as smoothly as v02 since I only want to display the lastname assigned to a billet (stored in different table though).
Thank you in advance,
EEH