Message Box Popup on certain days help !

[DarkOne72]

Hi All,

Maybe someone can assist me in trying to get this code to work? I have half of it working but not the month end part.
What I need:

When launching the database or if it is already loaded and you show all records and it is a Friday then show the message in a popup box saying "Please Run Your Weekly Report at EOD!" which works.
However, if it is the last Friday at the end of the month then I want another message box to appear saying "Please Run Your Monthly Reports at EOD!", this part doesn't work.
​

Please see the code I have below and maybe someone can assist me in getting it correct.

The Function/Module1:

Function eom(ByVal Input_Date As Date) As Date
    
eom = DateAdd("d", -1, DateAdd("m", 1, DateSerial(Year(Input_Date), Month(Input_Date), 1)))

End Function

The code behind the On-Timer event
with interval of 0

Private Sub Form_Timer()

Dim p_dtDate As Date

MonthEnd = eom(Now())

'Weekday 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday
If Weekday(Now()) = 6 Then
MsgBox ("Please Run Your Weekly Report at EOD!")


If Weekday(Now()) = MonthEnd Then
MsgBox ("Please Run Your Monthly Reports at EOD!")

End If
End If
End Sub

Can someone please help?
Thanks in advance.

 

[DarkOne72]

HI Andy, sorry for the delay but was dealing with a TS/Hurricane from Debbie.
Anyways, here is the code on form load and what I have in the module

Private Sub Form_Load()
DoCmd.ShowAllRecords

Dim strMsg As String

If Weekday(Date) = vbFriday Then
strMsg = "Please Run Your Weekly Report at EOD!"
End If

If Day(Date) > 25 Then
'No reason to check before the 26th
If Date = LastWorkDay Then
If Len(strMsg) Then
strMsg = strMsg & vbNewLine & vbNewLine & _
"and also" & vbNewLine
End If

strMsg = strMsg & "Please Run Your Monthly Reports at EOD!"
End If
End If

If Len(strMsg) Then MsgBox strMsg

End Sub

and the module:

Private Function LastWorkDay() As Date
Dim datDay As Date
Dim blnFoundIt As Boolean

'Get the last day of curent month
datDay = DateSerial(Year(Date), Month(Date) + 1, 0)

Do
Select Case Weekday(datDay)
Case vbSaturday, vbSunday
'Subtract a day if weekend
datDay = DateAdd("d", -1, datDay)
Case Else
blnFoundIt = True 'BINGO
End Select
Loop Until blnFoundIt

LastWorkDay = datDay

End Function

Thank you for your help !
Rick

 

[Andrzejek]

This is the Test code (based on what you have with combo's part, thanks combo) that you can run and 'pretend' to be [red]any day of this month[/red]. Works OK for the last workday of this month, which happens to be Friday:

Option Explicit

Sub Test()
Dim strMsg As String
Dim datMyD As Date

datMyD = CDate([red]"30/08/2024"[/red]) [green]'<- enter date you want to test[/green]

If Weekday(datMyD) = vbFriday Then
    strMsg = "Please Run Your Weekly Report at EOD!"
End If

If Day(datMyD) > 25 Then[green]
    'No reason to check before the 26th[/green]
    If datMyD = LastWorkDay Then
        If Len(strMsg) Then
            strMsg = strMsg & vbNewLine & vbNewLine & _
            "and also" & vbNewLine
        End If
        strMsg = strMsg & "Please Run Your Monthly Reports at EOD!"
    End If
End If

If Len(strMsg) Then MsgBox strMsg

End Sub

Private Function LastWorkDay() As Date
Dim datEOM As Date[green]
'Get the last day of curent month[/green]
datEOM = DateSerial(Year(Date), Month(Date) + 1, 0)

Do
    Select Case Weekday(datEOM)
        Case vbSaturday, vbSunday[green]
            'Subtract a day if weekend[/green]
            datEOM = datEOM - 1
        Case Else
            Exit Do   [green]'BINGO[/green]
    End Select
Loop
    
LastWorkDay = datEOM

End Function

Which shows that your code should run OK, so I don't know why it did not run on the last day of last month.

BTW - I hope your code is better aligned :-(



---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[DarkOne72]

HI Andy,

Haha, yeah the code is aligned properly, I had to copy it in a hurry and send it after pasting it in notepad; it lost alignment when I did it.

I will check everything and see if it runs.

Thank you for looking at it.

Rick

 

[Norman Sherlock]

You could just say "run your weekly report, and your monthly report if applicable"

No extra coding required.