Re: Updated datestamp doesn't work
- From: Piperlynne <Piperlynne@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 16 Jan 2009 07:55:01 -0800
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.
"Ken Sheridan" wrote:
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" wrote:
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" wrote:
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" wrote:
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" wrote:
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" wrote:
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" wrote:
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
"Graham" wrote:
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.
- References:
- Re: Updated datestamp doesn't work
- From: Piperlynne
- Re: Updated datestamp doesn't work
- From: Ken Sheridan
- Re: Updated datestamp doesn't work
- From: Piperlynne
- Re: Updated datestamp doesn't work
- From: Ken Sheridan
- Re: Updated datestamp doesn't work
- From: Piperlynne
- Re: Updated datestamp doesn't work
- From: Ken Sheridan
- Re: Updated datestamp doesn't work
- Prev by Date: Re: Need help with this please.
- Next by Date: Re: Database Documenter
- Previous by thread: Re: Updated datestamp doesn't work
- Next by thread: Need help with this please.
- Index(es):
Relevant Pages
|
