I have just built a database to house our inventory. It has two primary tables, the asset table and the user table. In the asset table there is a linked field to the user table so that we can assign an asset to a user.
There is also a date assignment field.
I've created a check-in / check-out form. So far it works just as I had planned. However, I need a way to track all the check-ins and check-outs so that I can do monthly / annual reports on the activity. Since the same device could be checked-in and out repeatedly, I don't think that I can do this at the record level.
I'm considering a history table that would be populated with a couple of fields from each table (not the entire asset record) just the date, notes, and person assigned, and somehow copy the record being viewed in the form by the tech (maybe with a macro button?). This is my first database, and I'm not sure what the best way would be to accomplish this. I'm not trying to do a complete change log for every edit (such as updating user information); this isn't a change log in that sense, just specific actions of recording when a device is sent out or returned. I would prefer a manual button.
This method implies that:
- the data source (Table or Query) of the Form has a unique ID,
- the command button name to log a row is Command_Log
- the Table that stores the logs is named Tbl_Log
- the unique ID column is numeric:
Private Sub Command_Log_Click
Dim strSQL As String
strSQL = "INSERT INTO Tbl_Log ( <comma-separated list of the columns names in Tbl_Log> ) & _
"SELECT ( <Comma-separated list of the columns names in the data source>) & _
"WHERE <Name of the unique ID column> = " & Me!<Name of the unique ID column>
CurrentDb.Execute strSQL, dbFailOnError
If the unique ID column is of type Text, use:
"WHERE <Name of the unique ID column> = '" & Me!<Name of the unique ID column> & "'"