Counting how many times a button is pressed

[Guest]

Hello All,
I have a form that has a command button on it that prints a letter for the
record that it is on.
What the user wants is a report that shows how many times the button was
clicked within a specified date range.

I am thinking that I need to do an event on the ON Click for the button.
But how do I do that? Do I need to have a seperate table to store the date
and a number then tell a query to count it for the report?

Any help would be great to get me on the right track.

 

[Guest]

You would want to use the Click event of the button, but when you say within
a date range, do you mean the day the button was clicked or a date range in
the current record?

 

[Guest]

I would recommend a table with one column that stores the date/time the click
event occurred and one autonumber column to be used as the primary key. Then
create an Append query that appends a row with the current date (using
Date()) into the date/time column. If you want more granularity, you can use
Now(), which will also give you the current time.

Then in the button's OnClick event, call the query using DoCmd.OpenQuery. It
should look something like this:

DoCmd.SetWarnings False 'turn off the annoying message.
DoCmd.OpenQuery "qryAppendTimeStamp" ' or whatever your query is named.
DoCmd.SetWarnings True ' turn the annoying messages back on.

You can now write a report that counts records in this new table using the
Date/Time column for criteria.

Barry

 

[John Vinson]

I am thinking that I need to do an event on the ON Click for the button.
But how do I do that? Do I need to have a seperate table to store the date
and a number then tell a query to count it for the report?

Exactly. If you want to record each date and time the button was
clicked, you need a table to do so. Your table doesn't need to store
"a number" - just the date.

You could have a table named CountClicks:

CountClicks
ReportName <text>
WhenPrinted <date/time>

with the two fields as a joint Primary Key. This will allow any report
to have this feature independently of other reports. In the print
button's Click event you'ld add code like:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim strSQL As String
Dim strDoc As String

On Error GoTo Proc_Error

strDoc = "yourreportname"
Set db = CurrentDb
strSQL = "INSERT INTO CountClicks(ReportName, WhenPrinted)" _
& " VALUES (""" & strDoc & """, #" & Now & "#);"
' create an unnamed, unstored append query
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute, dbFailOnError
Set qd = Nothing

<go on to launch your report>

Proc_Exit:
Exit Sub
Proc_Error:
<put appropriate error handling code here>
Resume Proc_Exit
End Sub

John W. Vinson[MVP]