SQL SELECT WHERE Problem

[Robin]

Hello,

I have the following scenerio:

An Engagement is made up of 5 Assignments to be completed sequentially by
different staff members. When the Engagement is first set up, I want the
first Assignment in the sequence to show up in a "New Assignments" listbox
which is based on listbox "lstSMainS". When the staff "checks" the
Assignment complete (CAComplete) it shows up when the next staff's ID is
chosen in the lstSMainS.

I have the following SQL which I thought would give me the the record where
the sequence number (CASequence) was the "next in sequence" after the
completed Assignments when CAStaffID matched lstSMainS. Instead it is giving
me each staff's assignment whether the assignment preceding it has been
complete or not.

SELECT ClientAssignment.ClientID, ClientAssignment.EngagementID,
ClientAssignment.EngagementYr, ClientAssignment.AssignmentID,
ClientAssignment.CADescriptionI, ClientAssignment.CADueDate
FROM ClientAssignment
WHERE
(((ClientAssignment.CASequence)=DMin("[CASequence]","ClientAssignment","CAComplete
= False AND ClientID = """ & [ClientID] & """ AND EngagementID = """ &
[EngagementID] & """ AND EngagementYr = """ & [EngagementYr] & """ AND
AssignmentID = """ & [AssignmentID] & """")) AND
((ClientAssignment.CAStaffID)=[Forms]![zfmMain]![lstSMainS]))
ORDER BY ClientAssignment.CADueDate;

Any thoughts would be appreciated,
Robin

 

[Robin]

In case my original explanation was a bit fuzzy, this is what I'm trying to do:

Assignment
CASequence=1, CAStaffID=MEL
CASequence=2, CAStaffID=DTM
CASequence=3, CAStaffID=RLC
and so on...

When MEL opens the form his StaffID is already chosen in lstSMainS and
CASequence1 shows up in the "New Assignments" listbox. When DTM or RLC open
the form (their StaffID's now populate lstSMainS), CASequence 2 and 3 are NOT
in the listbox because CASequence 1 is not complete.

When MEL marks the record Complete (CAComplete = True) then DTM sees
CASequence 2 in the "New Assingments" listbox when he opens the form. RLC
still won't see CASequence 3 in "New Assignments" until DTM marks CASequence
2 complete.

Right now everyone sees their assignment in the "New Assignments" listbox
before the preceding CASequence has been completed.

Does that make sense?

Thanks,
Robin