linking fields in a form

[bceo]

I wish to set up a DB to control the issuance of keys. I want to set up an input form where when you enter the key number a one field it will populate the Name field with a list of people who are allowed to have that key.
The DB will have around 30 keys each one has it's own list of people who can draw that key.
I was thinking of setting a tables for every key with the names, then either pop the list up or populate the drop down Name field to verify the person is allowed this key.
I am not good at VB so the simpler the method the better.
Thanks, in advance, for any help I can get.

 

[boblarson]

No, you don't create a table for every key. You would have something like this:

tblKey
KeyID - Autonumber(PK)
KeyDesc - Text

tblPersons
PersonID - Autonumber (PK)
LastName - Text
FirstName - Text
...other pertinent info about the person...


tblPersonsKeysAuthorized
PersonKeysAuthorizedID - Autonumber (PK)
PersonID - Long Integer (FK)
KeyID - Long Integer (FK)

and then

tblKeyCheckedOut
PersonID - Long Integer (FK)------| Composite PK
KeyID - Long Integer (FK) -------| Composite PK
DateCheckedOut - Date/Time
DateCheckedIn - Date/Time

So something like that.

 

[bceo]

Thanks for the quick reply Bob, if I understand what you sent
1) I set a table for the keys
2) I set up a table for the all the people
3) I set up a table for the key and the person who can draw it.
4) I then set up the last table, which I assume connects it all together.
If I have the above correct how do I set up the form to auto display or pop up the list of people who can draw a certain key?

 

[boblarson]

If I have the above correct how do I set up the form to auto display or pop up the list of people who can draw a certain key?

You just use a combo box with the row source set to a select statement which has the criteria of the person who is currently displayed on the main form.

Let me know if that needs more explanation.