Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    Audit trail delete

    All, I have code for audit trail on my database. It works but when delete, the ID field doesnt record. Here that part of the code:


    Code:
    Private Sub Form_AfterDelConfirm (Status as Integer)
    If Status =acDeleteOk Then Call AuditChanges ("EmplID", "Delete") 
    End Sub
    Any ideas of what I'm doing wrong?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Have you previously recorded the ID(s) being deleted? They're no longer available in that event. More here:

    http://allenbrowne.com/AppAudit.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thanks for responding...I didn't use Allen Brown's Audit code because it needs all the tables to have autonumber for Primary Ids.. Im using Martin Green from fontstuff.com

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I know Allen states must be an autonumber PK but really don't see why couldn't reference any field you want as the PK but then I've never implemented audit trail code. Oh, wait, I think it is to prevent a PK from being reused if a custom PK is generated based on the last PK in table, if the most recent record is deleted then PK could be re-generated. Process for creating custom PK would have to deal with this. Actual record deletion should be a rare event, if allowed at all. My process 'Voids' custom unique ID when record is no longer desired - record still exists and the PK is accounted for. So in the audit process this would be a record edit, not delete.

    Did you use Martin's alternate code?
    Have you step debugged?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I haven't used it or read the site, but I'm fairly certain that the ID is no longer available in the AfterDelConfirm event. Looks like Allen gets the ID(s) in the delete event and then uses it in the AfterDelConfirm event, but I haven't fully reviewed his code either. You might check Martin's sample db to see how it gets the ID.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thanks all for responding..I'll check Martin's sample to see how it's recorded...I didn't see an alternative code...I would hate to start from scratch with Allen's code but may have to...

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I was curious and tested. Martin's code doesn't actually work correctly. It gets the value from the form. I deleted customer ID 7, and the audit trail recorded ID 9, which was the record displayed after the delete. As I suspected, by the time that event fires the ID is gone and the next record is displayed.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    By the way, I shot him an email to alert him to this issue.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thank u...I've had some time to think about. It may be more beneficial to keep deleted records n maybe keep them in another table..again, thanks for all ur help. BTW, I have an earlier post for assistance in getting username recorded in the audit trail instead of userid..I'd appreciate any assistance..Audit trail username

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. I suspect most of us don't delete records or move them, we just flag them somehow (yes/ no field, status field, etc).

    I saw that thread earlier but I'm not sure how, presuming you mean getting the name from the network instead of the logged in user ID. You'd have to query Active Directory somehow. The last post here seems to work:

    https://www.accessforums.net/showthread.php?t=74134
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thanks...trying to get this code to work...will close this thread and concentrate on the pervious..

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. I tested that code to get the user name from the server and it worked for me.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Wait...u lost me...so I should create a table with userid and username and put this code in form open? How do I link it to the audit trail code?...

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No, this line from the last post linked to in post 11 gave my name rather than login name:

    sUser = objUser.DisplayName
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Audit Trail Help
    By HelpDesk in forum Access
    Replies: 9
    Last Post: 08-07-2015, 06:13 AM
  2. Having an audit trail
    By ryanmce92 in forum Modules
    Replies: 5
    Last Post: 06-03-2015, 02:29 PM
  3. Replies: 2
    Last Post: 02-09-2013, 12:39 PM
  4. Audit Trail
    By bigroo in forum Programming
    Replies: 4
    Last Post: 02-22-2012, 07:55 PM
  5. audit trail
    By slimjen in forum Forms
    Replies: 1
    Last Post: 10-13-2011, 01:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums