Time Stamp when record is changed

[Pat Hartman]

If the notes are in a separate table, then there is no problem. If the notes are in the same table, why not just include the column in the RecordSource and update it with:
Me.MyNotes = "blah blah"

If the notes are in a separate table and I know I want to update them, I just include the table in my RecordSource query. I never run update queries for stuff like this. Update queries are for bulk updates. If I already have a recordset open with the record I want to update, why incur the overhead of running a query? not to mention the database bloat if you actually have the SQL string in your code. Plus you also generate this conflict message if the record is dirty.

Any field update that you want to do should probably be done in the Form's BeforeUpdate event. Most of my tables have LastChangedDate and LastChangedBy columns and I populate those in the BeforeUpdate event of each form. These columns are not usually visible and are not bound to controls on the form.

Me.LastChangedDate = Now()
Me.LastChangedBy = Environ("UserName")

 

[pr2-eugin]

Thank you so much Pat.. I understand this.. I also did not know that you will be able to update fields just by using Me.Field_name.. I though that can be done only for visible fields.. or fields that are on the Form.. That is the only reason I used UPDATE. after you mentioned I tried using it.. Works great.. Thanks once agin for opeing my eyes..

 

[Pat Hartman]

Glad to help
I should add a warning though. Reports act a little differently. For reports, Access rewrites the RecordSource query (because it is so much smarter than we are) and that means that you can't reference columns that YOU think are in the RecordSource because unless you bound them to controls, they aren't! For reports, I create hidden bound controls and shrink them to be very tiny.

 

[nadzira]

hi guys,

I just want to know if this coding works for other database?
and I want to know where to put this coding because I faced the same problem which I want to record the timestamp of the gridview that has been modified by users. FYI I am using VS2010.

Hopefully u guys can help me..thank u very much..

 

[Cronk]

There can be issues with using single quotes around names when dealing with someone called O'Leary. Best to use chr(34) to insert double quotes.

Also, why not just set the timestamp to Now() rather than formatting a variable. And the ProductID is likely to be a long integer, not an integer.

Apologies for being pedantic - I just like avoiding possible future problems.

 

[nanscombe]

Thank you so much Pat.. I understand this.. I also did not know that you will be able to update fields just by using Me.Field_name.. I though that can be done only for visible fields.. or fields that are on the Form.. That is the only reason I used UPDATE. after you mentioned I tried using it.. Works great.. Thanks once agin for opeing my eyes..

A bit late to the party but where Me.Field_name (Me dot) can only be used to update fields which on the form whereas Me!Field_name (Me bang) can also be used to update fields in the underlying recordsource regardless of it being bound to a control on the form.

It's apparently a bit more complex than that but that is the simplistic view.

 

[pr2-eugin]

Hey Nigel, I am so glad I have made good progress.. LOL.. I did not know anything about Bang and Dot then, now I do.. It's always good to hear from you, thanks for sharing..

 

[DHudson]

what about if I'm not using forms, but manually updating fields

 

[Cronk]

You can also manually change the Timestamp field as well, but with the Jet engine in Access databases, there is no direct trigger available at the table level to lead to automating field data changes.

Another reason for only changing data through forms where business rules can be enforced.