Current datab only query

[LindaC]

I have a query that will be made into a form. The query has all the records
selected and inputted by users except for the location field. Oly one user
will be inputting and assigning the location field. When she opens the query
all the records are there and for all previous records the location is in.
Going forward if a user enters a new record it needs to be assigned a
location. My location selections are for example A1A, A1B. How can I get
the location drop down only to show which locations have no record associated
with it? If A1A was available but A1B was alreay used, I would want the only
selection to be made available be A1A. Thanks.

 

[Jeff Boyce]

Linda

Take a look at the "not in" query wizard (new objects button on toolbar).
The general idea is to create a query that lists choices that are "not in"
some other query against the same underlying table. That second query would
list the "in use" locations.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ofer Cohen]

In the RowSource of the combo you can create a criteria to remove all the
values that were selected

In SQL:
Select location From TableName Where location Not In (Select location From
SecondTableName)

As criteria for the location field
Not In (Select location From SecondTableName)