I have a Main Menu form on which I have a timer running. The timer compares the current time against times stored in tables to activate alerts for fixed as well as user constructed reminders.
I have my declarations setup in the module header.
Code:
Option Compare Database
Option Explicit
Private rs As Recordset 'Recordset for Expiration reminders
Private rst As Recordset 'Recordset for User configured reminders
I set their values in the Form_Load Event
Code:
Private Sub Form_Load()
Set rs = CurrentDb.OpenRecordset("tblExpirationReminders", dbOpenDynaset, dbSeeChanges)
Set rst = CurrentDb.OpenRecordset("tblReminders", dbOpenDynaset, dbSeeChanges)
End Sub
I run code in the Forms Timer event to 1. Display a clock on the form(as a visual indicator that my timer is still running), and 2. to compare the stored table Dates, Times, and Frequencies and display alerts when there is a match.
The code seems to work OK...Until it doesn't and then I get the error message "Object variable with Block variable not set". This happens at the point in the code where I attempt a MoveLast, MoveFirst to refresh the recordset. Apparently recordset variable no longer exists in memory.
Code:
'------------------------------------------------------------
' NAME: Form_Timer()
' PORPOSE: Compares current time against Times stored in tables to activate
' Expired alerts and user constructed reminders,
' REQUIRES: A table for system expiration reminders and a table for user
' configured reminders.
' Also requires module level recordsets that stay open so the can be
' continuously evaluated
' CREATED BY: Andrew Dulavitz (adulavitz@hotmail.com)
' DATE: september 2023
'-------------------------------------------------------------
Private Sub Form_Timer()
On Error GoTo Form_Timer_Err
Dim CurrentTime As Date
Dim CurrentDate As Date
Dim intDOW As Integer
Dim strDOW As String
Dim intType As Integer
Dim strMsg As String
' Set the time and display in label on form as a clock
CurrentTime = FormatDateTime(Now(), vbLongTime)
' the Timer label is a staus indicater.If this label is updating Then the Timer is working
Me.lblTime.Caption = CurrentTime
' This uses the Module level recordset "tblExpirationReminders"
' Refresh the recordsets by moving to First and Last record
If Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
End If
If Not rst.EOF Then
rst.MoveLast
rst.MoveFirst
End If
'for fixed Expiration reminders
Do Until rs.EOF
If rs!ReminderTime = CurrentTime And rs!IsActive Then
Eval rs!FunctionName ' Run the function and display the results if anything is returned.
End If
rs.MoveNext
Loop
'Move to 1st record to be reay for next timer loop
If rs.EOF And rs.RecordCount > 0 Then rs.MoveFirst
'For user defined reminders using recordset for "tblReminders"
Do Until rst.EOF
intType = rst!Recurrance
CurrentDate = FormatDateTime(Now(), vbShortDate)
If rst!RemTime = CurrentTime And rst!IsActive Then
' Evaluate the Reccurance Option Group
Select Case intType
Case 1 ' One Time Reminder
If rst!RemDate = CurrentDate Then
strMsg = rst!ReminderText
MsgBox strMsg, vbOKOnly + vbInformation, "Reminder"
If rst!Beep = True Then DoCmd.Beep
End If
Case 2 ' Daily Reminder
strMsg = rst!ReminderText
MsgBox strMsg, vbOKOnly + vbInformation, "Reminder"
If rst!Beep = True Then DoCmd.Beep
Case 3 ' Weekly Reminder
strDOW = rst!DOW
' Evaluate numeric day of week
intDOW = Nz(Switch(strDOW = "Sun", 1, strDOW = "Mon", 2, strDOW = "Tues", 3, _
strDOW = "Wed", 4, strDOW = "Thurs", 5, strDOW = "Fri", 6, strDOW = "Sat", 7))
If Weekday(CurrentDate) = intDOW Then
strMsg = rst!ReminderText
MsgBox strMsg, vbOKOnly + vbInformation, "Reminder"
If rst!Beep = True Then DoCmd.Beep
End If
Case 4 ' Monthly Reminder
If Day(CurrentDate) = rst!DOM Then
strMsg = rst!ReminderText
MsgBox strMsg, vbOKOnly + vbInformation, "Reminder"
If rst!Beep = True Then DoCmd.Beep
End If
End Select
End If
rst.MoveNext
Loop
'Move to 1st record to be reday for next timer loop
If rst.EOF And rst.RecordCount > 0 Then rst.MoveFirst
Form_Timer_Exit:
Exit Sub
Form_Timer_Err:
MsgBox Err.Number & " " & Err.description
Resume Form_Timer_Exit
End Sub
Any Idea about some other way I can construct these variables so they don't fail? Would I be better served using a Tempvar instead of standard module level variables for the recordsets?