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:
Code:
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.