Trouble With Listboxes (Access 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote