Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    Kudos is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29

    Yay! Glad I could help (after leading you in fruitless circles for a bit first).

    Does your After Update event still work though? You will have to pass in that parameter and if you do it within the After Update macro, it will likely overwrite the one you try to pass in from the After Delete macro, right? You'll end up with null values in the recordID again.
    Last edited by Kudos; 05-16-2017 at 09:49 PM. Reason: Question

  2. #17
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Sometimes you just need someone to ask the right question, or suggest a few options. Often another pair of eyes can help focus the issue.
    Thanks again.

    Have you done a lot of work with macros?
    This was my first effort with data macros. I was trying to help someone previously with an audit using forms and vba. The poster found an error and after some investigation we found that the original source was not deleting records correctly. I found that Access deletes (puts a deleted record into an area not exposed to the user; and the record pointer moves off that record; and if the delete is confirmed, Access continues on; and if the delete is canceled the record is returned to the user with its ID intact).
    It just seemed convoluted to use Forms and vba and events to create an Audit trail. especially one that demanded you use forms -it wouldn't handle delete from query or direct table interaction.

    The data macro approach to audit log handles all situations (but not all data types --MVF, attachments.)
    Thanks again, your efforts are much appreciated.

  3. #18
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Quote Originally Posted by Kudos View Post
    Yay! Glad I could help (after leading you in fruitless circles for a bit first).

    Does your After Update event still work though? You will have to pass in that parameter and if you do it within the After Update macro, it will likely overwrite the one you try to pass in from the After Delete macro, right? You'll end up with null values in the recordID again.
    Yes it all seems to work now. AfterInsert,AfterUpdate pass parmRecordID=[ProjId], AfterDelete passes [Old].[ProjID].
    I created record 24 and 25. Updated 25 then deleted 24. The associated log records are:

    Code:
    AuditID RecordID TableName FieldName ActionCode NewValue OldValue ChangedBy ChangedDate
    951 24 tblProjDemo projID CREATE 24 mellon 16-May-2017 11:54:50 PM
    952 24 tblProjDemo ProjName CREATE kudos1 mellon 16-May-2017 11:54:50 PM
    953 24 tblProjDemo ProjDesc CREATE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa mellon 16-May-2017 11:54:50 PM
    954 24 tblProjDemo ProjMgr CREATE aaaaaaaaa mellon 16-May-2017 11:54:50 PM
    955 24 tblProjDemo ProjDueDate CREATE 17-May-2017 mellon 16-May-2017 11:54:50 PM
    956 24 tblProjDemo ProjEstHrs CREATE mellon 16-May-2017 11:54:50 PM
    957 24 tblProjDemo ProjActualHrs CREATE mellon 16-May-2017 11:54:50 PM
    958 24 tblProjDemo ProjEstHrs UPDATE 2 mellon 16-May-2017 11:54:58 PM
    959 24 tblProjDemo ProjActualHrs UPDATE 1.4 mellon 16-May-2017 11:54:58 PM
    960 25 tblProjDemo projID CREATE 25 mellon 16-May-2017 11:55:32 PM
    961 25 tblProjDemo ProjName CREATE kudos2 mellon 16-May-2017 11:55:32 PM
    962 25 tblProjDemo ProjDesc CREATE bbbbbbbbbbbbbbbbbbbbbbb mellon 16-May-2017 11:55:32 PM
    963 25 tblProjDemo ProjMgr CREATE bbbbbbb mellon 16-May-2017 11:55:32 PM
    964 25 tblProjDemo ProjDueDate CREATE 08-Jul-2017 mellon 16-May-2017 11:55:32 PM
    965 25 tblProjDemo ProjEstHrs CREATE 200 mellon 16-May-2017 11:55:32 PM
    966 25 tblProjDemo ProjActualHrs CREATE 0 mellon 16-May-2017 11:55:32 PM
    967 25 tblProjDemo ProjDesc UPDATE bbbbbbbbbbcccc bbbbbbbbbbbbbbbbbbbbbbb mellon 16-May-2017 11:55:48 PM
    968 25 tblProjDemo ProjDueDate UPDATE 20-Jul-2017 08-Jul-2017 mellon 16-May-2017 11:55:48 PM
    969 25 tblProjDemo ProjDueDate UPDATE 11-Jul-2017 20-Jul-2017 mellon 16-May-2017 11:56:00 PM
    970 25 tblProjDemo ProjEstHrs UPDATE 205 200 mellon 16-May-2017 11:56:00 PM
    971 24 tblProjDemo projID DELETE 24 mellon 16-May-2017 11:56:17 PM
    972 24 tblProjDemo ProjName DELETE kudos1 mellon 16-May-2017 11:56:17 PM
    973 24 tblProjDemo ProjDesc DELETE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa mellon 16-May-2017 11:56:17 PM
    974 24 tblProjDemo ProjMgr DELETE aaaaaaaaa mellon 16-May-2017 11:56:17 PM
    975 24 tblProjDemo ProjDueDate DELETE 17-May-2017 mellon 16-May-2017 11:56:17 PM
    976 24 tblProjDemo ProjEstHrs DELETE 2 mellon 16-May-2017 11:56:17 PM
    977 24 tblProjDemo ProjActualHrs DELETE 1.4 mellon 16-May-2017 11:56:17 PM

  4. #19
    Kudos is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    My experience with them started here, about a year ago:

    https://scottgem.wordpress.com/2012/...data-macros-2/

    ...but based on what you're doing I think I'm going to get a little more adventurous in my tactics. Like you, I was not impressed with the drawbacks of the vba/form approach to auditing. Data macros have the upper hand, except for that data type limitation (they can't handle long text, either). Pretty solid in all other areas though!

  5. #20
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It's midnight here, so I'm off to bed.
    I appreciate your help.

    I saw Scott's blog. And a few others, that's what piqued my interest.

  6. #21
    Kudos is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    This whole thread, you were stating that macProjDemodAudit was a named macro on the table and my brain simply didn't parse that because I had no idea you could have a named macro on the table that wasn't directly embedded in a change event.


    Every time I asked you to change your After Update macro, I was trying to ask you to change macProjDemodAudit. Well done on reading between the lines of my garbled troubleshooting!

    And I am so glad I looked at your question. The fact that you can have a named macro just sitting on the table changes EVERYTHING. This is so exciting.

  7. #22
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My intent is to make a demo database that uses data macros and post it in the samples section.
    I did a vba/forms/events revision (see post #43) to Martin Green's AuditTrail. The poster and I agreed that his routine did not handle deletes as expected/advertised. I could not sort out his use of ActiveForm ( I had an issues with it) so revised the set up with a new Action.
    But as we discussed, the data macro ("trigger-like") approach has benefits from a theory/learning perspective. It doesn't matter whether it is a query, form event, direct table interaction or some vba manipulation -the auditlog gets triggered. From a database perspective, whether or not you should physically delete records, is a business/audit/security question. We often used "shadow delete" --that is setting a boolean flag to IsDeleted or similar - such that the flag was considered in all routines. If the flag was set, the record was treated as deleted, if not set it was an Active record. Default was null or No (false). Bottom line - records were not physically deleted.
    Although I avoided macros and prefer vba, it was the poster who had tried Scott's routine and Martin Green's and others without success that renewed my interest in a "practical audit example". From my reading/googling (and Scott's blog) I saw how named macros with parameters could be called/run from different situations/conditions and could share a common "log". I have not dealt/reviewed "plain Jane or UI macros", but have seen some youtube videos that use macros and no vba.

    Thanks for your posts, they made me look at the logic and parameters in more detail.
    Seems it was a shared learning process. Can't beat that!.
    Last edited by orange; 05-21-2017 at 09:21 AM. Reason: clarification

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

Similar Threads

  1. Replies: 4
    Last Post: 01-21-2015, 09:34 AM
  2. Replies: 4
    Last Post: 11-26-2014, 12:25 PM
  3. Data Macro in Access 2010
    By rkalapura in forum Forms
    Replies: 4
    Last Post: 12-08-2012, 08:09 PM
  4. 2010 Data Macro weirdness with Parameters
    By mkenyon2 in forum Programming
    Replies: 3
    Last Post: 10-09-2012, 10:12 AM
  5. Data Macro in Access 2010
    By rkalapura in forum Programming
    Replies: 1
    Last Post: 07-06-2012, 08:38 AM

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