Audit Function - One to Many Query (RowSource)

[PShiers]

I found code for creating detailed field level audit funtionality and I copied. It is really good as I can added alot of additional details about who, what & when in the audit table. However, I've spotted then the form has a Rowsource that is a One to Many Query, the functionality doesn't work for the field from the One side of the query. Have scroll through many post and it clear the this code used by many, and I attached the code.

Example: Telephone number if in the primary table (Contract) and address name is in the many table (addresses). If I change any address field it works, but if I change telephone number (is bound) if fails, but it it have no address name. Although address name already has a value and is bound and is a text box .oldvalue is blank and shows as a variant/integer. if I remove the tag 'Audit' from address name the it fail on next field address Line 1. I've check address table to ensure all field are text and check the form that all field are text box.

Any help on why it doesn't work?

Thanks

 

[The_Doc_Man]

To have a bound control with an .OldValue of null, blank, or zero usually suggests that you are on a new record which didn't exist before and thus doesn't have an "old" anything. If the control in question were NOT bound, there would not be a .OldValue for comparison. I don't know why you would have a NEW record there, but that is what it looks like from your description. The code you showed us looks like it believes the parameter UserAction passed in to it but since we can't see the actual call sequence for this case, we can't tell what happens. So my next question is, how sure are you that UserAction doesn't pass in EDIT when it was really a NEW record?

 

[jdraw]

Can you point us to the link where you found your "audit code"?
As Doc said it seems you are dealing with a new record that would not have an old value.
Martin Green has posted an audit routine that works with Forms and has code for ADD, EDIT and DELETE. The routine had some issues as per an older thread.

 

[Pat Hartman]

Just because Access allows you to do something, doesn't mean that you should. This is one of those situations. When you have a 1-many relationship, use a main form to edit the 1-side record and a subform for the many-side. This eliminates all problems.

 

[PShiers]

To have a bound control with an .OldValue of null, blank, or zero usually suggests that you are on a new record which didn't exist before and thus doesn't have an "old" anything. If the control in question were NOT bound, there would not be a .OldValue for comparison. I don't know why you would have a NEW record there, but that is what it looks like from your description. The code you showed us looks like it believes the parameter UserAction passed in to it but since we can't see the actual call sequence for this case, we can't tell what happens. So my next question is, how sure are you that UserAction doesn't pass in EDIT when it was really a NEW record?

It 100% not a new record, attached is screen shot of contact form and address name is populated with 'House', also screen shot of beforeupdate code showing its doing edit, and screen shot of table row showing the addnme is 'House'. I've try deleting and rebuilding the screen, deleting and recreating the query.

 

[PShiers]

Can you point us to the link where you found your "audit code"?
As Doc said it seems you are dealing with a new record that would not have an old value.
Martin Green has posted an audit routine that works with Forms and has code for ADD, EDIT and DELETE. The routine had some issues as per an older thread.

Audit Trail for Access Database

Hi, How do i create an audit trail for an access database. Need to capture the following: Username (AD username) computer name (user computer) collumns changed in the tables date and time access / amendement thanks,

community.spiceworks.com

 

[ebs17]

When you have a 1-many relationship, use a main form to edit the 1-side record and a subform for the many-side.

I would like to emphasize this boldly.
Only the record that is in ONE table is really unique as an object. Therefore, if you want to add, edit or delete records, it is better to just access their table. Then there are never any problems with assignment, you just have to pay attention to the set referential integrity.
For the purpose of changing bound forms, you will give each table used its own form and combine them according to relationships.
This also works with the logging of changes, even at table level, in an Access backend using DataMacros.

 

[jdraw]

PShiers,
The article you referenced indicates that it is based on the Martin Green audit materials (fontstuff.com). That link has not been active for a few years. Others have tried to contact him re some noted issues, but have had no responses from him.

This is taken mostly from the following reference: http://www.fontstuff.com/access/acctut21.htm but I have modified it slightly to fit certain needs.

Allen Browne has an article Creating an Audit Log
I provided a demo database with audit using data macros in this thread.

 

[PShiers]

I would like to emphasize this boldly.
Only the record that is in ONE table is really unique as an object. Therefore, if you want to add, edit or delete records, it is better to just access their table. Then there are never any problems with assignment, you just have to pay attention to the set referential integrity.
For the purpose of changing bound forms, you will give each table used its own form and combine them according to relationships.
This also works with the logging of changes, even at table level, in an Access backend using DataMacros.

Thank, made change so address in displayed (locked) on the contact screen, added button 'Edit Address', so address screen shows and allows edit. Seem to have solved the problem, just need to apply to everything else.