Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
LastUpdated
- Thread starter dirkg
- Start date
Depends on your definition of simple! ;-)<br><br>Access does not automatically time/date stamp records when they are changed. You can "easily" set this up by forcing all changes to be made through forms. You would add a field to any table that you want to audit, call it LastChanged. Then you would write code behind the form to post the current date and time to the LastChanged field whenever a field is changed. You could also capture the login of the person who changed it, but that is a bit more complicated.<br><br>Hope this helps. <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>
- Thread starter
- #3
I wrote a FAQ on this you might find helpful in the "Access: General Discussion" forum on this site. I think it is titled something like "getting user lgoin and date" and it tells how to capture both when a record is added or updated.
I didn't write this but it works; it needs a table to store the log:
==========================================================
Option Compare Database 'Use database order for string comparisons
Option Explicit
Const ahtcLogAdd = 1
Const ahtcLogUpdate = 2
Const ahtcLogDelete = 3
Function ahtLog(strTableName As String, varPK As Variant, intAction As Integer) As Integer
' Log a user action in the log table
On Error GoTo ahtLog_Err
Dim db As Database
Dim rstLog As Recordset
Set db = CurrentDb()
Set rstLog = db.OpenRecordset("ChangeLogging", dbOpenDynaset, dbAppendOnly)
With rstLog
.AddNew
![UserName] = fUserNTIdent 'modules to get the ID are readily available. Let me know if you cannot find one.
![TableName] = strTableName
![RecordPK] = varPK
![ActionDate] = Now
![Action] = intAction
rstLog.Update
End With
rstLog.Close
ahtLog = True
ahtLog_Exit:
On Error GoTo 0
Exit Function
ahtLog_Err:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "ahtLog()"
ahtLog = False
Resume ahtLog_Exit
End Function
Function ahtLogAdd(strTableName As String, varPK As Variant) As Integer
' Record addition of a new record in the
' log table
On Error GoTo ahtLogAdd_Err
ahtLogAdd = ahtLog(strTableName, varPK, ahtcLogAdd)
ahtLogAdd_Exit:
On Error GoTo 0
Exit Function
ahtLogAdd_Err:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "ahtLogAdd()"
Resume ahtLogAdd_Exit
End Function
Function ahtLogDelete(strTableName As String, varPK As Variant) As Integer
' Record deletion of a record in the
' log table
On Error GoTo ahtLogDelete_Err
ahtLogDelete = ahtLog(strTableName, varPK, ahtcLogDelete)
ahtLogDelete_Exit:
On Error GoTo 0
Exit Function
ahtLogDelete_Err:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "ahtLogDelete()"
Resume ahtLogDelete_Exit
End Function
Function ahtLogUpdate(strTableName As String, varPK As Variant) As Integer
' Record updating of a record in the
' log table
On Error GoTo ahtLogUpdate_Err
ahtLogUpdate = ahtLog(strTableName, varPK, ahtcLogUpdate)
ahtLogUpdate_Exit:
On Error GoTo 0
Exit Function
ahtLogUpdate_Err:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "ahtLogUpdate()"
Resume ahtLogUpdate_Exit
End Function
==========================================================
Enjoy - Joe McDonnell
==========================================================
Option Compare Database 'Use database order for string comparisons
Option Explicit
Const ahtcLogAdd = 1
Const ahtcLogUpdate = 2
Const ahtcLogDelete = 3
Function ahtLog(strTableName As String, varPK As Variant, intAction As Integer) As Integer
' Log a user action in the log table
On Error GoTo ahtLog_Err
Dim db As Database
Dim rstLog As Recordset
Set db = CurrentDb()
Set rstLog = db.OpenRecordset("ChangeLogging", dbOpenDynaset, dbAppendOnly)
With rstLog
.AddNew
![UserName] = fUserNTIdent 'modules to get the ID are readily available. Let me know if you cannot find one.
![TableName] = strTableName
![RecordPK] = varPK
![ActionDate] = Now
![Action] = intAction
rstLog.Update
End With
rstLog.Close
ahtLog = True
ahtLog_Exit:
On Error GoTo 0
Exit Function
ahtLog_Err:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "ahtLog()"
ahtLog = False
Resume ahtLog_Exit
End Function
Function ahtLogAdd(strTableName As String, varPK As Variant) As Integer
' Record addition of a new record in the
' log table
On Error GoTo ahtLogAdd_Err
ahtLogAdd = ahtLog(strTableName, varPK, ahtcLogAdd)
ahtLogAdd_Exit:
On Error GoTo 0
Exit Function
ahtLogAdd_Err:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "ahtLogAdd()"
Resume ahtLogAdd_Exit
End Function
Function ahtLogDelete(strTableName As String, varPK As Variant) As Integer
' Record deletion of a record in the
' log table
On Error GoTo ahtLogDelete_Err
ahtLogDelete = ahtLog(strTableName, varPK, ahtcLogDelete)
ahtLogDelete_Exit:
On Error GoTo 0
Exit Function
ahtLogDelete_Err:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "ahtLogDelete()"
Resume ahtLogDelete_Exit
End Function
Function ahtLogUpdate(strTableName As String, varPK As Variant) As Integer
' Record updating of a record in the
' log table
On Error GoTo ahtLogUpdate_Err
ahtLogUpdate = ahtLog(strTableName, varPK, ahtcLogUpdate)
ahtLogUpdate_Exit:
On Error GoTo 0
Exit Function
ahtLogUpdate_Err:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "ahtLogUpdate()"
Resume ahtLogUpdate_Exit
End Function
==========================================================
Enjoy - Joe McDonnell
I was looking for a solution to a similar problem and came across this.
Is there an any way, last update date for record can appear directly in a field within the table without having to use a form. Or in other words, could we not have a code directly add date to a table field as soon as a record is saved in that table.
Greetings
AK
Is there an any way, last update date for record can appear directly in a field within the table without having to use a form. Or in other words, could we not have a code directly add date to a table field as soon as a record is saved in that table.
Greetings
AK
- Status