Update and record

[Ripper T]

If the value in a form field will be periodically changed
(updated) , how can I record the new value, along with
the date and time of the update, in another table?

I'm using Access 2002.

Thanx,

Rip

 

[Larry Linson]

Use VBA DAO (or ADO) code to write a new Record to the other Table.

The code would be something like this (air) code:

Dim db as DAO.Database
Dim rs as DAO.RecordSet
Set db = CurrentDB()
Set rs = db.OpenRecordset("anotherTable")
rs.AddNew
rs("FirstFld") = Me!txtFirstFld
rs("UpdatedDateTime") = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

Larry Linson
Microsoft Access MVP

 

[RipperT]

Ok, I used this code suggested by Mr. Linson, which I
greatly appreciate, and tweaked it for my application. I
don't know code (clearly) but I'm not an idiot. I can
make this work with you guys' help.

Where:

"seg" is the name of the database
"Lock changes" is the name of the table I want to write to
"Lock" is the field I want to update from a form field
"Date" is the field I want the date (Now) to show up in
each time the value in the form field is changed

Private Sub Lock_Exit(Cancel As Integer)
Dim db As DAO.seg
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Lock changes")
rs.AddNew
rs("Lock") = Me!txtLock
rs("Date") = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

I get a compile error each time I leave the field. How do
I fix this code?

Thanx!
Rip