how to track when a record changes status (1 Viewer)

[atol]

Hey guys,
Here is my dilemma: I need to produce some metrics showing when a record has changed its state.
So I have a table, called tblRule. For each Rule, the user would populate a filed “CurrState” from a combo-box on a form. Now, I have to find a way to track somehow when a specific Rule changes from one “CurrState” to another… for example:
Rule # 12345 changed from state “reviewed” to state “approved”
Rule # 12388 changed from a state “updated” to a state “reviewed”, and so on.
Those various states belong to the same combo-box.
This change must be simultaneous, so one Rule can have only one state at a time. But when flips, I somehow need to remember what was the previous state, so I can prepare the metrics.
The metrics should show how many rules have moved from a state to state daily. And for every rule what state is currently and what was the prior state.
I guess I need to do some changes to the table, or I need to run some code (and I don’t know what or how to write that code)?
I tried to do some searches on the topic but it was a challenge as well….
I hope my explanation on what I am trying to do makes sense.
Any assistance is greatly appreciated.
Atol

 

[MarkK]

Your StateChangeEvent should be in a table. And you don't so much change states of the parent as you append a new state onto the parent, and the parent's state is then calculated as being the one with the Max() date.

tStateChangeEvent
StateChangeEventID (PK)
ParentObjectID (FK)
DateTimeOfChange (time dimension)
StateValue (value dimension, or FK link to state object if state is complex type)

And the state of the parent object is always calculated ...

State = currentdb.openrecordset( _
   "SELECT TOP 1 StateValue " & _
   "FROM tStateChangeEvent " & _
   "WHERE ParentObjectID = " & Me.MyID & " " & _
   "ORDER BY DateTimeOfChange DESC;").Fields(0)

... as the most recent state belonging to the parent.
And now calculating your metrics will be a breeze.

 

[gemma-the-husky]

another way is to "audit" changes

when anything happens that you need to track, get the change details saved into an audit table, or log file.

note that any software checks of any nature will be circumvented if data is changed other than by "authorised" means. ie - if they don't use your form to change the data, and just change the vlaue directly in the table, your audit steps will be avoided.

 

[atol]

Guys, thease are great ideas. Thank you both very much.
I will play around home tonight and if i have some clarification, i will post out...
Thank you again!!
Atol

 

[Rx_]

It depends on the purpose. For some things, a field for "created Date" just auto assigns NOW in the table def. Two other fields are "last updated" and "USER ID Last Updated".

For many things, a Transaction log is used. Build a standard Function and then call it at places of interest.
e.g. Call LogUsage3("Permit Type Change " & Me.APD_Fed_Status.Text, "Permit Tab", [Forms]![Home_2]![lst_id_Audit].Column(1)) ' Audit name
e.g. Call LogUsage3("Delete Record " & Me.txtDateSubmitted.value & " " & Me.cboSundryDescription.Column(0), "Completion Tab", [Forms]![Home_2]![lst_id_Audit].Column(1)) ' Delete action with detailed information of what, who and when

'---------------------------------------------------------------------------------------
' Procedure : LogUsage3
' Author    : Rx_
' Date      : 9/16/2011  orginal copy Sept 16th 2011 for network delay tracking
' Purpose   : Logs general user activity to table tLogUsage3
' location  : General Module so that any form, button or object can call it
' Stress Test:   20 concurrnet users in aggressive loop
'---------------------------------------------------------------------------------------
'  Create table named tLogUsage3
'  Add field (date/ time) UseDate, strFormName, CallingProc, Username, ControlName
'  use: in code, typically at command buttons or other key areas, add code to log
'   the name of the form, the calling procedure, and the control name
'   in no time, there will be users stats to show what parts the users like the most in your application
'    Example:  Call LogUsage("frmMainSwitchboard", "cmdOpenReport_click", "cmdOpenReport")
Function LogUsage3(ByVal strFormName As String, _
    strCallingProc As String, Optional DelayTime) As Boolean
10       On Error GoTo Err_LogUsage3
          Dim rst As DAO.Recordset  ' The tLogUsage table
20            Set rst = CurrentDb.OpenRecordset("tLogUsage3", 2, dbAppendOnly + dbSeeChanges)
30            rst.AddNew
40                rst![UseDate] = Now()
50                rst![strFormName] = strFormName
60                rst![CallingProc] = strCallingProc
70                rst![UserName] = Environ("username")
80                If Not IsMissing(DelayTime) Then
90                    rst![DelayTime] = Left(DelayTime, 75)
100               End If
110           rst.Update
120           rst.Close
130           LogUsage3 = True

Exit_LogUsage3:
140       Set rst = Nothing
150       Exit Function

Err_LogUsage3:
          ' just resume next and the usage is not logged
  'MsgBox Err.Number & Err.Description
160       Err.Clear
170       Resume Exit_LogUsage3
End Function

Each LogUsageTable keeps track of what form, what field, what user, and what time something changed. By filtering a User ID and Form name - all the data entry and the time it took to enter can be provided.
This function is widely used. This is one of a dozen tracking tables. This one probably has about 100,000 records a month added.

Rule: If you don't want to be able to explain something - it is simple - just don't log it!