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));

 

[John W. Vinson/MVP]

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));'

Sorry - small typo. Should be

WHERE ((([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?

Well, Tom might have mentioned 2007 - I didn't, and it has nothing to
do with this particular issue.

 

[dennis]

thanks john i followed your instructions, i created a new query based on
employees id and employees last name, and pasted your sql comments in(with
the small typo corrected) and used this as the row source. but the drop down
box employee in northwind was empty ,it did ask for parameters in
employee id
employee last name
orders employee id
sorry john what am i doing wrong?and thank you for your patience
rgds 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));'

Sorry - small typo. Should be

WHERE ((([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?

Well, Tom might have mentioned 2007 - I didn't, and it has nothing to
do with this particular issue.

 

[John W. Vinson/MVP]

thanks john i followed your instructions, i created a new query based on
employees id and employees last name, and pasted your sql comments in(with
the small typo corrected) and used this as the row source. but the drop down
box employee in northwind was empty ,it did ask for parameters in
employee id
employee last name
orders employee id

You need to change the names employee id, employee last name and
orders employee id to whatever the names of those fields actually are
in YOUR table. I posted an example for you to modify - since you have
not posted any details of your table structure, and I cannot see it
from here.

Perhaps you could post the SQL view of the query you are using, and
the names and datatypes of the fields in your table, if you need help
doing so.

 

[dennis]

thanks john the following is the sql i am using
SELECT MembersListNo.ID, MembersListNo.[Full Name]
From MembersListNo LEFT JOIN full name
On MembersListNo.ID =full name.[memberslistNo ID]
WHERE ((([full name]. [memberslistNo.ID]) IS NULL));
and this a list of my fields and types
members list No auto number
full name text
tag No text
orderfield number
yes john i do need help??
rgds dennis

 

[John W. Vinson/MVP]

thanks john the following is the sql i am using

SELECT MembersListNo.ID, MembersListNo.[Full Name]
From MembersListNo LEFT JOIN full name
On MembersListNo.ID =full name.[memberslistNo ID]
WHERE ((([full name]. [memberslistNo.ID]) IS NULL));

Several things wrong here. You obviously did not create this using the
query grid! First off, whenever you (unwisely) put a blank in a table
or fieldname you must - without execption - enclose it in [square
brackets]. Secondly, you're trying to join the table MembersListNo to
the *FIELD* [full name] - you can only join a table to another table,
not to a field. Or *do* you have a table very oddly named [full
name]? If so what are its fields? If you do, and it contains a field
named [memberslistNo ID], then you can try

SELECT MembersListNo.ID, MembersListNo.[Full Name]
From MembersListNo LEFT JOIN [full name]
On MembersListNo.ID =[full name].[memberslistNo ID]
WHERE ((([full name]. [memberslistNo.ID]) IS NULL));

Even at that, Access may get confused since you have both a table
named [full name] and a field named [full name]. It's certainly
confusing to me!!

 

[dennis]

hi john,i told you i i was a learner and yes i can get confused! however i
also perservere until i get it right.i do not have a table named full name?
so what i have done is go back to the beginning and use northwind as the
example without trying to adapt to my tables at this stage
lo and behold the employee drop down box now calls for a"enter parameter
value and after entering one of the employees id it then does not appear in
the list to select from.thats fine but
what i really want is to be able to select an employee once and then have it
removed from the list to avoid duplication?is this possible?
once again your help is really appreciated rgds dennis

thanks john the following is the sql i am using

SELECT MembersListNo.ID, MembersListNo.[Full Name]
From MembersListNo LEFT JOIN full name
On MembersListNo.ID =full name.[memberslistNo ID]
WHERE ((([full name]. [memberslistNo.ID]) IS NULL));

Several things wrong here. You obviously did not create this using the
query grid! First off, whenever you (unwisely) put a blank in a table
or fieldname you must - without execption - enclose it in [square
brackets]. Secondly, you're trying to join the table MembersListNo to
the *FIELD* [full name] - you can only join a table to another table,
not to a field. Or *do* you have a table very oddly named [full
name]? If so what are its fields? If you do, and it contains a field
named [memberslistNo ID], then you can try

SELECT MembersListNo.ID, MembersListNo.[Full Name]
From MembersListNo LEFT JOIN [full name]
On MembersListNo.ID =[full name].[memberslistNo ID]
WHERE ((([full name]. [memberslistNo.ID]) IS NULL));

Even at that, Access may get confused since you have both a table
named [full name] and a field named [full name]. It's certainly
confusing to me!!