Forms for Item Accountability

[WillTis88]

Here's the basis of what I'm trying to do. I work for a prison and I'm attempting to generate a system for Key issuance. Currently we use all paper documentation, but I'm trying to put in place a digital version of the paperwork.
The current paperwork looks like this:
Key # | Area of Use | Issued By | Issued To | Authorized By | Date/Time Issued | Date/Time Returned | Checked In By

What I'm trying to achieve is a simple form where the user inputs the Key #, their name in the "Issued By" area, the receiver's name in the "Issued To" area, and a supervisor's name in the "Authorized By" area. The user then presses "OK" or just hits enter, and it will fill in those sections of the log similar to the above. It will automatically put in a timestamp of when the user submitted that entry. It will also automatically input the "Area of Use" based on a list that contains Key|Area data. That part shouldn't be too difficult, I'm just running into some roadblocks with the timestamp and the "Area of Use" section.

The second part of what I'm trying to do is when keys are returned. I want a separate form that only has "Key #" and "Checked In By" fields to be input by the user. Hitting "OK" or enter then searches the database for any entries for that specific key issuance that still need to be "Checked In." It will then display all the entries for the specified key that need to be "Checked In" and allow the user to select which entry to complete, and fill in the rest of that entry with the "Checked In By" field and another timestamp. This is the part that I am having real difficulty with.

I am a complete newboot with this program and SQL/programming in general. Ideally I would like to have all this in it's own program but that is way beyond my knowledge of how to do. Any help will be appreciated!

Also, if this is in the wrong section I apologize. My main issue is making the actual forms and having them do what I need them to so I figured this would be the appropriate section.

If all of these questions have been answered before, please point me in the right direction. Keep in mind, though, I have zero experience in programming, so step-by-step instructions would be the easiest. Thanks again!

[FJCC]

Before you worry about the forms, you need to have a database set up. I get the impression you haven't done that. The link in this thread to "Base Tutorial: from newbie to advocate" should be very helpful.

[WillTis88]

I'm not sure what else needs to be set up that I don't already have. I figured out how to auto-fill in the "Area of Use" field using a list box with key numbers, but I'm running into an issue where it's not inputting the selected Key Number into the field. It's only wanting to input the related Area information. Also, I have no idea how to go about searching for records with a given variable and then auto-completing them with a click.

Attached what I'm working with now. Oh, and the timestamp on creating a new record is broken. Not sure what happened to make it stop working

[Villeroy]

The 2 tables in your file are not a database that could fits the complexity of the task. So you can not build fully functional forms on top of the 2 tables.

If I had to do this I had some questions:
What is the purpose of a key? Does it lock one particular area, multiple areas, all areas? Does your table KeyLocations describe the association of one key to one location? Why do you have keys with location (none)? Are there any master keys?

I can see 3 roles on your log. Persons who use keys (all keys, restricted to use certain keys?), persons who authorize keys and persons who check in keys. A person gets a key from another person, uses the key for some time before he gives back the key to a third person (may be the same as the giving person?).
How are these 3 groups related to each other? Are they 3 different groups of persons or does the role depend on the situation (a key user can be autorizer or checker next time). Are the authorizers the same as the checkers?
In other words: Having a plain list of all involved persons, is it possible to divide them into separate roles which must not overlap at current time but may change over time?

Is the location where one fetches a key always the same where he gives back the key? Do you need the same key list on more than one computer?

[WillTis88]

What is the purpose of a key? Does it lock one particular area, multiple areas, all areas? Does your table KeyLocations describe the association of one key to one location? Why do you have keys with location (none)? Are there any master keys?

A key is used to open certain locks throughout the facility. Keys can only open locks they are assigned to, via the KeyLocations table. There are no master keys. Some keys are "(none) " because they have not been assigned yet. All keys are stored on a board with a big grid. X-axis is 1-19, Y-axis is A-R. (none) keys do not have a key assigned to that slot yet.

I can see 3 roles on your log. Persons who use keys (all keys, restricted to use certain keys?), persons who authorize keys and persons who. check in keys. A person gets a key from another person, uses the key for some time before he gives back the key to a third person (may be the same as the giving person?).
How are these 3 groups related to each other? Are they 3 different groups of persons or does the role depend on the situation (a key user can be autorizer or checker next time). Are the authorizers the same as the checkers?
In other words: Having a plain list of all involved persons, is it possible to divide them into separate roles which must not overlap at current time but may change over time?

One person (the person working the central control picket) issues the key for the duration of the shift. They issue the key to one person to use for however long they need it.
Some keys are restricted and require authorization from a supervisor (sergeant, lieutenant, captain, major, warden). When the user is done with the key, they return it to the central control picket officer who checks it back in, verifying the return of the key.

The issuer is not always the same person who receives it back. If say I issue a key to one person, then my shift ends, the person who relieves me will then check the key back in.

There are different levels of authorization depending the key. Some keys require sergeant or above. Others require captain or above. Still others must be approved by the warden for use. If a key is Sgt. Or above, a sgt can check it out without needing to fill in the "authorized by" section.

Is the location where one fetches a key always the same where he gives back the key? Do you need the same key list on more than one computer?

You check the key out at the control picket and return it to the control picket. Officers working there may be different like I said before. Might use the system in other control pickets for different buildings, but they will not be connected to each other.

[Villeroy]

[Example] Logging outgoing and incoming items.