Last Edit Field Update 1

[sunwindandsea]

First time creating an Access database and I'm having trouble figuring how the code to udpate a field,"Last Edit Date" whenever any field in the record is changed.

Data
Event
On Change
Private Sub e_Mail_AfterUpdate()
tblContact.Last_Edit_Date = Now()
End Sub
doesn't work Obviously there is a way to change the date-time stored in a date field in the record if any field in the rerdord is changed. I just having trouble writing the code.

Thanks,

Ed

 

[cmmrfrds]

If the recordset is bound to the Form then the Form can be referenced as ME. The update is done through the recordset not through a table name.

Me.Last_Edit_Date = Now()

 

[cmmrfrds]

Whoops, missed one thing.

The afterupdate() event is after the recordset has been updated and commited. Use the beforeupdate() event if you want the database updated.

 

[sunwindandsea]

Thanks for the imputcmmrfrds but it still doesn't update the date-time in the field. Here's the current code

Private Sub Last_Edit_Date_BeforeUpdate()
Me.Last_Edit_Date = Now()
End Sub

The field control source is Last_Edit_Date
The form is bound to the the correct table, Table Source: tblContact.

What am I doing wrong? Missing?

 

[cmmrfrds]

Have you tabbed off the record or otherwise committed the update to the table, before checking to see if it was updated?

At this point.
Me.Last_Edit_Date = Now()
add.
Debug.Print "edit date = "; Me.Last_Edit_Date

Then after running do a "Control G" to look at the results. If you don't see the results then for some reason you are not getting to the event. How are you checking that the field was updated?

 

[sunwindandsea]

Thanks cmmrfrds for the reply.

1) This field is updated if any field in the datbase has been changed. I guess I did't make that lcear in my query.

2) I guess that I should have specified or asked how can I test if any field in the record has been changed prior to exiting the current record and moving to the next record? I usually click on the previous and next arrows at the bottom of the form to move to another record.

Should I add a Save button and only allow changes made using the save button?

Thanks for all your help,

Ed

 

[PHV]

In the BeforeUpdate event procedure of the form check the Me.Dirty property:
If Me.Dirty Then Me.Last_Edit_Date = Now()

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cmmrfrds]

PHV has got it. You need to be at the Form level to catch individual fields that don't themselves change. If there is no change to the individual field the events for the field are not executed.

 

[sunwindandsea]

Thanks, PHV for your help.

I'll setting forth the solution for any user who reads this thread.

1) Click on amy field and select properties.

2) Scroll through the items in the window at the top of the page until you "Form." Why didn't Microsoft include the font and color properties on these pages so that these could be set globally?

3) Select the "Event" tab.

4) Click in the "On Dirty" field.

5) Click on the ellipsis to the right of the field and to the left of the scroll bar.

5) From the items displayed in the pop-up "Choose Builder" window select "Code Builder." Click "OK."

6) At hte blinking cursor insert the following line

" Me.Last_Edit_Date = Now()"

THe procedure should appear as below:

Private Sub Form_Dirty(Cancel As Integer)
Me.Last_Edit_Date = Now()
End Sub
Thanks for everyone help.

Ed

 

[RoyVidar]

Erm - I think the suggestions by cmmrfrds and PHV is to use the before update event of the form not the on dirty event. The before update event of the form triggers whenever a save operation is attempted on a bound form - > whenever a new record is saved, or there's an attempt to save changes to the current record.

The .Dirty event of the form, triggers whenever there's a change in any of the bound controls on the form, even if you just accidently change a value and hit ESC, the .Dirty event has fired, and you've saved a timestamp for a record that hasn't been changed.

Roy-Vidar

 

[Henrietts]

Hi everyone, I am so glad to have found this resource. I hope you'll forgive my low level of programming skill- I'm a self-taught Access devotee working for a non-profit as a consultant after leaving FT work to be with my daughter. Visual Basic is something I hope to learn. Right now I'm just trying to get a last-updated field working.

My question about the above thread is this regarding #2: What exactly does "Scroll through the items in the window at the top of the page until you 'Form' " mean? Which window, the form design view or the properties of the field, and how does something "form" assuming that this is intended as a verb? I've tried popping the code (thank you *so* much for that) into a few different places as described but sense that I'm missing how to tie the action of any change to any field on the form to a change in the "last updated" field.

TIA,
Susan

 

[sunwindandsea]

# 2 Explained

The title bar is the top line of any window in almost all if not all WIndows OS applications.

After clicking on properties a window appears with a title bar and immediately below the title is a window with a scroll bar, the down arrow to the right of the window.

Click on the down arrow and a list of the part of a form, field names, label names, combo box names, etc will be displayed.

Scroll up/down until you are able to hightlight "Form" and away you go.