Change a yes/no field automatically with Key management

[Ramon19]

Hi all,

Im new with Access database and I found almost everything i needed on the internet. There is only one thing I just cant figure out when this looked like one of the easier things. Im making a database where all soorts of keys of all kinds of companies and homes are stored. In this database I want to keep track of the keys that are being used, who has the keys and when the keys are taken and brought back. Most of these things work except one thing. In the table where I have the keys I have a field wich is 'In use'. This is a yes/no field and I want it to be yes when someone takes a key and to switch back to no when someone returns it. This is possible if it is done by the employees who use the keys but i want it to go automatically so no mistakes are made.

I have one table for the keys, one for the employees that take and return the keys, I use a master with detail form for when someone takes or returns a key and this is all shown in a query.

Can someone help me?

With kind regards,



Ramon

[RuralGuy]

How do you indicate a key is "in use" or "not in use"? In what field of what table?

[Ramon19]

I have a table called 'keys list'. In this table I have all the information about all the keys. One of the fields in this table is called 'In use'. This is a Yes/No field with a selectionbox. I would like to see it in the table so I can look up all the keys and see if they are being used. If you suggest another way of showing if the keys are being used or not than i can try something else. It doesnt really mather as long as the box is being checked on if its in use and checked off if its returned and that I have an easy global view of the keys.

Thanks for helping btw

[RuralGuy]

What process do you use when someone Returns a key?

[Ramon19]

When one of the employees returns the keys he was using, i have a form on wich they fill in there employee number and the number of the transaction. Then the transaction number and all the keys are shown within the form. The transaction number, employee number, date of when the keys were taken, when they should have been returned and when they were actually returned are in the master form. Then the keys and the boxes ("In use", wich I want to go automatically) are in a detail form. Now they check the boxes so they are back to not being used and save the record.

[Ramon19]

Oh and the master and detail form are connected through the transaction number. But I dont know if that is relevant...

[RuralGuy]

Are you opening up a table directly or do all of your viewing and transactions go through Forms?

[Ramon19]

Everything goes through forms because i dont want someone to be able to change something in the table.

[RuralGuy]

Excellent! Since you have a transaction number, it seems to me that you should be able to create an UPDATE query that changes the InUse field and run it whenever you want from the Main Form.

[Ramon19]

Oke, I didnt thought of that. But I know how to use an update query to update a whole field but I don't know how I should make it work for only the keys that are being used. Can you explain how I should do that or maybe you show a link to a tutorial were I can look that up.

[RuralGuy]

Is there only one record showing in the SubForm?

[Ramon19]

Well, the sub has 3 fields, Transaction number, Keynumber and 'in use'. But in the Form it only shows the Keynumber and 'in use'. The other is on the footer of the form. And the subform can have as many records as possible because the employees usually take about 20 keys in one time. But these records all have the same transaction number wich is linked to the master form

[RuralGuy]

Now I'm lost. Do the Keys have unique numbers?

[Ramon19]

Sorry for replying a bit late. But yes the keys have unique numbers.

[RuralGuy]

You could pass both numbers to the function so it could make the switch.