How can I monitor a field in a table

[atrium]

I have a field in a table that randomly changes back to zero. In the program that updates the field as it should is fine, it updates it as it should. I have been right through all update queries that are used else where and not one update touches that field. I have had a look at the dependencies for the table and checked all the queries and forms used - solution.
Is there a way that in normal processing I can be alerted that the contents of the field has changed, when and by what program.

I hope someone can help

 

[Isaac]

Honestly the best advice is probably to continue to do a deeper search and find the real problem. It's somewhere.

But you could also create a Data Macro and see if it alerts you as to when this occurs. Maybe that will help you trace it? I've never used Data Macros, but I've heard of them.

 

[theDBguy]

Hi. The first thing that came to my mind was to use a Data Macro. However, I am not sure what information you can get out of it. We'll probably need to know more about your setup.

Edit: Oops, too slow...

 

[Galaxiom]

Could be an update query in VBA. Use Find in the editor to search for the table or field in the whole project.
Download and install V-tools and use the Total Deep Search to search everywhere in the project and database.

Is the field value definitely changing back from another value or is it a new record that got a zero inserted?

 

[atrium]

Galaxion, No it's definitely a field that had a value and now it's zero.

 

[Galaxiom]

Could there be another database front end connected to it?

Can users open the table in the Navigation Pane? Maybe a meddler has been going directly into the table and editing the value?

 

[arnelgp]

somebody is stealing the cookies?

 

[atrium]

Could there be another database front end connected to it?

Can users open the table in the Navigation Pane? Maybe a meddler has been going directly into the table and editing the value?

I don't believe they are
It makes more work for every one

 

[atrium]

somebody is stealing the cookies?

 

[atrium]

I have created a table data macro that will send me an email on after change and the value equals zero

 

[Pat Hartman]

Is the field required? Is there a validation rule that will work such as
>0
or
<> 0

Add the validation rule.

If it might be a user changing the value to zero, you can trap that in the Form's BeforeUpdate event. Again, it depends on what the valid range of values for the field is. In the BeforeUPdate event, you can allow 0 if that is the initial value (i.e.IsNull( Me.somefield.OldValue 0 = True) but not allow a non-zero value to be changed to zero.

A data macro might work but they do not support VBA so probably the best it can do is to log the event. I keep an UpdateDT and UpdateBy field on each table and populate them when the data is changed. It is not a real audit trail but it tells me who the last person to update the table was and when they did it. If you do that, you can log the entire record so you know when it happened and who did it. Then you need a function that runs whenever anyone opens the database and emails you if there is a record in the log table. The function should update the log record with a timestamp so that you don't get inundated with emails but get only one. Otherwise, it will be up to you to monitor the error log table at least daily to be able to catch the error immediately while the person who did it has some memory of what he did.

Although I agree, if a user didn't do it, it is a bug and you need to find it, prevention is most important if 0 is an invalid value.