Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Command button New Record/ Audit Report (Access)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command button New Record/ Audit Report (Access)

    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.

  3. #3
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command button New Record/ Audit Report (Access)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command button New Record/ Audit Report (Access)

    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>post 320697</post#>, 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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command button New Record/ Audit Report (Access)

    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.

  6. #6
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command button New Record/ Audit Report (Access)

    Thanks

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command button New Record/ Audit Report (Access)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    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 to pull the old value and the new value.

    As always, thanks for the help!
    Dashiell

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command button New Record/ Audit Report (Access)

    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").

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •