creating report user log

[T]

I'm trying to determine the useage of varioius reports I have. I need
the following to occur when a report is opened, get the name of the
report, datetime, username.
I created a tblLog w/reportname, reportdate and theuser.

This is the module I have for capturing the username.

' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiGetUserName Lib "advapi32.d ll" Alias _
"GetUserNam eA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName( strUserName, lngLen)
If ( lngX 0 ) Then
fOSUserName = Left$(strUserNa me, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

How would I use this, can it be done on the onopen event of all
reports? How would I use above and insert it into the tblLog

Module for inserting reportname, reportdate into the table
Function ReportUsage(InR eportName As String)
On Error GoTo Error1
Dim db As DATABASE
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordse t("tblReportUsa ge") 'name of your table
rs.AddNew
rs("ReportName" ) = InReportName
rs("ReportDate" ) = Now 'Never call a field "Date" !!
rs.Update
Exit1:
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
Error1:
MsgBox Err.Number & "; " & Err.Description
Resume Exit1
End Function

[tina]

you have the log table already, so just create an Append query to add a
record to the table. run the Append query in the same code (or macro) that
opens the report. an example of the SQL statement follows:

CurrentDb.Execu te "INSERT INTO tblLog ( rptName, " _
& "rptDate, rptUser ) SELECT '" & strReportName _
& "', #" & Date & "#, '" & fOSUserName & "'", _
dbFailOnError
hth
"T" <te****@wideope nwest.comwrote in message
news:11******** **************@ d57g2000hsg.goo glegroups.com.. .

I'm trying to determine the useage of varioius reports I have. I need
the following to occur when a report is opened, get the name of the
report, datetime, username.
I created a tblLog w/reportname, reportdate and theuser.

This is the module I have for capturing the username.

' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiGetUserName Lib "advapi32.d ll" Alias _
"GetUserNam eA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName( strUserName, lngLen)
If ( lngX 0 ) Then
fOSUserName = Left$(strUserNa me, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

How would I use this, can it be done on the onopen event of all
reports? How would I use above and insert it into the tblLog

Module for inserting reportname, reportdate into the table
Function ReportUsage(InR eportName As String)
On Error GoTo Error1
Dim db As DATABASE
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordse t("tblReportUsa ge") 'name of your table
rs.AddNew
rs("ReportName" ) = InReportName
rs("ReportDate" ) = Now 'Never call a field "Date" !!
rs.Update
Exit1:
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
Error1:
MsgBox Err.Number & "; " & Err.Description
Resume Exit1
End Function

[Don Leverton]

Yup ... you've got it!
Here it is with some very minor revisions:
=============== =============== =============== =======
Module for inserting reportname, reportdate, (AND UserName) into the table.

Function ReportUsage(InR eportName As String)
On Error GoTo Error1

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordse t("tblLog", dbOpenDynaset) 'name of your table is
"tblLog" you said, correct?

With rs
.AddNew
!ReportName = InReportName 'Calling this function from ANY report will
supply the report name
!ReportDate = Now 'Never call a field "Date" !!
!TheUser = fOSUserName
.Update
.Close
End With

Exit1:
db.Close
Set rs = Nothing
Set db = Nothing
Exit Sub

Error1:
MsgBox Err.Number & "; " & Err.Description
Resume Exit1

End Function
=============== =============== =========
All you need to do is to put that function in a module in the DB window, so
that it is available globally.
I'd stick your function in the same module as Dev's API.

Put this in each report's code module
=============== =============== =========
Private Sub Report_Open(Can cel As Integer)
DoCmd.Maximize
ReportUsage (Me.Name) ' The report name will be passed to the function and
inserted in your log table
End Sub
--
--
HTH,
Don
=============== ==============
E-Mail (if you must) My*****@Telus.n et

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

=============== =============== =============== =============== ==============

"T" <te****@wideope nwest.comwrote in message
news:11******** **************@ d57g2000hsg.goo glegroups.com.. .

I'm trying to determine the useage of varioius reports I have. I need
the following to occur when a report is opened, get the name of the
report, datetime, username.
I created a tblLog w/reportname, reportdate and theuser.

This is the module I have for capturing the username.

' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiGetUserName Lib "advapi32.d ll" Alias _
"GetUserNam eA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName( strUserName, lngLen)
If ( lngX 0 ) Then
fOSUserName = Left$(strUserNa me, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

How would I use this, can it be done on the onopen event of all
reports? How would I use above and insert it into the tblLog

Module for inserting reportname, reportdate, UserName into the table
Function ReportUsage(InR eportName As String)
On Error GoTo Error1
Dim db As DATABASE
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordse t("tblReportUsa ge") 'name of your table
rs.AddNew
rs("ReportName" ) = InReportName
rs("ReportDate" ) = Now 'Never call a field "Date" !!
rs.Update
Exit1:
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
Error1:
MsgBox Err.Number & "; " & Err.Description
Resume Exit1
End Function

[Chuck Grimsby]

For what it's worth, you may also want to add a (shudder) Autonumber
field to that table. This will allow the table to be sorted in the
order that the "events" are written to the table in. I say "Shudder"
as I've had a few problems with autonumber fields over the years, and
have learned not to (totally) trust them. Your mileage may (and
probably will!) vary, however it's really up to you. (Other here may
report ever seeing such problems, but a search of this newsgroup will
show a few.)

Personally, I have a Public module-level function I use for this task,
which means that I can just call it from anywhere in my DB to stuff
something into the log, and another function that exports it whenever
I want to export (and possibly clear) the table.
On Apr 14, 2:03 pm, "T" <tei...@wideope nwest.comwrote:

I'm trying to determine the useage of varioius reports I have. I need
the following to occur when a report is opened, get the name of the
report, datetime, username.
I created a tblLog w/reportname, reportdate and theuser.

This is the module I have for capturing the username.

' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiGetUserName Lib "advapi32.d ll" Alias _
"GetUserNam eA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName( strUserName, lngLen)
If ( lngX 0 ) Then
fOSUserName = Left$(strUserNa me, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

How would I use this, can it be done on the onopen event of all
reports? How would I use above and insert it into the tblLog

Module for inserting reportname, reportdate into the table
Function ReportUsage(InR eportName As String)
On Error GoTo Error1
Dim db As DATABASE
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordse t("tblReportUsa ge") 'name of your table
rs.AddNew
rs("ReportName" ) = InReportName
rs("ReportDate" ) = Now 'Never call a field "Date" !!
rs.Update
Exit1:
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
Error1:
MsgBox Err.Number & "; " & Err.Description
Resume Exit1
End Function