Dual listboxes ... need to tweak VBA or query

[skydivetom]

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

[rpeare]

The STAFF identifier should be used in the junction table linking staff to working group, not the billet. If you have five staff members and select as single billet it'll have the effect of appearing as though every staff member with the job description in question is added to the billet which is not what you want.

[rpeare]

Here's your version 2 modified. I had to change the relationships (I don't use relationships because they can cause some real problems when you want to do a redesign of your database like I did in this case) the workgroupbillet table now stores the staff id rather than the billet but otherwise functions as you had it before.

Just a couple of notes:
Your error handling treats all errors as the same error and will be deceptive if you encounter it. You should trap the exact error by debug.printing err.number & err.description and plan around those errors if you want to specifically identify to the user what the problem is.

I would stay away from the .add/.update method of creating records/editing records. Especially over large datasets it can really bog down your code.

            .AddNew
            .Fields("WorkingGroupIDfk") = [WorkingGroupIDpk]
            .Fields("BilletIDfk") = Me.lstAvailable.ItemData(varItem)
            .Update

Version 2.zip

[skydivetom]

rpeare: Thank you... you offer a very intriguing solution.

Pros:
- Members assigned to same billet (incumbent staff member and new hire) can be assigned to same working group.

Cons:
- As part of future planning, if a billet is unfilled, no assignment can be made.
- Now, naturally a billet/position (thing) cannot attend a meeting; however, assigning a vacant billet (on paper) can be helpful as it will identity holes in organization/working group.

So, I'll have to further muddle over before integrating this proposed solution. However, I'm extremely glad that this option is available and also the reduced overhead (fewer queries and less VBA).

Again, thank you... I'll keep you posted.

EEH