Last date of update saved on UpdatedOn when Save Button is click

  • Thread starter Thread starter Neil
  • Start date Start date
N

Neil

I added a new fieldname Last Update in a table. It is already added in the
form. When changes are made in the form and when i click the save button, the
current date should appear. If the form is open and no changes are made, when
we click the save button, the Last Update date will not change.

Please help me on this. Thanks in advance.
 
Hi Neil,
You can put code in the Before Update event of the form to do this.
Put it at the end of any validation code in the before update event.
It is 99% effective. If a user makes an edit then presses Esc to cancel the
edit, access still treats the form as if it is dirty.

Here is the code.
Me.LastUpdate = Now()

If you want to be sure that you only save LastUpdate when there is a real
change to data, you can use something like-->

If Me.ControlName1 <> Me.ControlName.OldValue1 _
& Or Me.ControlName2 <> Me.ControlName.OldValue2 Then
Me.LastUpdate = Now()
End If


Replace ControlName1 and 2 with the names for your form.

If you have a lot of controls on your form, you can use something like
this-->

Public Function RealChangeCheck(frm As Form) As Boolean
'check if ctl values are changed
Dim ctl As Access.control

For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acCheckBox, acListBox
'Debug.Print ctl.Name & ": " & ctl
If Not ctl.Locked = True And ctl.Visible = True Then
If frm.NewRecord = True Then
If Len(ctl.Value & vbNullString) > 0 Then
RealChangeCheck = True
Exit For
End If
Else
If ctl.Value <> ctl.OldValue Then
RealChangeCheck = True
Exit For
End If
End If
End If
End Select

Next ctl

End Function

You call this function in the before update of the form like this-->
If RealChangeCheck(Me) = True Then
Me.LastUpdate = Now()
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Ms. Jeannete,

Can you clear this one for me.

You mean to put the code in the properites of the form and not on the Save
Button?
 
The close button has this code-->

Private Sub cmdClose_Click()
If Me.Dirty Then
Me.Dirty = False
End If


Replace cmdClose with the real name of the close button on your form.

When you click the close button, the code checks if the form is dirty. If
the form is dirty, the code in the Before Update event for the form runs.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
If the form is open and no changes are made, when
we click the save button, the Last Update date will not change.
Click to expand...

Would you want it to change, given that there was in fact no update? If the
user just looks at the form and does NOTHING, the form's BeforeUpdate event
will not fire.

If you *do* want it to update in that situation you will need to program your
own Save button (do you have one?) and use it to explicitly set the [Last
update] control to Now().
 
If you *do* want it to update in that situation you will need to
program your own Save button (do you have one?) and use it to
explicitly set the [Last update] control to Now().
Click to expand...

Well, that depends on whether you want to store the time or just the
day. If you want the day, use Date() instead. I don't like storing
date and time in the same field because it makes querying on date
rangers (without respect to time) harder. I also think that it's
used in a lot of cases where it shouldn't be, because of the way the
Access wizards and sample databases are configured. There's no
excuse at all on a report to have a control with Now() as the
Controlsource and a Short Date format, for instance.
 
Back
Top