code to detect change in table field value

[Rasta]

Hi,

I have an Access 2000 database. One of the field values in one of the tables
appears to be randomly changing . This could be happening through code or
user interaction. I know tables don't have events, but is there a way to
write a procedure that fires when the value of this field changes and then
write the time of the change, new value, etc to a different table - sort of
a log table.

Thanks,
Amanda

 

[Larry Linson]

You are correct -- tables do not have events and you cannot do this at the
table level. If you want an event to fire on change, you must limit the
change to being performed in a form -- that means you create a "fully
developed application" in which no users (other than you/the developer) has
access other than through forms, not directly to tables, not to queries.

Most server databases allow you to define a "trigger" that fires under such
circumstances, but converting to a server data store.

If it were my database, I would first obtain the trial version of Find and
Replace, from Rick Fisher Software, http://www.rickworld.com, and use it to
find and examine all occurrences of the field in question in the entire
database. It could well be that the changes may be due to something you
have not considered. If I didn't find the problem then, I would consider
whether to clamp down on security of the application (which likely will
require development time and effort) or consider moving to a server back end
(which likely will require installation time and effort, and very possibly
development time and effort, also).

If that table is normally updated via a form, then I might add three
fields... time and date, user identification, and a copy of the field's
value... without informing the users and update them programmatically in the
AfterUpdate event of the suspect field. That way, when you find an invalid
value, you can determine if it was due to an input error by a user from the
form, or whether someone is accessing the table outside the "normal" way.

Larry Linson
Microsoft Office Access MVP