Command button New Record/ Audit Report (Access)

Topic

New Reply

First, I’d like to say that is is a GREAT website and excellent tool for beginner Access users like myself. I often know what I want to do, but have know way of executing it. Each time I’ve placed a post, someones always been able to help me execute. Thanks!!!

Ok so on to my new issue. In the attached Chart of Accounts form, I would like to create a New record command button. Whenever I use the toolbox to create this, it states “You can’t go to specified record” when clicked upon. Why is it doing this, previously when I used this function, it would work correctly.

Also, I want to be able to create an “audit trail” of any changes that are made to records. I would like to be able to create a report that would provide me with the record that was altered, the date it was altered and by whom.

Thanks

Reply | Quote

Replies

In the database as attached, the query Chart of Accounts doesn’t return any records, since there are no ID’s that all 4 tables have in common.
Moreover, the query is not editable and hence, the form is not editable, and you cannot add new records.

Reply | Quote

Why is the query not editable? Is there something I could do to make it editable?

I deleted several of the records so that the database would be under the 100KB for attaching.

Reply | Quote

The query is not editable because you have one-to-many relationships from Oracle Chart of Accounts to the other three tables. A query based on Oracle Chart of Accounts and one other table would be updateable, but not with two or more other tables.

Reply | Quote

Thanks

Reply | Quote

Access is not the ideal application for keeping a full-blown audit trail. SQL Server is much more suitable for that.

Nonetheless, it is possible to keep track of who changed a record. In post 320697, you’ll find code for a simplistic audit trail. It only records whether a record has been added, modified or deleted. See Creating an Audit Log on Allen Browne’s website for a more complete audit trail.

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

Hi Hans,

I think the audit trail created by Allen Browne will work for me with one (slight or not so slight) modification. What needs to be changed in the code at his post: http://allenbrowne.com/AppAudit.html%5B/url%5D to pull the old value and the new value.

As always, thanks for the help!
Dashiell

Reply | Quote

Allen Browne’s code saves the old values in the temporary audit table in the AuditEditBegin function, called from the Before Update event of the form. If the update isn’t canceled, the AuditEditEnd function, called from the After Update event of the form, transfers the record from the temporary audit table to the audit table, and saves the new values there too. So updating a record in the form will result in two records in the audit table: one with the old values (identified by audType = “EditFrom”) and one with the new values (identified by audType = “EditTo”).

Reply | Quote