Upcoming Holidays Alert

[hicham6w]

Hello everyone,
I have been trying to get an upcoming holidays alert on a form open. The list of holidays is in a table called "tblHolidays" with the following fields
"HolidayMonth" as byte, "HolidayDayOfMonth" as byte. I used the following code, but it is stuck in the first record and the message box is blank:

Public Sub Holiday_Alert()

Dim myDb As DAO.Database
Dim rst As DAO.Recordset

Dim strSQL As String
Dim strMsg As String

Dim M As Integer 'month field
Dim D As Integer 'day field
Dim JD As Date 'calculated holiday date

Dim n As Integer

Set myDb = CurrentDb()
strSQL = "SELECT * from tblHolidays ORDER BY HolidayMonth, HolidayDayOfMonth"
Set rst = myDb.OpenRecordset(strSQL, dbOpenSnapshot)

'initial value of the msgbox
strMsg = ""

'assigning variables
M = rst.Fields("HolidayMonth").Value
D = rst.Fields("HolidayDayOfMonth").Value
JD = DateSerial(Year(Date), M, D)

'populate recordset
rst.MoveLast
rst.MoveFirst

'loop to find upcoming holidays
For n = 0 To rst.RecordCount
        rst.FindFirst (JD - Date) > 0 And (JD - Date) < 15
                    If Not rst.NoMatch Then
                        strMsg = strMsg & rst.Fields("HolidayName") & " est prévue dans " & (JD - Date) & " jours" & vbCrLf
                    End If
          'report n on status bar every 50
          If n Mod 50 = 0 Then
          DoCmd.Echo True, n
          End If
          'move next
          rst.MoveNext
          n = n + 1
Next n

'show results
MsgBox strMsg, vbInformation, "Journées Mondiales"

rst.Close
Set rst = Nothing
Set myDb = Nothing

End Sub

Any help would be greatly appreciated.

[orange]

Why did you decide not to have Holiday dimmed as a Date datatype?

[hicham6w]

Why did you decide not to have Holiday dimmed as a Date datatype?

Thanks for you reply, it was because I wanted it to hapen every year not just this year.

[hicham6w]

Hello again I've finally got the code to work, Here it is :

Public Sub Holidays_Alert()

Dim myDb As DAO.Database
Dim rst As DAO.Recordset

Dim strSQL As String
Dim strMsg As String
Dim strJN As String

Dim M     As Integer
Dim D     As Integer
Dim JD    As Date
Dim intRemaining As Integer

Dim n As Integer 'counter
Dim x As Integer

Set myDb = CurrentDb()
strSQL = "SELECT * from tblHolidays ORDER BY HolidayMonth, HolidayDayOfMonth"
Set rst = myDb.OpenRecordset(strSQL)

'initial value of the msgbox
strMsg = ""

'populate rst : unecessary ?
'rst.MoveLast
'rst.MoveFirst

'loop to find upcoming holidays
For n = 0 To rst.RecordCount - 1

'assign vars (must be inside the loop)
M = rst.Fields("HolidayMonth").Value           'mount field
D = rst.Fields("HolidayDayOfMonth").Value     'day field
strJN = rst.Fields("HolidayName").Value      'Holiday name
JD = DateSerial(Year(Date), M, D)              'Holyday date this year
intRemaining = JD - Date                             'remainig days

'Debug.Print strJN; intRemaining
        rst.FindNext intRemaining >= 0 And intRemaining <= 15                '(in the future 15 days)
                    If Not rst.NoMatch Then
                              strMsg = strMsg & strJN & vbTab & " dans " & intRemaining & " jours" & vbNewLine & vbCrLf
                              x = x + 1
                    End If
'                    'report n on status bar
'                    If n Mod 50 = 0 Then
'                    DoCmd.Echo True, n
'                    End If
rst.MoveNext
n = n + 1
Next n
                     
'show results if any
If strMsg <> "" Then
MsgBox strMsg, vbInformation, x & " Journées Mondiales prévues dans deux semaines ..."
End If

rst.Close
Set rst = Nothing
Set myDb = Nothing

End Sub