Results 1 to 4 of 4
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    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's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    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.
    Bob Fitzpatrick

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
  •  
Other Forums: Microsoft Office Forums