Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

    Access 2010 table data macro

    Just trying to get some familiarity with Table Data Macros.


    Does anyone have an example, or a link, to show how to get the RecordID of the record being deleted?
    Could be some action(s) in the table Before Delete macro, but I haven't found anything. I can get the RecordID for After Insert and After Update actions, but can not find sample with the Before or After Delete.

    Thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The macro doesnt need the ID, the query does.
    the query, qdDeleteId=
    delete * from table where [id]=forms!myform!txtID

    the macro runs :
    openquery "qdDeleteID"

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Thanks for responding ranman, but a table data macro does not require a query.
    I have just started with data macros and am using google and youtube to find details/examples.
    In 2010 and above table data macros supposedly have the same functionality of a "trigger" in other databases.
    That is changes to tables can be "triggered" from anywhere - query, form, vba, regular macros, direct interaction with the table.

    I'm looking for an example(s) that shows how to capture information about the record that is being deleted - specifically an autonumber pk (recordId).
    I'd like to build an audit log to record any/all changes (add/edit/delete) to records in a table. I can do add/edit and most of delete (except for capturing the recordID).

    Thanks again for responding.

  4. #4
    Kudos is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    Switch to the After Delete event and use the syntax [Old].[recordID] to capture the value you need.

    Create a Record in tblAudit
    SetField
    Name tblAudit.DeletedrecordID
    Value [Old].[recordID]

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I am testing with the AfterDelete event. Here is a picture of the issue using a sample from my AuditLog.

    I can record the RecordID for Insert and Update, but I can not get the recordID for DELETEs.
    If you look at recordId 1, you can see the recordID (ProjID) for for Create and UPDATE, but there is no RECORDID for the DELETE. The value does show up in the ProjID field in log record 699. I'm trying to capture the value to record it under RecordID? I have tried ProjID and Old.ProjID but nothing has worked.
    As I have said, I'm new to this so may be missing something basic.
    Thanks for responding.

    Code:
    AuditID RecordID TableName FieldName ActionCode NewValue OldValue ChangedBy ChangedDate
    697 3 tblProjDemo ProjActualHrs CREATE mellon 14-May-2017 10:59:50 PM
    698 3 tblProjDemo ProjDesc UPDATE Office equipment and furniture Ofice equip and furniture mellon 14-May-2017 11:00:22 PM
    699 tblProjDemo projID DELETE 1 mellon 14-May-2017 11:00:26 PM
    Last edited by orange; 05-17-2017 at 07:38 AM. Reason: remove extraneous code

  6. #6
    Kudos is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    I would just check your macro very carefully. I don't think there is anything unique about a record ID field that would cause it to behave differently so my guess would be that your delete macro is simply missing the instruction to copy that ProjID value to the RecordID field. It should be something like this, to use your table/field names. You're completely sure this is present in the after delete macro?

    Create a Record in AuditLog
    SetField
    Name AuditLog.RecordID
    Value [Old].[ProjID]

    Attached a 2013 database with an audit that captures record id and text when record is deleted from main table.
    Attached Files Attached Files

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your response has me looking carefully at my set up. And I am second guessing a few things.

    I have a named macro on the tblAuditLog that writes records record to the table. (macWriteAuditLog)
    I have parameter for the fields and setFields to put values on the fields to be written.
    The one involved here is parmRecordID with description PK RecordID of record being actioned
    I have a named macro on the table I am auditing (tblProjDemo) (macProjDemodAudit)
    In macProjDemodAudit I check each field for If Updated( fldname) and runMacro macWriteAuditLog

    In the AfterInsert, AfterUpdate of tblProjDemo I identify the ActionCode and the parmRecordID (ProjID).

    In the AfterDelete macro I am setting parmRecordID to OLD.ProjId, but I now see I am calling macProjDemodAudit. I was anticipating it would write the audit record since that macro runsDataMacro macWriteAuditLog ( but now I see that's true if a field(s) was updated.)

    However, I don't have an Else condition, if the Updated is false??? I don't have that for AfterINSERT either,but the AfterInsert works as expected. This is confusing... Perhaps I should be calling macWriteAuditRec directly???

    If in the AfterDelete I make a runDataMacro to macWriteAuditLog, I am asked to input all parameters again. Not a big deal, but my point with the named data macro (macProjDemodAudit) was to call it from the After..... passing the ActionCode and the RecordId parm.

    I will try (later tonight) to use either an Else condition in (macProjDemodAudit) or directly call macWriteAuditLog passing all parameters (repeats the effort of the named macro partially??).

    Part of my confusion is that Updated is critical for AfterUpdate, but I have nothing for After Insert/Create or Delete.
    I do have another macro to write /log Delete records and the field values at time of delete (planned). I would also like to demo a "logical delete" where a flag is set to "IsDeleted", but the record remains in the database.

    This is all demo/learning on my part. Trying to get an AuditLog using data macros, not form events and vba.
    Using youtube and forums to get info.


    Do you have suggestions? Other thoughts?

    Thanks for responding.

    Update: I just realized you had posted an example while I was responding.
    Last edited by orange; 05-17-2017 at 07:04 AM. Reason: remove older graphic

  8. #8
    Kudos is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    All my tiny database really shows is that the Old.ProjID is definitely available to the After Delete event. You're passing an After Delete event parameter through an After Update event parameter and it looks like it's not surviving the journey - the Old.ProjID seems to be making it through to the OldValue field via the parmOldValue = [Old].[ProjID] in the After Update event...so my guess would be that what is being written to the recordID field is the 'new' ProjID of the deleted record...a null value.

    You're doing stuff I've never tried though! I would probably not attempt to pass an After Delete event through an After Update event. I'd repeat the parameter input and point it straight to the audit table macro.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Yes, I recognize our set ups are different.
    I'm trying to work with named macro with parameters, and call it from events on different tables to make a more universal audit log. I do get the old id in the OLD Field Value, but I was trying to format the log to show the recordID (pk in this example).
    In effect, I'm trying to put that PK into a Parm and write that value as the "RecordID" on the auditlog table.
    I have tried parmRecordID =[Old].[ProjId] and parmRecordId = [Projid]----neither one provides the value???

    There's not a lot of details or examples for data macros.
    Again thanks for responding.

  10. #10
    Kudos is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    If you change nothing in your macros except changing (within macProjDemodAudit) parmRecordID = [ProjID] to parmRecordID = [Old].[ProjID] and then trigger it with your delete event, does anything appear in that RecordID field in the audit log?

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Click image for larger version. 

Name:	AfterDeleteCallingmacProjDemodAudit.jpg 
Views:	45 
Size:	10.0 KB 
ID:	28688

    AuditLog records no value in RecordID with Delete (neither ProjId nor Old.ProjId)


    The macro is AfterDelete.
    Last edited by orange; 05-17-2017 at 07:34 AM. Reason: remove older code

  12. #12
    Kudos is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    Don't change the After Delete one. Change the After Update one, then trigger it with your After Delete event (which tries to pass in the Old.ProjId).

    I think I'm just convinced that your After Delete parameters are getting completely overwritten by the After Update parameters. You could pass in a seahorse and it will still come out [ProjID], which is no use in the case of a deleted record.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Not sure I'm following your post:
    I just set up AfterUpdate to pass Old.projId as parmRecordID
    and AfterDelete to use ProjId as parmRecordID
    Deleted a record (17) and nothing in the RecordID on the AuditLog.
    Last edited by orange; 05-17-2017 at 07:33 AM. Reason: older code removed

  14. #14
    Kudos is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    Hang on a sec, in your After Update event macro, shouldn't your ParmRecordID = [ParmRecordID] not [ProjID]? You have it defined as a parameter at the top but then it's not used as a value.

    (And yup, you followed. Since it was able to write the 17 to the OldValue using the syntax [Old].[ProjID] in the After Update macro, I expected it to also be able to write that value to the RecordID field using identical syntax, indicating that it was completely ignoring the parameter you tried to pass in through the After Delete event. However...it still failed. This wasn't a solution anyway, since "parmRecordID = [Old].[ProjID]" is not correct for the After Update macro. I'm still surprised that it didn't work.)

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Yes, you're correct. I just revised the macro parameter in macProjDemodAudit from ProjId to parmRecordId . It's the parameter passed in the AfterDelete [Old].[ProjId] that gives the parm its proper value. I tried a delete. !!!! It's now giving the value.
    Thank you for digging into this, it certainly helped me rethinking/questioning the setup. It's a learning effort, but seems to be more trial and error than expected. Debugging these macros is far from intuitive.
    Code:
    AuditID RecordID TableName FieldName ActionCode NewValue OldValue ChangedBy ChangedDate
    943 21 tblProjDemo projID DELETE 21 mellon 16-May-2017 11:22:42 PM
    944 21 tblProjDemo ProjName DELETE nnnnnnnnnnnnn mellon 16-May-2017 11:22:42 PM
    945 21 tblProjDemo ProjDesc DELETE nnnnnnnnnnnnnbbbbbbbbbbb mellon 16-May-2017 11:22:42 PM
    946 21 tblProjDemo ProjMgr DELETE dertwsa mellon 16-May-2017 11:22:42 PM
    947 21 tblProjDemo ProjDueDate DELETE mellon 16-May-2017 11:22:42 PM
    948 21 tblProjDemo ProjEstHrs DELETE mellon 16-May-2017 11:22:42 PM
    949 21 tblProjDemo ProjActualHrs DELETE mellon 16-May-2017 11:22:42 PM
    Last edited by orange; 05-16-2017 at 09:41 PM. Reason: clarification of parameter values and event involved.

Page 1 of 2 12 LastLast
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