By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,479 Members | 2,260 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,479 IT Pros & Developers. It's quick & easy.

Remove a value from a Combo box once the value has been selected

P: 5
Hi
I am trying to remove a value from a Combo box once the value has been selected

I have a form with a combo box that uses the following sql for the row source
Expand|Select|Wrap|Line Numbers
  1. SELECT EmpTable.EmpNo, EmpTable.Surname, EmpTable.Forename
  2. FROM EmpTable
  3. ORDER BY EmpTable.Surname;
Once the user selects the employee in the combo box they click a button and the EmpNo is added to the TransTable along with other Fields on the form.
This is then displayed in a data sheet sub form on the original form via a query on the TransTable. Then the prosess starts again only now I donít want the EmpNo that is in the TransTable to appear in the combo box.

So I should just be able to add something in the criteria of the query above so it does not show rows were the EmpNo exists in the TransTable. This seem like it should be easy but I canít seem to get it to work!

Can anyone point me in the right direction please?
Dec 15 '07 #1
Share this Question
Share on Google+
3 Replies


puppydogbuddy
Expert 100+
P: 1,923
Expand|Select|Wrap|Line Numbers
  1. SELECT EmpTable.EmpNo, EmpTable.Surname, EmpTable.Forename
  2. FROM EmpTable
  3. WHERE EmpNo Not IN (SELECT EmpNo FROM TransTable WHERE TransTable.EmpNo=EmpTable.EmpNo);
Dec 15 '07 #2

P: 5
Many thanks for the qick reply.
I can see how this works now. I need to improve my sql especially when it comes to nesting select.
Dec 15 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
Subqueries in SQL may prove helpful if you're interested.
Dec 17 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.