Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trouble With Listboxes (Access 2000)

    I have a form with a combo box and two list boxes. The user selects a duty section from the combo box and then can assign a recruit from the list box on the left by double-clicking their name to move them over to the the list box on the right. (If they double-click the wrong name, they can remove the name from the box on the right.)

    Once a duty section has been filled (number of people determined by the user), the user selects another duty section from the combo box to fill that section.

    So far I have gotten all of that to work. My problem is that I do not want somebody who has been assigned to a duty section to show up in the list box on the left when a different duty section is selected in the combo box. The result now is that a person can be assigned to every duty section but they are only supposed to be assigned to one. (My workaround so far has been to just try and remember who has been assigned to what section - but I don't like that solution!)

    Attached is a stripped down example.

    Thanks for your help.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trouble With Listboxes (Access 2000)

    Am I correct in assuming that the list on the left should only display recruits that haven't been assigned to any duty yet, instead of those who haven't been assigned to the duty selected in the combo box? If so, change the SQL for qryRecruitDutyList to

    SELECT tblRecruit.RecruitID, [LName] & ", " & [FName] AS Student, tblRecruit.ASMOOut
    FROM tblRecruit LEFT JOIN tblDutyAssignment ON tblRecruit.RecruitID = tblDutyAssignment.RecruitID
    WHERE (((tblRecruit.ASMOOut) Not Like Yes) AND ((tblDutyAssignment.RecruitID) Is Null))
    ORDER BY [LName] & ", " & [FName];

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trouble With Listboxes (Access 2000)

    Thanks, Hans! That did the trick! I had my head so wrapped around using the query within a query that I never thought to use the table for the join instead.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •