remove an item from a combo after using that item

[dennis]

sorry my previous thread was not helpful to anyone
i am using access 2003 in xp and have a form with a combo box to select
names from a table.this is all working fine,but i want to remove the selected
name (after selection) so that it cannot be selected again.it would also help
if i could requery the table to maintain the original table?.
i am new to access but learning fast,many thanks rgds dennis

 

[Tom van Stiphout]

On Fri, 1 Aug 2008 15:51:01 -0700, dennis

Let me address the first question: how to exclude items already used.
Thinking of a Northwind-like database, we have an Orders form with a
SalesPerson dropdown on it. Let's say that our very unusual rule is
that salespersons can only have one Order.
The SalesPerson dropdown would have a RowSource of a query like this:
select EmployeeID, EmployeeName
from Employees
where EmployeeID not in (select SalesPersonID from Orders)

I'm sure you can adapt this to your situation.

You lost me on your " requery the table" question.

-Tom.
Microsoft Access MVP

 

[dennis]

many thanks tom,yes you are on the right track. i told you i am learning,can
you show me an example of how the query should look like if i use northwind
as my example
thaks again dennis

 

[Tom van Stiphout]

On Fri, 1 Aug 2008 17:16:00 -0700, dennis

Access 2007 > Create a new query, switch to SQL view, and paste this
in:
SELECT Employees.ID, Employees.[Last Name]
FROM Employees
WHERE (((Employees.ID) Not In (select [Employee ID] from Orders)));

-Tom.

 

[John W. Vinson/MVP]

Access 2007 > Create a new query, switch to SQL view, and paste this
in:
SELECT Employees.ID, Employees.[Last Name]
FROM Employees
WHERE (((Employees.ID) Not In (select [Employee ID] from Orders)));

Or, logically equivalent but perhaps more efficient:

SELECT Employees.ID, Employees.[Last Name]
FROM Employees LEFT JOIN Orders
ON Employees.ID = Orders.[Employee ID]
WHERE ((([Orders].[EmployeeID) IS NULL));

 

[dennis]

thanks fellas but i have further problem with you instructions
i created a new query with employees id and employees last name and pasted
the following into sql view
SELECT Employees.ID, Employees.[Last Name]
FROM Employees LEFT JOIN Orders
ON Employees.ID = Orders.[Employee ID]
WHERE ((([Orders].[EmployeeID) IS NULL));

i received an error message
missing )or item in query expression'((([orders].[employee id)is null));',
can you help me sort this out. Also i noticed you quoted access2007 i am
using 2003 as previously advised?
thanks again rgds dennis

Access 2007 > Create a new query, switch to SQL view, and paste this
in:
SELECT Employees.ID, Employees.[Last Name]
FROM Employees
WHERE (((Employees.ID) Not In (select [Employee ID] from Orders)));

Or, logically equivalent but perhaps more efficient:

SELECT Employees.ID, Employees.[Last Name]
FROM Employees LEFT JOIN Orders
ON Employees.ID = Orders.[Employee ID]
WHERE ((([Orders].[EmployeeID) IS NULL));