help on generic module for form events

[richardb]

Just hacking around with Access 2000 and will be using forms to update a variety of tables. I want to be able to record the time of change to each field in each table. Changes totables will take place through forms.

I'm sure people will have done this sort of thing before so some pointers would be welcome.

For example, a table might be a catalogue containing item names and item prices, with fields "name" and "price" respectively. I could add another two fields name_ch_time and price_ch_time to the table. The form would only show name and price.
In practice I'll have all sorts of tables and fields but I'd have a uniform naming convention for the changed time fields

My thought was to create a global module in which it's straightforward to grab the current form and the current control on an update event. But then I get a bit stuck. I want to do something like
...
Dim changeTime as String
....
changeTime = currentCtrlName "_ch_time"
currentForm!changeTime = Now()
...

I'm sure there must be away to substitute the value for changeTime rather than have it treated as a literal but I don't see how...

Thanks for any help, Richard.

[RhinoBull]

Not sure I followed you but as far as I did it looks like you want to set Date_Time_Modified whenever some field's value was modified ??? If that's the case then why not create a field say "Last_Modfied" and when record is going to be updated - set current date/time and perhaps WHO did it.

[richardb]

That is what I want to do. However, I might have 30 or 40 tables each with 15 or 20 fields. I didn't want to handcode it every time for every field. Instead I wanted a short global module which I could just call without arguments that would do all the legwork for me. - it detects the change, it knows which control and therefore which audit trail field to update. Yes, I'd use both the time and the username and may beeven the old value.

R.

[RhinoBull]

So, why do you need to go through those troubles instead of simply define couple of fields on "critical" (or main if you will) tables (I assume some basic relations are in place in your db) and always update them based on say some RecordID.
Whe record is accessed - you can always pull last modified stuff by its ID.