A
Amer
I want to record a time stamp (date and time) whenever a check-box is checked
in the form.
in the form.
W
Wayne-I-M
HiAmer
2 ways to to do it.
The 1st will alter the stamp each time (so you could have something like -
last time altered, etc)
The 2nd will record each time the time is altered.
1st method
In the table (the form is based on) create a new field (AlteredTD).
Format this to Time Date (you can decide on which "bts" of the time date you
want to record)
On the form create a new unbound control (you want a tik box but I would use
a button - just my preference - up to you)
Add the new Time Date field from the table
Use this OnClick of the button
Private Sub ButtonName_Click()
Me.AlteredTD = Now
End Sub
2nd Method
In the table the form is based on create a new field call TimeDateID (Format
as a number)
Create a new table (tblAltered)
Add fields
TimeDateID - Autonumber
AlteredTD - set format as time date as you want.
Link your main table to the new table (using TimeDateID) in the
relationships window
Base the form on a query
Bring in the new table you have created
Bring in TimeDateID and AlteredID into the grid
Add this new field as a bound control into the form
Add the OnClick event to a button again.
You will now be able to produce a report or have a continous form showing
each time the button was clicked
Having siad all this I woundn't do it this way.
If you really want to record when a record is altered you should not depend
on someone clicking something on the form - use an AfterUpdate event to add
the record (if you are using method 2) or to set the value (if using method 1)
Good luck with your project
2 ways to to do it.
The 1st will alter the stamp each time (so you could have something like -
last time altered, etc)
The 2nd will record each time the time is altered.
1st method
In the table (the form is based on) create a new field (AlteredTD).
Format this to Time Date (you can decide on which "bts" of the time date you
want to record)
On the form create a new unbound control (you want a tik box but I would use
a button - just my preference - up to you)
Add the new Time Date field from the table
Use this OnClick of the button
Private Sub ButtonName_Click()
Me.AlteredTD = Now
End Sub
2nd Method
In the table the form is based on create a new field call TimeDateID (Format
as a number)
Create a new table (tblAltered)
Add fields
TimeDateID - Autonumber
AlteredTD - set format as time date as you want.
Link your main table to the new table (using TimeDateID) in the
relationships window
Base the form on a query
Bring in the new table you have created
Bring in TimeDateID and AlteredID into the grid
Add this new field as a bound control into the form
Add the OnClick event to a button again.
You will now be able to produce a report or have a continous form showing
each time the button was clicked
Having siad all this I woundn't do it this way.
If you really want to record when a record is altered you should not depend
on someone clicking something on the form - use an AfterUpdate event to add
the record (if you are using method 2) or to set the value (if using method 1)
Good luck with your project
K
Ken Sheridan
Wayne:
The triggering of the AfterUpdate event doesn't necessarily mean a row has
been 'altered' only updated; in terms of the data content it can be exactly
as it was before. You might be interested in the following module:
''''module starts''''
' 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''''
which works by storing the current values in a data structure then comparing
doing the same with the values after an update and comparing the contents of
each. Its called in a form's module like this:
''''module starts
Option Compare Database
Option Explicit
Private Sub Form_AfterUpdate()
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)
StoreNewVals rst
If RecordHasChanged() Then
MsgBox "Record has changed"
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 MyTable WHERE MyID = " & Me.MyID
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
StoreOldVals rst
End If
End Sub
''''module ends''''
The insertion of a new row is regarded as a 'change' as well as the editing
of an existing row.
Ken Sheridan
Stafford, England
The triggering of the AfterUpdate event doesn't necessarily mean a row has
been 'altered' only updated; in terms of the data content it can be exactly
as it was before. You might be interested in the following module:
''''module starts''''
' 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''''
which works by storing the current values in a data structure then comparing
doing the same with the values after an update and comparing the contents of
each. Its called in a form's module like this:
''''module starts
Option Compare Database
Option Explicit
Private Sub Form_AfterUpdate()
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)
StoreNewVals rst
If RecordHasChanged() Then
MsgBox "Record has changed"
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 MyTable WHERE MyID = " & Me.MyID
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
StoreOldVals rst
End If
End Sub
''''module ends''''
The insertion of a new row is regarded as a 'change' as well as the editing
of an existing row.
Ken Sheridan
Stafford, England
W
Wayne-I-M
Hi Ken
I just tried the module - it's really good. I will keep for when needed in
any thing I'm doing that needs if thats OK. (I have a store - folder) of
stuff that may come in useful.
Thank you
I just tried the module - it's really good. I will keep for when needed in
any thing I'm doing that needs if thats OK. (I have a store - folder) of
stuff that may come in useful.
Thank you
K
Ken Sheridan
Just don't sue me if it breaks!
Wayne-I-M said:
A
Amer
Thank you for your valuable assistance. I tried the first way and it really
works.
I will consider using the AfterUpdate as well.
Regards,
Amer
works.
I will consider using the AfterUpdate as well.
Regards,
Amer
A
Amer
I guess Wayne-I-Am wouldn't favor your solution less than I do.
Thanks for the help.
Regards,
Amer
Thanks for the help.
Regards,
Amer
W
Wayne-I-M
OK - I hold the lawers backI have quite a few - just in case - codes that I keep just as they are
interesting or I may want to use them (or bits of them) in the future.
eg. (will never be used ?) I have a code for importing the bar code scanner
results from a mobile phone (I have a Nokia 90 with a bar code reader). I
have a chinese text converter (mind you we do import from china so "may"
bring this in one day to copy the e mails) etc etc etc - as I said most of
the codes are "just in case"
Thanks again
W
Wayne-I-M
Ken's code is a little like windows back up - it looks for alterations where
mine is just a simple "do this now" code.
So they are 2 different things. You need to decide what your requirements
are, and use the best thing for the job.
Hope this has been some help.
Good luck with your project
mine is just a simple "do this now" code.
So they are 2 different things. You need to decide what your requirements
are, and use the best thing for the job.
Hope this has been some help.
Good luck with your project