Tracking Use of Reports

[naigy via AccessMonster.com]

Hi I have a switchboard with buttons on it for various forms for inputting
data and also a form which opens up a reportslist form with some variables
which can be used for the reporting (at present just a date range). The
reports are listed in a listbox using data from one of the system tables.

What I am wanting to do is each time someone accesses a particular report to
output to a table (or external file) the networkid, date, time, reportname
and if possible the values in the reportslist form (datefrom and dateto). Is
this possible and if so can someone please point me in the right direction.

If possible I would also like to create a log of data changed in forms (not
new records) also with a similar format.

Many Thanks.

 

[Allen Browne]

So you have a form where the user selects the report they want, enters any
criteria, and clicks a button to open the report, and you want to log this.

Presumably the button's Click event procedure builds a string to use in the
WhereCondition of OpenReport. This event procedure could also execute an
append query statement to insert a log record.

The logging table would have fields such as:
ID AutoNumber
ReportName Text what report was opened.
ReportFilter Memo what filter
ReportDateTime Date/Time when it was opened.
ReportUser Text who opened it

For the code to get the report user, see:
http://www.mvps.org/access/api/api0008.htm

The logging code will look like this:
Dim strSql As String
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError

To get the SQL statement you need to generate in your code from these
values, mock up a query (no source table), change it to an Append query
(Append on query menu), type any old values into the Field row, and match
them to the fields to append to. Then switch to SQL View (View menu in query
design), and there's your example.

For your 2nd question regarding logging edits (deletions if you wish), see:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

 

[naigy via AccessMonster.com]

Thanks Allen. I have followed your example from what I can see but I have a
few problems. Firstly here is my OpenReport Click code.


Private Sub Openrpt_Click()
DoCmd.OpenReport Me.RptList.Value, acViewPreview

Dim strSql As String
strSql = "INSERT INTO ReportUsage ( UsageID, ReportName, ReportFilter1,
ReportFilter2, ReportDateTime, ReportUser ) SELECT [UsageID] AS Expr1,
[ReportName] AS Expr2, [ReportFilter1] AS Expr3, [ReportFilter2] AS Expr4,
[ReportDateTime] AS Expr5, [ReportUser] AS Expr6;"
DBEngine(0)(0).Execute strSql, dbFailOnError

End Sub

Now when I run this I get a Runtime 3061 error and it says "Too few
parameters. Expected 6". I was wondering where in the append / sql code I
input what I want the values to be. Ie Me.RptList.Value for ReportName &
ReportFilter1 as Me.DateFrom.Value.

Thanks for your assistance to get to this stage.

So you have a form where the user selects the report they want, enters any
criteria, and clicks a button to open the report, and you want to log this.

Presumably the button's Click event procedure builds a string to use in the
WhereCondition of OpenReport. This event procedure could also execute an
append query statement to insert a log record.

The logging table would have fields such as:
ID AutoNumber
ReportName Text what report was opened.
ReportFilter Memo what filter
ReportDateTime Date/Time when it was opened.
ReportUser Text who opened it

For the code to get the report user, see:
http://www.mvps.org/access/api/api0008.htm

The logging code will look like this:
Dim strSql As String
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError

To get the SQL statement you need to generate in your code from these
values, mock up a query (no source table), change it to an Append query
(Append on query menu), type any old values into the Field row, and match
them to the fields to append to. Then switch to SQL View (View menu in query
design), and there's your example.

For your 2nd question regarding logging edits (deletions if you wish), see:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

Hi I have a switchboard with buttons on it for various forms for inputting
data and also a form which opens up a reportslist form with some variables

[quoted text clipped - 12 lines]

(not
new records) also with a similar format.

 

[naigy via AccessMonster.com]

Possibly an easier question. I have got that query to work properly now but
only when the Docmd.OpenQuery command is used. Is there a way to surpress
confirmation prompting without changing the global setting in access or the
database or is this a bad idea. Many Thanks.

Thanks Allen. I have followed your example from what I can see but I have a
few problems. Firstly here is my OpenReport Click code.

Private Sub Openrpt_Click()
DoCmd.OpenReport Me.RptList.Value, acViewPreview

Dim strSql As String
strSql = "INSERT INTO ReportUsage ( UsageID, ReportName, ReportFilter1,
ReportFilter2, ReportDateTime, ReportUser ) SELECT [UsageID] AS Expr1,
[ReportName] AS Expr2, [ReportFilter1] AS Expr3, [ReportFilter2] AS Expr4,
[ReportDateTime] AS Expr5, [ReportUser] AS Expr6;"
DBEngine(0)(0).Execute strSql, dbFailOnError

End Sub

Now when I run this I get a Runtime 3061 error and it says "Too few
parameters. Expected 6". I was wondering where in the append / sql code I
input what I want the values to be. Ie Me.RptList.Value for ReportName &
ReportFilter1 as Me.DateFrom.Value.

Thanks for your assistance to get to this stage.

So you have a form where the user selects the report they want, enters any
criteria, and clicks a button to open the report, and you want to log this.

[quoted text clipped - 34 lines]

 

[Allen Browne]

To use Execute instead of RunSQL or OpenQuery, you need to concatenate the
values into the string, e.g.:

strSql = "INSERT INTO ReportUsage ( UsageID, ReportName, ReportFilter1,
ReportFilter2, ReportDateTime, ReportUser ) SELECT """ & Me.[UsageID] & """
AS Expr1, """ & Me.[ReportName] & """ AS Expr2, ...

More info:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

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

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

Possibly an easier question. I have got that query to work properly now
but
only when the Docmd.OpenQuery command is used. Is there a way to surpress
confirmation prompting without changing the global setting in access or
the
database or is this a bad idea. Many Thanks.

Thanks Allen. I have followed your example from what I can see but I have
a
few problems. Firstly here is my OpenReport Click code.

Private Sub Openrpt_Click()
DoCmd.OpenReport Me.RptList.Value, acViewPreview

Dim strSql As String
strSql = "INSERT INTO ReportUsage ( UsageID, ReportName, ReportFilter1,
ReportFilter2, ReportDateTime, ReportUser ) SELECT [UsageID] AS Expr1,
[ReportName] AS Expr2, [ReportFilter1] AS Expr3, [ReportFilter2] AS Expr4,
[ReportDateTime] AS Expr5, [ReportUser] AS Expr6;"
DBEngine(0)(0).Execute strSql, dbFailOnError

End Sub

Now when I run this I get a Runtime 3061 error and it says "Too few
parameters. Expected 6". I was wondering where in the append / sql code I
input what I want the values to be. Ie Me.RptList.Value for ReportName &
ReportFilter1 as Me.DateFrom.Value.

Thanks for your assistance to get to this stage.

So you have a form where the user selects the report they want, enters
any
criteria, and clicks a button to open the report, and you want to log
this.

[quoted text clipped - 34 lines]

(not
new records) also with a similar format.

 

[naigy via AccessMonster.com]

Thanks for your help. All is now solved using sql. I could not get the report
user (fOSUserName) to function kept getting an error that it expected a
procedure and not a module so used enviroment user name code


Many Thanks.

 

[Allen Browne]

Okay.

(You have to save the module with a different name, i.e. don't use the same
name as the function.)