Need bound combo box on vehicle form to show only keys that have not already been selected

[heidii]

Hello

I have a Vehicle Database.

In the Vehicle Table I have a column where I can select what key #
belongs to this vehicle. It is bound to my Key Table, that has a list
of key #'s 1-200. In the Key Table the key # is the Primary Key and
is also the only field in the table.

I have made an input form for my Vehicle Table: When my input user
gets to the key# input box, I want them to enter the key number
associated with that key, but I do not want keys that have already
been assigned to another vehicle to show in the drop down list.

How can I accomplish this?

Thanks

Heidi

 

[John W. Vinson]

Hello

I have a Vehicle Database.

In the Vehicle Table I have a column where I can select what key #
belongs to this vehicle. It is bound to my Key Table, that has a list
of key #'s 1-200. In the Key Table the key # is the Primary Key and
is also the only field in the table.

I have made an input form for my Vehicle Table: When my input user
gets to the key# input box, I want them to enter the key number
associated with that key, but I do not want keys that have already
been assigned to another vehicle to show in the drop down list.

How can I accomplish this?

An "unmatched" query can do this. Assuming that you have fields [Key
#] in both tables (and I'd strongly advise changing the fieldname,
special characters such as # can cause problems) try

SELECT [Key Table].[Key #]
FROM [Key Table] LEFT JOIN [Vehicles]
ON [Key Table].[Key #] = [Vehicles].[Key #]
WHERE [Vehicles].[Key #] IS NULL;

This "frustrated outer join" query will find all records in [Key
Table], and then only show you those which do NOT have a match in
[Vehicles].

John W. Vinson [MVP]