Results 1 to 3 of 3
  1. #1
    Gina Maylone is offline Always learning
    Windows 10 Access 2016
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544

    Smile avoid duplicate selection in a list box

    Happy Fathers day to all you dad's out there!

    I have a form (IntakeAssessment) to determine the level of care a senior resident requires. I have a list box that contains the areas that are measured to determine Level Of Care (LOC) and Cost. ie.
    AREA Score ScoreDesc
    Bathing 0 Independent with bathing & showering
    Bathing 5 Standby assistance 2 times a week
    Bathing 10 Total assistance 2 times a week
    Bathing 20 Requiring assistance 2 times a week/or showers taking longer than 20 minutes
    Dressing and Grooming 0 Independent with dressing & grooming
    Dressing and Grooming 5 Partial assistance
    Dressing and Grooming 10 Total assistance
    Dressing and Grooming 20 Total assistance more than 3 times a day



    etc.

    The reason I went with a list box is because I want the end user to populate the areas and scores that are relevant to their agency (via a user form). The list box allows multi-select (yes, I know, I shouldn't do that) but I can't think of a better way to give control to the users or to show them the various choices they have. In my Intake form, the selections are passed to the IntakeAssessment table along with the PK (ResID), cost and LOC. What I'm wondering is if there is a way that I can keep users from selecting 2 items from one area. Like "Bathing - 0" and "Bathing - 5". Also, is there a way to group like items in a list box? I don't think there is, but just in case, I'm putting it out there. I am of course open to suggestions on a better way to handle this. The Areas, Scores, Score Descriptions, LOC and costs need to be dynamic.(right word?) I thank you in advance for any suggestions!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The list box allows multi-select (yes, I know, I shouldn't do that)
    multi select listboxes are fine because you keep control. The issue is with multivalue comboboxes


    What I'm wondering is if there is a way that I can keep users from selecting 2 items from one area. Like "Bathing - 0" and "Bathing - 5".
    only by having code in the before or afterupdate event for the listbox.

    A better way would be to normalise your data and use a subform with combobox - the combobox rowsource being refreshed after each entry to exclude the opportunities for 'wrong' selections - using the same principle as cascading combos.

    With regards grouping, depends what you mean. If as you have displayed, sort on the Area field

  3. #3
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This looks like a many-to-many relationship between Resident and LOC.
    I assume your form has a combobox lookup to find the desired Resident, and the listbox assignments are then made for that Resident.
    If you will post the table relationship diagram with field names, I'll post code for the after_update event for the combobox that will show only the LOCs in the listbox that have not already been assigned to that Resident.

    A more elegant solution would be the form above with 2 listboxes, AvailableLOC and AssignedLOC, showing the LOCs available, and the LOCs currently assigned to the Resident, with arrows between the listboxes to Add/Remove LOC assignments for the Resident.
    Last edited by davegri; 06-18-2017 at 09:47 PM. Reason: clarif

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

Similar Threads

  1. Get List Box Selection From VBA
    By jo15765 in forum Modules
    Replies: 3
    Last Post: 08-16-2016, 09:13 AM
  2. Replies: 3
    Last Post: 05-06-2014, 12:57 PM
  3. Replies: 15
    Last Post: 11-08-2012, 10:09 AM
  4. How to avoid duplicate or conflicting data
    By Binarygk in forum Queries
    Replies: 4
    Last Post: 04-07-2012, 09:02 AM
  5. Replies: 8
    Last Post: 01-29-2012, 12:50 PM

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