• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

After Insert Data MAcro, creating records in other tables (1 Viewer)

Db-why-not

Registered User.
Local time
Today, 14:21
Joined
Sep 17, 2019
Messages
124
I created an audit table for my database and I have been using the After Insert, After Update, After Delete Macros to create an audit trail in my audit_trail table. I created another Audit_Memo_fields table to track changes for my memo/ long text fields in my database. I can't get the macros to create a record in the Audit_Memo_fields table though. Not sure why it won't work. The two tables are exactly the same except one table has Long Text fields and the other table does not.

I have also noticed that when I try to create record in more than 1 table using the after delete/insert/update macros it only creates a record in the first table. If I change the order of the create record: table, whichever table is first will get the record put in but the second table will not have a record created in. I have included a screenshot of my macro for After Delete. I followed this website https://scottgem.wordpress.com/2012/10/18/audit-trail-using-data-macros-2/to create all the macros.
data-Macro.JPG
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:21
Joined
Oct 29, 2018
Messages
12,585
Hi. Not sure if I'm missing something without seeing your entire code, but I would probably try to store the data into local variables, if you plan on reusing them to create multiple records in different tables. Just a thought...
 

Db-why-not

Registered User.
Local time
Today, 14:21
Joined
Sep 17, 2019
Messages
124
data-Macro2 (1).JPG
Not sure if this screenshot is more helpful. My audit table has 7 fields in it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:21
Joined
Oct 29, 2018
Messages
12,585
View attachment 82449 Not sure if this screenshot is more helpful. My audit table has 7 fields in it.
Hi. My guess is the context is getting lost, somehow. So, as I was saying, try storing each value into a local variable, so they can persist longer than what it appears to be doing right now. That is, if you were saying the first Create a Record action is working but not the next or succeeding ones. That implies the values are lost after the first action completed. Just a thought...
 

Db-why-not

Registered User.
Local time
Today, 14:21
Joined
Sep 17, 2019
Messages
124
Hi. My guess is the context is getting lost, somehow. So, as I was saying, try storing each value into a local variable, so they can persist longer than what it appears to be doing right now. That is, if you were saying the first Create a Record action is working but not the next or succeeding ones. That implies the values are lost after the first action completed. Just a thought...
Ok, I will try using an alias.
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:21
Joined
Jan 23, 2006
Messages
13,372
You may find this link helpful with respect to data macros and audit trail.
 

Db-why-not

Registered User.
Local time
Today, 14:21
Joined
Sep 17, 2019
Messages
124
Not sure what it's called, but I hope I explained it clear enough. Let us know how it goes.
I am trying to use the SetLocalVar but it doesn't seem to be working.

This is within AfterInsert data macro for my US_Demographics table

SetLocalVar
Name US_ID_Var
Expression = [US_Demographics].[US_ID_FK]

Create a Record in Audit_trail
SetField
Name Audit_trail.TableName
Value = "US_Demographics"
SetField
Name Audit_trail.Record_ID
Value = [US_ID_var] 'SetLocalVar value
SetField
Name Audit_trail.FieldName
Value = "us_performed_by
SetField
Name Audit_trail.NewValue
Value = [US_Demographics].[us_performed_by]
SetField
Name Audit_trail.TypeChange
Value = "New Record"

Create a Record in Audit_trail
SetField
Name Audit_trail.TableName
Value = "US_Demographics"
SetField
Name Audit_trail.Record_ID
Value = [US_Demographics].[US_ID_FK] 'If I just use the value directly from the table it is creating the record for this one
SetField
Name Audit_trail.FieldName
Value = "us_number"
SetField
Name Audit_trail.NewValue
Value = [US_Demographics].[us_number]
SetField
Name Audit_trail.TypeChange
Value = "New Record"

It doesn't create the record in my audit table when I use the SetLocalVar as a value.

I didn't type out all the records I am creating I am only showing the first two records I have the Data macro creating. I am actually having the data macro create 22 different records in 3 different tables.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 12:21
Joined
Oct 29, 2018
Messages
12,585
I am trying to use the SetLocalVar but it doesn't seem to be working.

This is within AfterInsert data macro for my US_Demographics table

SetLocalVar
Name US_ID_Var
Expression = [US_Demographics].[US_ID_FK]

Create a Record in Audit_trail
SetField
Name Audit_trail.TableName
Value = "US_Demographics"
SetField
Name Audit_trail.Record_ID
Value = [US_ID_var] 'SetLocalVar value
SetField
Name Audit_trail.FieldName
Value = "us_performed_by
SetField
Name Audit_trail.NewValue
Value = [US_Demographics].[us_performed_by]
SetField
Name Audit_trail.TypeChange
Value = "New Record"

Create a Record in Audit_trail
SetField
Name Audit_trail.TableName
Value = "US_Demographics"
SetField
Name Audit_trail.Record_ID
Value = [US_Demographics].[US_ID_FK] 'If I just use the value directly from the table it is creating the record for this one
SetField
Name Audit_trail.FieldName
Value = "us_number"
SetField
Name Audit_trail.NewValue
Value = [US_Demographics].[us_number]
SetField
Name Audit_trail.TypeChange
Value = "New Record"

It doesn't create the record in my audit table when I use the SetLocalVar as a value.

I didn't type out all the records I am creating I am only showing the first two records I have the Data macro creating. I am actually having the data macro create 22 different records in 3 different tables.
Hi. Try specifying it's a local var, just like you did with tables. For example:

Rich (BB code):
SetField
Name  Audit_trail.Record_ID
Value = LocalVars![US_ID_var]            'SetLocalVar value
Hope that helps (untested - please double check the syntax)...
 

Db-why-not

Registered User.
Local time
Today, 14:21
Joined
Sep 17, 2019
Messages
124
Hi. Try specifying it's a local var, just like you did with tables. For example:

Rich (BB code):
SetField
Name  Audit_trail.Record_ID
Value = LocalVars![US_ID_var]            'SetLocalVar value
Hope that helps (untested - please double check the syntax)...
Its working now. I had to make the Value = [US_ID_var], I thought it was that but when I changed it to LocalVars![US_ID_var] it didn't work. Then I changed it back to Value = [US_ID_var] and then it worked, not sure what was happening. Thanks for your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:21
Joined
Oct 29, 2018
Messages
12,585
Its working now. I had to make the Value = [US_ID_var], I thought it was that but when I changed it to LocalVars![US_ID_var] it didn't work. Then I changed it back to Value = [US_ID_var] and then it worked, not sure what was happening. Thanks for your help.
Hi. It's magic! Glad to hear you got it working. Good luck with your project.
 

Users who are viewing this thread

Top Bottom