Results 1 to 4 of 4

Dual listboxes -- need to modify process

  1. #1
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    191

    Dual listboxes -- need to modify process

    Hello 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
    Attached Files Attached Files

  2. #2
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,073
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  3. #3
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    191
    Per Bob's recommendation, attached is the database (zipped) in MDB format.
    Attached Files Attached Files

  4. #4
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,073
    skydivetom

    Please read the following link: https://www.excelguru.ca/content.php?184
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-14-2019, 04:43 AM
  2. Dual listboxes ... need to tweak VBA or query
    By skydivetom in forum Forms
    Replies: 3
    Last Post: 08-08-2019, 04:36 PM
  3. Replies: 4
    Last Post: 08-07-2019, 07:48 PM
  4. Replies: 20
    Last Post: 08-03-2019, 03:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums