Assign code to field

[Dina01]

Hello,

I am trying to assign a specific code to an agent, when he gets unavailable, and I am not too sure how to do this.

I have a few combo box that I can choose the name of the agents, but what I am trying to do is if the agent was assigned to a call on the first combo box, and then I try to add anothere agent on the second, his name should disappeared from my list of choise.

The SQL statement of the combo box is
SELECT Effectif_tb.Agent, Effectif_tb.Poste, Effectif_tb.Code, Effectif_tb.Groupe FROM Effectif_tb GROUP BY Effectif_tb.Agent, Effectif_tb.Poste, Effectif_tb.Code, Effectif_tb.Groupe HAVING (((Effectif_tb.Code)<>1)) ORDER BY Effectif_tb.Groupe;


Effetif is the table where I can see the code, therefore is code = 1 agent in Not Available but if it's 0 the agent is available.
And agent field is the fullname of the agent.
How to assign the code to the agent?

I think it's sometnigh like this.

Private Sub Agt1_AfterUpdate()
If Not IsNull(Me.Agt1) then
' ASSIGN CALL TO AGENT AND CODE SHOULD BE 1
me.agt1.requery
End Sub


I don't know how to do this. Can someone please help