Write to Table when a Report is Opened

  • Thread starter Thread starter cbjames
  • Start date Start date
C

cbjames

I am looking for some code to place in all of my reports that will write
three values to the table [Report_Access] when a reports is open. (1) User
(2) Now() and (3) The Report Opened. This way we can track the "who,what and
when" of our reports. Any help would be greatly appreciated. Thanks.
 
cbjames said:
I am looking for some code to place in all of my reports that will write
three values to the table [Report_Access] when a reports is open.
(1) User (2) Now() and (3) The Report Opened. This way we can
track the "who,what and when" of our reports. Any help would be
greatly appreciated. Thanks.
Click to expand...

Public Function LogDoc(obj As Object)
On Error GoTo Err_Handler
'Purpose: Track who opened forms/reports when.
'Author: Allen Browne http://allenbrowne.com
'Release: Feb 2009.
'Variation: To log the Windows user instead of JET user, see:
' http://www.mvps.org/access/api/api0008.htm
'Usage: Set the On Load property of your forms:
' =LogDoc([Form]
' or for reports:
' =LogDoc([Report]
' Alternatively, in code (event procedure) use:
' Call LogDoc(Me)
'Assumes: Table named tblLogDoc, with fields:
' LogDocID AutoNumber Primary key
' DocName Text (64) name of form/report
' DocTypeID Number (Long) 2=form, 3=report
' DocUser Text (64) who opened it
' DocDateTime Date/Time when they opened it.
Dim strSql As String
Dim lngObjType As Long
Dim strUser As String

strUser = CurrentUser()
If TypeOf obj Is Form Then
lngObjType = acForm
ElseIf TypeOf obj Is Report Then
lngObjType = acReport
End If

strSql = "INSERT INTO tblLogDoc ( DocName, DocTypeID, DocUser,
DocDateTime ) " & _
"SELECT """ & obj.Name & """ AS DocName, " & lngObjType & " AS
DocTypeID, """ & _
strUser & """ AS DocUser, Now() AS DocDateTime;"
DBEngine(0)(0).Execute strSql, dbFailOnError

Exit_Handler:
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Function
 
Try this.....


Public Sub LogUse(strObjectName as string)
dim strSQL as string

strSQL = "INSERT INTO Report_Access ([Who], [What], [When] " & _
"VALUES ('" & environ("Username") & "',"' & strObjectName & "',
now())

CurrentDB.Execute strSQL

End Sub
 
This code worked perfect!!! I have pasted it on all of our reports and they
are tracking like a charm. Thanks so much for the time and effort.
-Charles James

Allen Browne said:
cbjames said:
I am looking for some code to place in all of my reports that will write
three values to the table [Report_Access] when a reports is open.
(1) User (2) Now() and (3) The Report Opened. This way we can
track the "who,what and when" of our reports. Any help would be
greatly appreciated. Thanks.
Click to expand...

Public Function LogDoc(obj As Object)
On Error GoTo Err_Handler
'Purpose: Track who opened forms/reports when.
'Author: Allen Browne http://allenbrowne.com
'Release: Feb 2009.
'Variation: To log the Windows user instead of JET user, see:
' http://www.mvps.org/access/api/api0008.htm
'Usage: Set the On Load property of your forms:
' =LogDoc([Form]
' or for reports:
' =LogDoc([Report]
' Alternatively, in code (event procedure) use:
' Call LogDoc(Me)
'Assumes: Table named tblLogDoc, with fields:
' LogDocID AutoNumber Primary key
' DocName Text (64) name of form/report
' DocTypeID Number (Long) 2=form, 3=report
' DocUser Text (64) who opened it
' DocDateTime Date/Time when they opened it.
Dim strSql As String
Dim lngObjType As Long
Dim strUser As String

strUser = CurrentUser()
If TypeOf obj Is Form Then
lngObjType = acForm
ElseIf TypeOf obj Is Report Then
lngObjType = acReport
End If

strSql = "INSERT INTO tblLogDoc ( DocName, DocTypeID, DocUser,
DocDateTime ) " & _
"SELECT """ & obj.Name & """ AS DocName, " & lngObjType & " AS
DocTypeID, """ & _
strUser & """ AS DocUser, Now() AS DocDateTime;"
DBEngine(0)(0).Execute strSql, dbFailOnError

Exit_Handler:
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Function
Click to expand...
 
I "pdf"ed this document and filed it in my Access Reference folder. I will
use it to experiment myself with writing some code, Thanks again.
-Charles

Allen Browne said:
Here's a more comprehensive solution:
Log usage of forms and reports
at:
http://allenbrowne.com/AppLogDocUse.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

cbjames said:
I am looking for some code to place in all of my reports that will write
three values to the table [Report_Access] when a reports is open.
(1) User (2) Now() and (3) The Report Opened. This way we can track
the "who,what and "when" of our reports. Any help would be greatly
appreciated. Thanks.
Click to expand...
Click to expand...
 
Thank you for the code.
-Charles

Rob Wills said:
Try this.....


Public Sub LogUse(strObjectName as string)
dim strSQL as string

strSQL = "INSERT INTO Report_Access ([Who], [What], [When] " & _
"VALUES ('" & environ("Username") & "',"' & strObjectName & "',
now())

CurrentDB.Execute strSQL

End Sub

cbjames said:
I am looking for some code to place in all of my reports that will write
three values to the table [Report_Access] when a reports is open. (1) User
(2) Now() and (3) The Report Opened. This way we can track the "who,what and
when" of our reports. Any help would be greatly appreciated. Thanks.
Click to expand...
Click to expand...
 
this was absolutely what i've been looking for!! thank you sooo much!
but...i need one more thing....
i also need to know how to make it log the "CaseID" number of the record in
my database. i have it so my auditors open the form by typing in the caseid
number.
I do not write code...yet....but copying and pasting worked great for this.
could you tell me how to add that one piece?
--
terri


Allen Browne said:
Here's a more comprehensive solution:
Log usage of forms and reports
at:
http://allenbrowne.com/AppLogDocUse.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

cbjames said:
I am looking for some code to place in all of my reports that will write
three values to the table [Report_Access] when a reports is open.
(1) User (2) Now() and (3) The Report Opened. This way we can track
the "who,what and "when" of our reports. Any help would be greatly
appreciated. Thanks.
Click to expand...
Click to expand...
 
nevermind, someone helped me get this last piece...thanx to all of you.
--
terri


terri said:
this was absolutely what i've been looking for!! thank you sooo much!
but...i need one more thing....
i also need to know how to make it log the "CaseID" number of the record in
my database. i have it so my auditors open the form by typing in the caseid
number.
I do not write code...yet....but copying and pasting worked great for this.
could you tell me how to add that one piece?
--
terri


Allen Browne said:
Here's a more comprehensive solution:
Log usage of forms and reports
at:
http://allenbrowne.com/AppLogDocUse.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

cbjames said:
I am looking for some code to place in all of my reports that will write
three values to the table [Report_Access] when a reports is open.
(1) User (2) Now() and (3) The Report Opened. This way we can track
the "who,what and "when" of our reports. Any help would be greatly
appreciated. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top