Removing Choosen Item from ComBo Box

[Dina01]

I have a form that has a few combo box and what I would like it to do is that if I choose a name in the first box, well that name should not appear in the second combo box, and if I choose one from the 1 and 2, well does name should not appear in the 3rd box.


Therfore I want to refresh the combo box..

This is the code That I have and used to work before...but it's not working anymore and I don't know why.

can someone please help
************************

Private Sub Agt1_AfterUpdate()
On Error Resume Next

Me.Agt2.Requery
Me.Agt3.Requery
Me.Agt4.Requery
Me.Agt5.Requery
Me.Agt6.Requery
Me.Agt7.Requery
Me.Ambu.Requery
Me.Patrouille.Requery

End Sub

Private Sub Agt2_AfterUpdate()
On Error Resume Next

Me.Agt3.Requery
Me.Agt4.Requery
Me.Agt5.Requery
Me.Agt6.Requery
Me.Agt7.Requery
Me.Ambu.Requery
Me.Patrouille.Requery

End Sub

Private Sub Agt3_AfterUpdate()
On Error Resume Next

Me.Agt4.Requery
Me.Agt5.Requery
Me.Agt6.Requery
Me.Agt7.Requery
Me.Ambu.Requery
Me.Patrouille.Requery

End Sub

and so on

 

[Jack Cowley]

Maybe this article will help...

http://support.microsoft.com/support/kb/articles/Q209/5/76.ASP?LN=EN-US&SD=gn&FR=0&qry=q209576&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ACC2000

 

[Dina01]

I looked at that article but that is not what I am trying to do...

Let's say I have 3 combo box that come from table named: tblName

tblName
-------
Dina
Cathy
Maria
Carlo

When I go to my form and go into the first combo box

and choose Dina in cbo1
then if I go into cbo2, Dina should not appear as a choice, I should only see Cathy, Maria, Carlo.

 

[Jack Cowley]

The article does what you want. It is up to you to create the query for ComboBox2 that will show only the names you want based on the selection in ComboBox1. Here is what your SQL might look like for ComboBox2:

SELECT MyTable.tblName
FROM MyTable
WHERE Not MyTable.tblName = [Forms]![MyForm]![ComboBox1];


[This message has been edited by Jack Cowley (edited 05-18-2002).]