Restrict the amount of clicks of a button

[The Joker via AccessMonster.com]

Hello all,

I have a question which I think will have a pretty easy solution. I have a
button on a form that starts a print session that is very large. I want to
restrict it to only being used twice for the month and if they try it again
it will prompt a message box telling them they will need to contact an
administrator. How would I create a count on the button then afterwards just
show a message box? Thank you for any advice you may be able to lend.

 

[Douglas J. Steele]

Create a table (let's assume it's called ClickInfo) with two fields:
MonthYear (a text field which will hold the date information as yyyymm), and
NumberOfClicks.

In the Click event of the button, put code along the lines of:

Private Sub cmdPrint_Click()
Dim strSQL As String
Dim varClicks As Variant

varClicks = DLookup("NumberOfClicks", "ClickInfo", _
"MonthYear='" & Format(Date(), "yyyymm") & "'")
If IsNull(varClicks) Then
strSQL = "INSERT INTO ClickInfo(MonthYear, NumberOfClicks) " & _
"VALUES('" & Format(Date(), "yyyymm") & "', 1)"
CurrentDb.Execute strSQL, dbFailOnError
Else
If varClicks >= 2 Then
MsgBox "The report's already been run twice this month. " & _
"Contact an administrattor if you need to run it again."
Exit Sub
Else
strSQL = "UPDATE ClickInfo " & _
"SET NumberOfClicks = NumberOfClicks + 1 " & _
"WHERE MonthYear = '" & Format(Date(), "yyyymm") & "'"
CurrentDb.Execute strSQL, dbFailOnError
End If
End If

' put your code to run the report here

End Sub

 

[Frank]

This might (test it first) also work -

Create a table (say, "tblPrintCount") with one field (say,
"bytCount" ... a Number field of Size Byte) to store the count of prints.

Private Sub cmdPrint_Click()

Const pcMAXIMUM_PRINTS = 2

If Nz(DLookup("bytCount", "tblPrintCount"),0) >= pcMAXIMUM_PRINTS Then
MsgBox _
"The report’s already been run the maximum times ( " & _
pcMAXIMUM_PRINTS & _
" ) this month!"
Else
Docmd.RunSQL _
"UPDATE tblPrintCount" & _
" SET bytCount = bytCount + 1"

' put your code to run the report here

Endif

End Sub

Create a table (let's assume it's called ClickInfo) with two fields:
MonthYear (a text field which will hold the date information as yyyymm), and
NumberOfClicks.

In the Click event of the button, put code along the lines of:

Private Sub cmdPrint_Click()
Dim strSQL As String
Dim varClicks As Variant

varClicks = DLookup("NumberOfClicks", "ClickInfo", _
"MonthYear='" & Format(Date(), "yyyymm") & "'")
If IsNull(varClicks) Then
strSQL = "INSERT INTO ClickInfo(MonthYear, NumberOfClicks) " & _
"VALUES('" & Format(Date(), "yyyymm") & "', 1)"
CurrentDb.Execute strSQL, dbFailOnError
Else
If varClicks >= 2 Then
MsgBox "The report's already been run twice this month. " & _
"Contact an administrattor if you need to run it again."
Exit Sub
Else
strSQL = "UPDATE ClickInfo " & _
"SET NumberOfClicks = NumberOfClicks + 1 " & _
"WHERE MonthYear = '" & Format(Date(), "yyyymm") & "'"
CurrentDb.Execute strSQL, dbFailOnError
End If
End If

' put your code to run the report here

End Sub

 

[The Joker via AccessMonster.com]

Douglas,

Works PERFECT! Thank you so much!!!

Create a table (let's assume it's called ClickInfo) with two fields:
MonthYear (a text field which will hold the date information as yyyymm), and
NumberOfClicks.

In the Click event of the button, put code along the lines of:

Private Sub cmdPrint_Click()
Dim strSQL As String
Dim varClicks As Variant

varClicks = DLookup("NumberOfClicks", "ClickInfo", _
"MonthYear='" & Format(Date(), "yyyymm") & "'")
If IsNull(varClicks) Then
strSQL = "INSERT INTO ClickInfo(MonthYear, NumberOfClicks) " & _
"VALUES('" & Format(Date(), "yyyymm") & "', 1)"
CurrentDb.Execute strSQL, dbFailOnError
Else
If varClicks >= 2 Then
MsgBox "The report's already been run twice this month. " & _
"Contact an administrattor if you need to run it again."
Exit Sub
Else
strSQL = "UPDATE ClickInfo " & _
"SET NumberOfClicks = NumberOfClicks + 1 " & _
"WHERE MonthYear = '" & Format(Date(), "yyyymm") & "'"
CurrentDb.Execute strSQL, dbFailOnError
End If
End If

' put your code to run the report here

End Sub

Hello all,

[quoted text clipped - 8 lines]

just
show a message box? Thank you for any advice you may be able to lend.

 

[Frank]

My earlier suggestion would NOT work.

This might (again, test it) -

To the table I suggested, add a second field ("dtmDate") to store the date
of the most recent print.

Private Sub cmdPrint_Click()

Const pcMAXIMUM_PRINTS = 2

If Nz(DLookup("bytCount", "tblPrintCount"), 0) >= pcMAXIMUM_PRINTS And _
Month(DLookup("dtmDate", "tblPrintCount")) = Month(Now()) Then
MsgBox _
"The report’s already been run the maximum ( " & _
pcMAXIMUM_PRINTS & _
" ) times this month!"
Else
DoCmd.SetWarnings False
DoCmd.RunSQL _
"UPDATE tblPrintCount" & _
" SET dtmDate = Now(), bytCount = bytCount + 1"
DoCmd.SetWarnings True

' put your code to run the report here
End If

End Sub

Douglas,

Works PERFECT! Thank you so much!!!

Create a table (let's assume it's called ClickInfo) with two fields:
MonthYear (a text field which will hold the date information as yyyymm), and
NumberOfClicks.

In the Click event of the button, put code along the lines of:

Private Sub cmdPrint_Click()
Dim strSQL As String
Dim varClicks As Variant

varClicks = DLookup("NumberOfClicks", "ClickInfo", _
"MonthYear='" & Format(Date(), "yyyymm") & "'")
If IsNull(varClicks) Then
strSQL = "INSERT INTO ClickInfo(MonthYear, NumberOfClicks) " & _
"VALUES('" & Format(Date(), "yyyymm") & "', 1)"
CurrentDb.Execute strSQL, dbFailOnError
Else
If varClicks >= 2 Then
MsgBox "The report's already been run twice this month. " & _
"Contact an administrattor if you need to run it again."
Exit Sub
Else
strSQL = "UPDATE ClickInfo " & _
"SET NumberOfClicks = NumberOfClicks + 1 " & _
"WHERE MonthYear = '" & Format(Date(), "yyyymm") & "'"
CurrentDb.Execute strSQL, dbFailOnError
End If
End If

' put your code to run the report here

End Sub

Hello all,

[quoted text clipped - 8 lines]

just
show a message box? Thank you for any advice you may be able to lend.