Yappy said:
I have followed the steps described on Microsoft Online to record the date
and time when a record is modified and added these fields to my form.
Is there a way to drill this process down to when a field has been
modified
and indicate which field(s) was/were modified? If so, I would need step
by
step direction for creating the code or information where I can find it.
I am using Access 2003.
Yes, Add code to the before update event It makes sense to add a separate
table which stores, the OldValue, and the date/time of change, that way you
can keep track of multiple changes.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strSQL As String
Dim lngCount As Long
Dim lngItemID As Long
Dim strContractorID As String
Dim strSubdivision As String
Dim lngModelID As Long
Dim dblCost As Double
Dim dblCostCode As Double
Dim dtmEffectiveDate As Date
Dim db As DAO.Database
Dim rstItems As DAO.Recordset
lngItemID = Me.txtItemID.OldValue
strContractorID = Me.txtContractorID.OldValue
strSubdivision = Me.txtSubdivision.OldValue
lngModelID = Me.txtModelID.OldValue
dblCost = Me.txtCost.OldValue
dblCostCode = Me.txtCostCode.OldValue
dtmEffectiveDate = Me.txtEffectiveDate.OldValue
Set db = CurrentDb
strSQL = "INSERT INTO tblItemHistory ( ItemID, Subdivision, ModelID,
CostCode, Cost, ContractorID, EffectiveDate )"
strSQL = strSQL & " VALUES (" & rstItems!ItemID & ", '" & strSubdivision &
"', " & lngModelID & ", " & dblCostCode & ", " & rstItems!Cost & ", '" &
rstItems!ContractorID & "', '" & rstItems!EffectiveDate & "');"
db.Execute strSQL
End Sub
To explain the code briefly, I've dim'd a variable for each value on the
form, and stored the OldValue of each textbox in it, then I've written that
data to a history table. You'll need to add error handling, as I stripped
this out of a much more complex piece of code.
