Restrict combo to 'unused' ID's 1

[gingernob]

Newbe, I have searched forum but could not find something identical:
Have 4 tables:
exams
invigilators
invigactivity
invigstatus

fields:
exams:eid, [plus other not relevant stuff]
invigilators: invigID, invigstatusID [plus other not relevant]
invigstatus: invigstatusID, invigstatus
invigactivity: invigactivityID, invigID and eid

Have primary form: 'exams' with subform 'invigilator_activity'
really simple stuff so far huh! so essentially the subform shows the invigilators associated with a particular exam.
what i can not do is in the combo for the invigilators activity, reduce the available invigilators as they get allocated, eg. invig1, invig2, invig3. when there are no invigilators allocated, i should click the dropdown and see all 3, but say i allocate invig1, on the next record when i click on the dropdown, i want to see only invig2 and invig3 as invig1 has already been allocated and want to avoid invigilator being allocated twice in error.

i tried placing this on combo source:
SELECT INVIGILATORS.INVIGID, ([INVIGILATORS].[INVIGFIRST] & " " & [INVIGILATORS].[INVIGOTHER] & " " & [INVIGILATORS].[INVIGLAST]) AS [FULL NAME], INVIGSTATUS.INVIGSTATUSID
FROM INVIGSTATUS INNER JOIN INVIGILATORS ON INVIGSTATUS.INVIGSTATUSID = INVIGILATORS.INVIGSTATUSID
WHERE (((INVIGSTATUS.INVIGSTATUSID)=2) AND ((Exists (SELECT INVIGACTIVITY.INVIGID,INVIGACTIVITY.EID FROM INVIGACTIVITY WHERE INVIGACTIVITY.INVIGID = INVIGILATORS.INVIGID AND INVIGACTIVITY.EID = [Forms]![EXAMS]![EID].[value]))=False));

This kind of works in that it does restrict, but additionally does not show the records that exist, that HAVE been allocated. I hope this makes some kind of sense and any help gratefully recieved, but if you need more info, please let me know.

 

[TheAceMan1]

How are ya gingernob . . .

The following link should give you the idea. Just pay attention to only one of the listboxes. Remember this is just to present the concept your after.

Control Listbox/Combobox Items (Concept)

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[gingernob]

Hi Aceman1

Thanks for that and yes, conceptually all understood and re-created my static table, BUT now what i want to do is:

create a TMP[rnd] table and load the rowsource of the listboxes from the tmp table.
all the creation side works, just when my unbound form loads and i try to set the rowsource, it says the selection criteria is wrong. i tried 2 ways. The tmp table name is set in the public var called strtable2
i have verified that this var is setting correctly.
1) Me.lsttmp1.RowSource = "SELECT strTable2.FullName AS Expr1, strTable2.INVIGID AS Expr2, strTable2.DELETED AS Expr3 FROM strTable2 WHERE ((([strTable2].[DELETED])=0));"

2) Me.lsttmp1.RowSource = "SELECT " & strTable2 & ".FullName, " & strTable2 & ".INVIGID, " & strTable2 & ".DELETED FROM " & strTable2 & " WHERE (((" & strTable2 & ".DELETED)=1));"

Neither work, though 2 ran ok with no errors but did not load the data!!!
any ideas.
Many thanks
David

 

[gingernob]

apologies, error in copy/paste;
in above 1) should state:
"SELECT strTable2.FullName, strTable2.INVIGID, strTable2.DELETED FROM strTable2 WHERE ((([strTable2].[DELETED])=0));

 

[TheAceMan1]

gingernob . . .

The 1st line failed because you didn't concatenate the variable [blue]strTable2[/blue] properly.

The 2nd line failed because you changed DELETED=[red]0[/red] to DELETED=[red]1[/red] ... change back to [red]0[/red]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]