Current User

[Guest]

Hi,

Is it possible to have an entry made to a table when a user accesses a report?
If so, how?

I want to check on the frequency that a User accesses the reports that I've
made up to see if they're being used, and if not, delete them or create new
ones or improve the existing ones.

TIA,

KevinT

 

[Allen Browne]

You could use the Open event of the report to execute an append query
statement to add a record to your report logging table.

1. Create a table with these fields:
LogReportID AutoNumber primary key
ReportName Text
UserName Text
OpenDateTime Date/Time
Save the table with the name tblLogReport.

2. In the Open event procedure of your report, add these 3 lines:
Dim strSql As String
strSql = "INSERT INTO tblLogReport ( ReportName, UserName,
OpenDateTime ) SELECT """ & Me.Name & """ AS ReportName, """ &
Environ("username") & """ AS UserName, Now() AS OpenDateTime;"
dbEngine(0)(0).Execute strSql, dbFailOnError

If you are using Access security, replace:
Environ("username")
with:
CurrentUser()

 

[Guest]

Thanks Allen,

It works a treat.

You could use the Open event of the report to execute an append query
statement to add a record to your report logging table.

1. Create a table with these fields:
LogReportID AutoNumber primary key
ReportName Text
UserName Text
OpenDateTime Date/Time
Save the table with the name tblLogReport.

2. In the Open event procedure of your report, add these 3 lines:
Dim strSql As String
strSql = "INSERT INTO tblLogReport ( ReportName, UserName,
OpenDateTime ) SELECT """ & Me.Name & """ AS ReportName, """ &
Environ("username") & """ AS UserName, Now() AS OpenDateTime;"
dbEngine(0)(0).Execute strSql, dbFailOnError

If you are using Access security, replace:
Environ("username")
with:
CurrentUser()

 

[Douglas J. Steele]

Just be aware that it's trivial for users to reset the Username environment
variable.

You might want to use the code from
http://www.mvps.org/access/api/api0008.htm at "The Access Web" rather than
the Environ function.

 

[Guest]

Thanks Doug.
I used the CurrentUser() function as I have Access Security setup on the
database - don't know if that makes a difference to what you're saying about
the environ variable.
Anyway, I'm the brightest star in our little group of misfits, which isn't
saying much, and I have no idea how to change the username etc.

Just out of curiosity though, I inserted the module into a "new" module in
the "dummy" practice database that I use to experiment with new stuff before
I insert it into the bona fide database. How do I retrieve the info the
module provides.
Excuse me if I seem dumb, but I haven't worked with modules before.
Would love to get into VB though to be able to write this stuff etc though.

KevinT

 

[Allen Browne]

Okay, Kevin, if you are using Access security, CurrentUser() will give you
what you want.

In a database that is not secured, Doug's point is valid, and I do use the
API call he pointed you to in all my unsecured databases.

To test it, choose the Modules tab of the Database window, and click New.
Paste the code in the new code window.
Choose Compile on the Debug window to ensure VBA understands it.
Press Ctrl+G to open the Immediate window.
Enter:
? fOSUserName()

When you press enter, the response should be your Windows user name.