INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

code to ask if user wishes to print a report

code to ask if user wishes to print a report

(OP)
A Kiwanis Club database...

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

Behind that form lies the following code:

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
If Day(Date) <= 7 Then
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

RE: code to ask if user wishes to print a report

Try this:

Replace:

CODE

If Day(Date) <= 7 Then 

with this:

CODE

If IsReportWeek Then 

and add this Function to your code:

CODE

Private Function IsReportWeek() As Boolean
Dim datLastThursday As Date

'Find last day of the Month
datLastThursday = DateSerial(Year(Date), _
     Month(Date) + 1, 0)

'Count down and find last Thursday of the Month
Do Until Weekday(datLastThursday) = vbThursday
    datLastThursday = datLastThursday - 1
Loop

'MsgBox "Last Thursday of the Month is " & datLastThursday

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.

RE: code to ask if user wishes to print a report

(OP)
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

RE: code to ask if user wishes to print a report

I am glad it works for you smile

>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 IsReportWeek is defined as Boolean, it returns either True or False, and that is what your If statement evaluates.

You can use Call when Subs are called.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: code to ask if user wishes to print a report

(OP)
Thank you, Andy!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close