combo box to show only available items

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a staff form w/ a cbo box that's to show only available locker keys
that can be assigned. Once a key is assigned it's to go out of the list.
Can anyone tell me how to do this? Currently, I have a Staff table w/ Locker
& FullName as fields and a Keys table w/ KeyID and FullName as fields. The
cbo opens a form showing all keys & has an Available chk bx which unchecks
when a name from that form's cbo (cboFullName) is selected. It's then
assigned in the staff table & the Key table. Two buttons from the Staff form
show either the current record's key# or all keys - both of which can
unassign the #. This is very clumsy & confusing as the popup form for all
keys, doesn't go to the selected key# in the Staff form's cbo. Any
suggestions on making this more automatic?
 
LDV,

Do Staff.Locker and Keys.KeyID have the same data in them, that is the
locker number? Or is the KeyID a different number?

If it's the former, you should *either* store the KeyID in the Staff table,
*or* store the Name (or preferably, a StaffID field) in the Key table, but
not both. When you need to who has what key, you can display this
information using a query.

Since it is more logical that the locker is an attribute of the person
rather than the other way around, storing Staff.Locker makes more sense.

As to limiting your combo box list to those keys that have not yet been
assigned, use the Unmatched query wizard, selecting the Keys table first,
then Staff, and selecting the matching fields. It will generate query with
the following SQL:

SELECT Keys.KeyID
FROM Keys LEFT JOIN Staff ON Keys.KeyID = Staff.Locker
WHERE (((Staff.Locker) Is Null));

Use this query as the RowSource of your combo box.

Hope that helps.
Sprinks
 
Back
Top