code to ask if user wishes to print a report 1

[THWatson]

A Kiwanis Club database...

When a database opens, the first form to come up is frmSplash.

Behind that form lies the following code:

Private Sub Form_Timer()
   On Error GoTo Form_Timer_Error

DoCmd.Hourglass True
Me.TimerInterval = Me.TimerInterval - 50
If Me.TimerInterval = 0 Then
DoCmd.Hourglass False
DoCmd.Close
[b]If Day(Date) <= 7 Then[/b]
Select Case MsgBox("              REMINDER..." _
                   & vbCrLf & "A Celebrations Report needs to be printed once a month." _
                   & vbCrLf & " " _
                   & vbCrLf & "             Do you want to print that report now?" _
                   & vbCrLf & "" _
                   & vbCrLf & "(this reminder appears up to the 7th of each month)" _
                   , vbYesNo Or vbExclamation Or vbDefaultButton1, "Celebrations Report check")
    Case vbYes
        DoCmd.OpenForm "frmMonthSelector"
        Exit Sub
    Case vbNo
        DoCmd.OpenForm "frmMainMenu"
End Select

End If
DoCmd.OpenForm "frmMainMenu"
End If

   On Error GoTo 0
   Exit Sub

Form_Timer_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Timer of VBA Document Form_frmSplash"

End Sub

Note the line in bold print: If Day(Date) <= 7 Then
The message occurs if the date upon which the database is opened is in the first 7 days of the month.

The user wants to change this so that the message occurs anytime between the second last Thursday and the last Thursday of the month.
Can you advise appropriate code to select that date range?

Thanks.

Tom

 

[Andrzejek]

Try this:

Replace:

If Day(Date) <= 7 Then

with this:

If IsReportWeek Then

and add this Function to your code:

Private Function IsReportWeek() As Boolean
Dim datLastThursday As Date
[green]
'Find last day of the Month[/green]
datLastThursday = DateSerial(Year(Date), _
     Month(Date) + 1, 0)
[green]
'Count down and find last Thursday of the Month[/green]
Do Until Weekday(datLastThursday) = vbThursday
    datLastThursday = datLastThursday - 1
Loop
[green]
'MsgBox "Last Thursday of the Month is " & datLastThursday
[/green]
If Date >= (datLastThursday - 7) And Date <= datLastThursday Then
    IsReportWeek = True
End If

End Function

Warning - code not tested for the month of December.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[THWatson]

We'll have to see what happens in December, but it works currently. THANKS!

Do I have to add anything after the "If IsReportWeek Then" line?
such as lines
Call IsReportWeek()
Else End If

Or are those lines redundant?

Tom

 

[Andrzejek]

I am glad it works for you

>Do I have to add anything after the "If IsReportWeek Then" line?
No.
Functions return a value (Subs do not return any values) so you need to either assign this value to something, or evaluate it/use it.
Since [tt]IsReportWeek[/tt] is defined as [tt]Boolean[/tt], it returns either True or False, and that is what your [tt]If[/tt] statement evaluates.

You can use [tt]Call[/tt] when Subs are called.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[THWatson]

Thank you, Andy!