assign item from list box, make unavailable until unassigned

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

Guest

Can anyone tell me how to allow a user to select an item in a combo box on a
form, it gets automatically assigned to the current record and, at the same
time, make it unavailable (preferably not shown in list) until it's
unassigned from 1st record? I have a Staff table & a Keys table, which
contains lockers & peds (filtered by 2 qrys to fill 2 combo boxes. Users can
have 1 of ea but no dup#s assigned.
 
The best way will be, I think, to base the combo on a subtract query - so
that it only displays items on the "AllPossibleComboItems" table that don't
appear on the main table.
 
Use a KeyIssue table with a foreign key matching the primary key in KeysTable.

KeysTable --
KeyID – Autonumber
Name

KeyIssue –
KeyID – number – long integer
Issue – datetime
Return – datetime
Assigned – text

Query for keys on issue – KeysOut
SELECT KeyIssue.KeyID, KeyIssue.Issue, KeyIssue.Return
FROM KeyIssue
WHERE (((KeyIssue.Return) Is Null));

ListBox record source query –
SELECT KeysTable.KeyID, KeysTable.Name
FROM KeysTable LEFT JOIN KeysOut ON KeysTable.KeyID = KeysOut.KeyID
WHERE (((KeysOut.KeyID) Is Null));
 
Back
Top