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

    Dual listbox -- change process to allowing multiple selection acoss multiple other values

    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
    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

  5. #5
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by skydivetom View Post
    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?
    Hi Tom!

    In design view of query Q51C_BilletsToUniversalJointTaskList, replace the "table" T20_JunctionTable_BUJTL with the original table. That's all.

    Cheers,
    John

    P.S.: Don't forget to Combact and Repair your databases before compress it.

  6. #6
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Hello John... thank you... that was an easy solution. I wouldn't have figured it out though.

    As the process continues to evolve, I have posted yet another different question in the below listed thread. Yet another layer of "monkey wrench". I welcome any thoughts/recommendations.

    https://www.accessforums.net/showthread.php?t=77953

    Thank you in advance for any help you might be able to offer.

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

Similar Threads

  1. Replies: 20
    Last Post: 08-03-2019, 03:21 AM
  2. Replies: 5
    Last Post: 06-11-2014, 05:10 PM
  3. Replies: 21
    Last Post: 10-05-2012, 11:36 AM
  4. Passing Multiple selection values to a report
    By techexpressinc in forum Forms
    Replies: 7
    Last Post: 01-13-2012, 02:27 PM
  5. Multiple default values in listbox
    By rickscr in forum Forms
    Replies: 3
    Last Post: 04-07-2011, 09:49 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