After Insert Data MAcro, creating records in other tables

[Db-why-not]

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.

 

[theDBguy]

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]

Not sure if this screenshot is more helpful. My audit table has 7 fields in it.

 

[theDBguy]

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]

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.

 

[theDBguy]

Ok, I will try using an alias.

Not sure what it's called, but I hope I explained it clear enough. Let us know how it goes.

 

[jdraw]

You may find this link helpful with respect to data macros and audit trail.

 

[Db-why-not]

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.

 

[theDBguy]

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:

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]

Hi. Try specifying it's a local var, just like you did with tables. For example:

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]

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.