NauticalGent
Ignore List Poster Boy
- Local time
- Yesterday, 23:14
- Joined
- Apr 27, 2015
- Messages
- 6,321
As a follow-up to this post (https://www.access-programmers.co.uk/forums/showthread.php?t=294056) ,
I have gone about as far as I can and could use a hand. The users/clients have asked me to re-establish a way to determine who made the last change to a record and what (fields) were changed. What's more, in the past, they simply just hovered the mouse over the field in the form and it told them who made the change. In the past, this was done in a VERY inefficient manner which caused the underlying tables to have about 2/3 the amount of fields required.
I decided on the Audit Trail mentioned in the link because it seemed the most dynamic and simple.
To give the customer what they asked for and not revive the "monster", I decided to make a Sub that would provide what they wanted from the AuditTable (Which I affectionately re-named "TattleTale")
The Sub:
To call the Sub, I use the following on the MouseMove event:
If possible, I would like to avoid having to place this Sub on each applicable Field's MouseMove event.
As always, I KNOW I tend to GoAroundMeArse-HoleToGetToMeElbow. With that in mind if there is a better way to achieve this, I am ALL ears (eyes).
Thanks in advance!
I have gone about as far as I can and could use a hand. The users/clients have asked me to re-establish a way to determine who made the last change to a record and what (fields) were changed. What's more, in the past, they simply just hovered the mouse over the field in the form and it told them who made the change. In the past, this was done in a VERY inefficient manner which caused the underlying tables to have about 2/3 the amount of fields required.
I decided on the Audit Trail mentioned in the link because it seemed the most dynamic and simple.
To give the customer what they asked for and not revive the "monster", I decided to make a Sub that would provide what they wanted from the AuditTable (Which I affectionately re-named "TattleTale")
The Sub:
Code:
Private Sub SetControlTip(ctl As Control)
On Error GoTo err_handler
Dim dbs As dao.Database
Dim qdf As dao.QueryDef
Dim rst As dao.Recordset
Dim strRec_ID As String
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qrySelectAuditTrail") 'Parameterized query to select info
strRec_ID = Trim(Str([RECID])) 'Ask me how many times it took me to figure that bit out!
With qdf
.Parameters("[strRecID]") = strRec_ID
.Parameters("[strField]") = ctl.Name
Set rst = .OpenRecordset
If rst.RecordCount > 0 Then ' Took me a minute to realize I needed this too...
ctl.ControlTipText = "Old Value: " & rst.Fields(3) & vbCrLf _
& "New Value: " & rst.Fields(4) & vbCrLf _
& "Edited By: " & rst.Fields(5)
Else
ctl.ControlTipText = "No History"
End If
End With
exit_handler:
rst.Close 'Lets see if this bit revives some old arguments!
Set rst = Nothing
dbs.Close dbs
Set dbs = Nothing
err_handler
MsgBox Err.Number & " - " & Err.Description
Resume exit_handler
End Sub
Code:
Private Sub UpDate_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Call SetControlTip(UpDate)
End SubAs always, I KNOW I tend to GoAroundMeArse-HoleToGetToMeElbow. With that in mind if there is a better way to achieve this, I am ALL ears (eyes).
Thanks in advance!
Last edited:
