record start/finish dates of a combo box selections

[SpiritedAway]

Hi

I have a table [tblDocuments] with a field [Status] which is a combo box to update the cycle of the document. (i.e/ not started, draft, review, executed, approved etc..). The combo is a lookup field from a look up table [tblLookupStatus].

The user now wants to be able to do this: Each time a user selects from the combo box - a date is recorded when the option was selected. Before the user can then choose something else from the combo box the system will record the date his option was deselected/changed.

So you will end up with something like this.


Document 1. Started. Started 13/10/2010 Ended 14/10/2010
Draft. Started 14/10/2010 Ended 20/10/2010
Reviewed Started 20/10/2010 Ended 21/10/2010
Actioned Started 21/10/2010 Ended 01/11/2010
etc....
Document 2. Started....etc


Currently in [tblDocuments]. Each record [DocID] is for one document and the status is changed using the combo box to change a different status. I want to keep [DocID] for one document in [tblDocuments]

But I need another table perhaps that will record each cycle of that document and record start date (when it was selected) and end date (when it was changed/deselected).

Can anyone give me some ideas or advice about how this would be done - any insight would be greatly appreciated.

I'm using Access 2007

Thanks again for all your help everyone.

Spirited Away

 

[Trevor G]

I would create a tblHistoryDateSelected table and the way forward then is to use some VBA code, you would have to use this direct on a form to populate the table with who did this, when was this selected and when was it changed/closed.

An example of this type of code is shown below:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblHistoryDateSelected")
With rst

.AddNew
.Fields("FromDateSelected").Value = Me.txtStartDate.Value
.Fields("ToDateSelected").Value = Me.txtEndDate.Value
.Fields("Username").Value = Environ("UserName")
.Fields("DateSystemAdded").Value = Date
.Fields("Monies").value = me.txtCountryCurrency.value * me.txtExchange.value")
.Update


End With

You would have to decide on the field names and also where the trigger should run this code, normally something like selecting from a combo box and use the AfterUpdate Event.

 

[SpiritedAway]

Hi Trevor,

Thanks for the help, I will try this tomorrow and update you with how I get on.

Thanks Again.

Spirited Away

 

[ddt1972]

I am trying to do something very similar, how did it work out for you Spirit?

 

[Trevor G]

Sadly some people forget they have asked for help and got a solution and don't follow it up with a reply.

The code I created will work for you just adjust the names as I suggested.

What are you trying to do?

 

[ddt1972]

I have an option group that the user clicks on a radio button to change the status of a job, ie active, awaiting parts, awaiting other....etc. I also have an audit trail using this code:

Option Compare Database
Const cDQ As String = """"
Sub AuditTrail(frm As Form, recordid As Control)
'Track changes to data.
'recordid identifies the pk field's corresponding
'control in frm, in order to id record.
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String
On Error GoTo ErrHandler
'Get changed values.
For Each ctl In frm.Controls
With ctl
'Avoid labels and other controls with Value property.
If .ControlType = acTextBox Then
If .Value <> .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "Audit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
'View evaluated statement in Immediate window.
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
End If
If .ControlType = acCheckBox Then
If .Value <> .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "Audit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
'View evaluated statement in Immediate window.
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
End If
If .ControlType = acComboBox Then
If .Value <> .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "Audit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
'View evaluated statement in Immediate window.
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
End If
End With
Next
Set ctl = Nothing
Exit Sub
ErrHandler:
'MsgBox Err.Description & vbNewLine _
' & Err.Number, vbOKOnly, "Error"
End Sub

I can't seem to get the option group added to the audit....

All help is appreciated.....

 

[Trevor G]

Could you upload a sample of your database with the form on it and someone will take a look for you.

 

[ddt1972]

Here is a copy of the form in question, and the module being called up for the audit trail.