Updated datestamp doesn't work

[Graham]

I have a MainTable and an InputForm.
I have two columns on the MainTable, DateCreated & DateUpdated. Both are
formatted as short date, with an input mask.
DateCreated works fine with†=Date()†in the Default Property of the
MainTable, and shows up in a control on InputForm, when a new record is
added, the current date is added.

DateUpdated should show the date, on that record, of any amendments to that
record. DateUpdated has no Default Property in the MainTable, and in the
MainForm Control Properties I have entered :

Private Sub DateUpdated_BeforeUpdate(Cancel As Integer)
Me.DateUpdated = Date
End Sub

It doesn’t work!

I’ve entered it in the BeforeUpdate & the AfterUpdate Event.

I’ve added () after Date
I’ve changed the fullstop in Me.DateUpdated to an exclamation mark
Me!DateUpdated

Nothing appears to work. Can anyone see where I’m going wrong ? Do I need to
be amending any other Prooperties ?
Many Thanks

 

[Ron2006]

As I read the code you have written, you are saying
Just before the update of the "Date-Updated" field you want the system
to put date into the Date-Updated field. In order for the action to be
performed you have to be changing the date you want updated.


Put this/that code in the afterupdate event of whatever fields you
want to trigger the updating of the Date_Updated.

or

To capture the date for any change to any field put this code in the
BeforeUpdate event of the form (next to the place where you see the
BeforeInsert and the AfterInsert.

Me.DateUpdated = Date


Ron

 

[Stockwell43]

Hi Graham,

If all your looking for is to enter a date in the field, use a command
button so the user can click the button to automatically enter the current
date of the amendment. Each time something is changed on the record, they can
just click the button to refresh with current date. Unless I am not
understanding what your looking for?

 

[John Spencer]

Try putting the code in the Before Update event of the FORM. Putting the
code in the control's before update event is not the correct place

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Graham]

Thank you all. I was putting the code in the 'Update' control, by placing it
in the Form properties it now works a treat. As soon as I change any of the
controls on the Input Form, this date is automatically updated.
I'd looked at loads of postings on this subject and not one mentioned that
the code should be in the Form properties rather than the Control Properties.
Many Thanks again!!

 

[Ken Sheridan]

There is one potential fly in the ointment here in that a user can update a
record without actually changing any data, i.e. they can change it and then
change it back to the original again. So if you want to assign the current
date to the DateUpdated column only if the data has in fact changed then you
need to check that the data has actually been amended. One way you can do
this is by first adding the following module to the database. Just open a
new standard module, paste in the code from below in place of the two lines
already there, and then save the module:


' module basChangedRecord

' determines if data in a record edited
' in a form has actually been changed

Option Compare Database
Option Explicit

' arrays for storing values from recordsets
Dim aOldVals(), aNewVals()

Public Sub StoreOldVals(rst As DAO.Recordset)

' store values of current row in array
aOldVals = rst.GetRows()

End Sub

Public Sub StoreNewVals(rst As DAO.Recordset)

' store values of edited row in array
aNewVals = rst.GetRows()

End Sub

Public Function RecordHasChanged() As Boolean

Dim n As Integer, intlast As Integer
Dim var As Variant
Dim aOld(), aNew()

intlast = UBound(aOldVals) - 1

' loop through array of original values
' and store in new array
For Each var In aOldVals()
ReDim Preserve aOld(n)
aOld(n) = var
n = n + 1
Next var

n = 0

' loop through array of edited values
' and store in new array
For Each var In aNewVals()
ReDim Preserve aNew(n)
aNew(n) = var
' if any value has changed then return True
If Nz(aNew(n), 0) <> Nz(aOld(n), 0) Then
RecordHasChanged = True
Exit For
End If
n = n + 1
Next var

End Function
' module ends


Then in the form's Current event procedure put the following code, changing
MyTable and MyID to the names of your table and its primary key column:


Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

If Not Me.NewRecord Then
strSQL = "SELECT * FROM MyTable WHERE MyID = " & Me.MyID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

StoreOldVals rst
End If


In the form's AfterUpdate event procedure put:


Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM MyTable WHERE MyID = " & Me.MyID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

If Nz(Me.DateUpdated) <> VBA.Date Then
StoreNewVals rst
If RecordHasChanged() Then
With rst
.MoveFirst
.Edit
.Fields("DateUpdated") = VBA.Date
.Update
End With
Me.Refresh
End If
End If

Ken Sheridan
Stafford, England

 

[John Spencer]

Ken,
Nice routine.

Question - does this handle changing null to zero or changing a zero to
null?

I think you attempted to handle nulls using the comparison
If Nz(aNew(n), 0) <> Nz(aOld(n), 0) Then

It seems to me this would fail if the Old value was null and the new
value was zero OR the other way round. An alternative might be

If (IsNull(aNew(n)) = True and IsNull(aOld(n)) = False) _
OR (IsNull(aNew(n)) = False and IsNull(aOld(n)) = True) _
OR (Nz(aNew(n), 0) <> Nz(aOld(n), 0)) Then

AND with that test you probably don't even need the calls to the NZ
function.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Piperlynne]

This is great. . .I'm trying to use it in my db.
However. . when I load the form I get an error on StoreOldVals that sub or
function not defined (on current event)

It's defined in the module. . I checked. . .soo. .. hmmm??? Any ideas???

 

[Ken Sheridan]

Firstly here's a revised version, which answers John's point, which I seem to
have overlooked at the time (sorry, John), but fortunately someone else
brought to it my attention in another thread:

' module basChangedRecord

' determines if data in a record edited
' in a form has actually been changed

Option Compare Database
Option Explicit

' arrays for storing values from recordsets
Dim aOldVals(), aNewVals()

Public Sub StoreOldVals(rst As DAO.Recordset)

' store values of current row in array
aOldVals = rst.GetRows()

End Sub

Public Sub StoreNewVals(rst As DAO.Recordset)

' store values of edited row in array
aNewVals = rst.GetRows()

End Sub

Public Function RecordHasChanged() As Boolean

Dim n As Integer, intlast As Integer
Dim var As Variant
Dim aOld(), aNew()

intlast = UBound(aOldVals) - 1

' loop through array of original values
' and store in new array
ReDim Preserve aOld(UBound(aOldVals))
For Each var In aOldVals()
aOld(n) = var
n = n + 1
Next var

n = 0

' loop through array of edited values
' and store in new array
ReDim Preserve aNew(UBound(aOld))
For Each var In aNewVals()
aNew(n) = var
' if any value has changed then return True
If (IsNull(aNew(n)) And Not IsNull(aOld(n))) _
Or (Not IsNull(aNew(n)) And IsNull(aOld(n))) _
Or aNew(n) <> aOld(n) Then

RecordHasChanged = True
Exit For
End If
n = n + 1
Next var

End Function


Delete your current module and replace it with the above. Be sure to save
it with a name other than that of one of the functions (as you see I call it
basChangedRecord). If you still have problems, and a compact/repair doesn't
help, then trying changing the function names to something like
StoreMyOldVals, StoreMyNewVals and MyRecordHasChanged for instance (just do a
couple of 'find/replace all' operations on the whole current project from the
VBA menu bar).

Ken Sheridan
Stafford, England

 

[Piperlynne]

Thanks Ken.

I updated and compiled/repaired and changed function names throughout.

Now I'm getting runtime '3021
No current record. Which I think something in the way I have this setup. . .

I'm going to go bang my head against the wall now ;-)

 

[Ken Sheridan]

Can you post back with the code you are using to call the functions in the
form's Current and AfterUpdate event procedures. On what line is the error
occurring?

Ken Sheridan
Stafford, England

 

[Piperlynne]

The line the error is actually occuring in is in the module

Option Compare Database
Option Explicit

' arrays for storing values from recordsets
Dim aOldVals(), aNewVals()

Public Sub StoreMyOldVals(rst As DAO.Recordset)

' store values of current row in array
aOldVals = rst.GetRows() <---- error happening here

End Sub


Here's the current and afterupdate call functions



Private Sub Form_AfterUpdate()


Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblItem WHERE SKU = " & Me.SKU

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

If Nz(Me.LastRevDt) <> VBA.Date Then
StoreMyNewVals rst
If MyRecordHasChanged() Then
With rst
.MoveFirst
.Edit
.Fields("LastRevDt") = VBA.Date
.Update
EndWith
Me.refresh
End If
End If
End Sub

Private Sub Form_Current()

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

If Not Me.NewRecord Then
strSQL = "SELECT * FROM tblItem WHERE SKU = " & Me.SKU

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

StoreMyOldVals rst
End If
End Sub

 

[Ken Sheridan]

All I can think of is that the form is bound to an empty table, i.e. you are
inserting the first record. Once a row exists in the table it should work.

I assume the SKU column is a number data type.

Ken Sheridan
Stafford, England

 

[Piperlynne]

I moved the current to before update and can now open the table without the
error occuring. However when I save a new record I get the same error. There
are already 340 records in the table and they do show up on the form. I
double checked the properties and the form is connected to the table.

I'm going to spend the day playing with it a bit and see if I can't get it
to work.