Well i have a database table:
with four fields so when a item is signed out all 5 fields are filled in on a form but when there signed back in the user only needs to fill in 3 fields and the other fields need to revert back to blank.
Item No: This is a combo box
Name Of User:
Date Signed Out:
These are the four fields.
So when the item is signed back in the Date In and item number would only be present on the form.
Once the entry is entered, I want it to save and then delete the name of user, job no and date in records??
Mate Ideally i would love a history table where every change user etc is recorded i tried to enter a audit trail and it didn't like it.
Ideally I would love to be able when stock comes back in it moves the information to the history table making the equipment table blank ready for queries etc to run of it as well as keep it ready for when the next user needs to sign out that particular equipment?
Usually you don't change the values of the base table (let's call it Tbl_Items), and possibly of a second base table (Tbl_Users). This second table is useful if the set of users is known, is rather constant, does not vary much.
A third table (Tbl_Movements or Tbl_Transactions) stores the ID from Tbl_Items, the ID of Tbl_Users if this table exists (or the user's data if it does not) and data related to the movement: date of movement, type of movement, other data specific to the movement (possibly an ID from a third base table).
Queries are used to retrieve available items, "engaged" items, etc. They also indicate what kind of movements are allowed at a given time for a given item.
The principle is very similar to those used for stock management systems.