Capture record change on linked table

[btrini10]

I am working with an application that has a backend database and a front end web GUI. It is used for Timesheet capture. I have created an access database that links to all the necessary tables in the application. My issue is, users are allowed to unsubmit a timesheet via the web interface. This action changes the status from 3 to 4. Is there any way for me to capture whenever a record's status is changed to 4. Even if I open my access database after a user has unsubmited and re-submited, in which case the status went from 3 to 4 and back to 3, I would like to record that the status was changed to 4.

My access database allows managers to approve submited timesheets. A timesheet with a status of three that does not have an approvers initial is available to be approved. If the employee unsubmits and then resubmits, the status goes from 3 to 4 and back to three, but because my database does not currently recognize that the time was unsubmited and re-submited, it does again make the timesheet available for approval. Regretfully, the application does not record the time the timesheet is submited so I am not able to compare approval time to submit time.
Hopefully you are all not too confused
Any ideas would be appreciated.

 

[lameid]

Unfortunately the Access datbase engine, Jet, does not have this functionality. If you were using Access for the application or front end, I would suggest you use after update events to code logging to a table.

If you were to move your database to SQL server, you could write a Trigger to accomplish this kind of logging. I believe most of the major server database packages support this (Oracle, MYSQL).