Update a date field if recordset is modified - help

[nan121]

Hi! I created a field (LastUpdate) in my table that inserts the current date when a new record is created, but I want it to be updated either through a form or the table itself (using any method possible) if any changes are made to the current record. I'm creating a report off the form that lets the user enter the first and last ID # of the records they want in the report, but I would also like it to be able to print those records modified by the current date. Any suggestions??

 

[JoeMiller]

In the AfterUpdate event of the form that the users change data on you can place code to change the date to your desired date like so:

[tt]
LastUpdate=Date()
[/tt]

HTH Joe Miller
joe.miller@flotech.net

 

[Vika]

If you make changes to the recordset from a form then each text box that can be changed should contain the following code on Change Event of this text box.


Private Sub txtName_Change()
txtLastUpdate = Now()
Me.Requery
End Sub


I would also recomend setting LastUpdate text box to be disable. This way nobody can just type in a date.

Hope it helps you!

 

[JoeMiller]

If the form displays one record at a time then AfterUpdate event looks at the whole record and will only fire when the record is updated in ANY field. So you only need to put the code in ONE place. And be warned, if you use Now() then you get the time & date in your LastUpdate field which can cause problems down the road when trying to pull records using dates only. Date() returns ONLY the system date from the computer being worked on.

Another note, in my example I updated the field LastUpdate and not a textbox (no need for a textbox at all) so that you could just place the code and have it update the date without letting the user know it was done. If you want the user to see the field, then Vika's suggestion to disable the control is very good. Joe Miller
joe.miller@flotech.net

 

[nan121]

Thanks guys! You are awesome! I combined your answers and used the AfterUpdate sub form and coded the

Private Sub User_Name_AfterUpdate()
Enter_Date = Now()
Me.Requery
End Sub

When I used the Change event it would change and lock up as soon as I typed a single character in the field. What does the Me.Requery do and can I modify it? Right now it will let me update one field and then it will update the date but jump back to the first record. I'd like it to stay in the same record. Is that possible? Thanks!!

 

[JoeMiller]

Me.Requery makes Access refresh the form, you can comment it out with a ' at the beginning of the line and you should be all set.
Joe Miller
joe.miller@flotech.net