Module level Recordset variable losing it's value

[Salty Mariner]

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.

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

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.

'------------------------------------------------------------
' 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?

[Micron]

I would requery the data by running the sql/query that builds the rs - assuming you need to. You're not using the .Add method to add records to the rs so I don't see how you can expect new data to be in the rs

Refresh the recordsets by moving to First and Last record

, especially when it's static. Once you create it and don't edit or add records to it, there's no changes to it.

[June7]

I know global variables can lose value when an unhandled runtime error triggers. TempVars do not.

Since you have error handling I would not expect variable to lose value.

Might disable error handler for debugging.

[Salty Mariner]

I know global variables can lose value when an unhandled runtime error triggers. TempVars do not.

Since you have error handling I would not expect variable to lose value.

Might disable error handler for debugging.

Good point about the error handling. I just tried inserting into my Error Handler

If Err.Number = 91 Then Resume Form_Timer_Exit

We'll see what that does.

I'm wondering if when the error fires If I should try issuing the SET statements against the recordsets again and then exit out thinking that this will "fix" whatever caused the variables to lose their values. The real fix however (I think) would probably be something different where the variables don't lose their value.... Like trying this in a Tempvar as previously mentioned.

[CJ_London]

Not saying I agree with your method but to answer your question I tend to use a self healing process.

To do this I would declare your recordsets as a) properly defined and b) within your timer event using 'self healing'

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`

Static rs As DAO.Recordset     'Recordset for Expiration reminders
Static rst As DAO.Recordset    'Recordset for User configured reminders

if rs is nothing then Set rs = CurrentDb.OpenRecordset("tblExpirationReminders", dbOpenDynaset, dbSeeChanges)
if rst is nothing then Set rst = CurrentDb.OpenRecordset("tblReminders", dbOpenDynaset, dbSeeChanges)

not sure why you feel the need to 'refresh' the data but I don't believe movelast/movefirst will do that - but I would need to check

not sure why you are formatting values to text to assign to a date variable but this

CurrentTime = FormatDateTime(Now(), vbLongTime)

can simply be replaced with

CurrentTime = Time

ditto

CurrentDate = FormatDateTime(Now(), vbShortDate)

can be replaced with

CurrentDate=Date


why bother doing this

'Move to 1st record to be reay for next timer loop
    If rs.EOF And rs.RecordCount > 0 Then rs.MoveFirst

when next time the sub is called you do this

' Refresh the recordsets by moving to First and Last record
If Not rs.EOF Then
    rs.MoveLast
    rs.MoveFirst
    End If

ditto for your rst recordset

You are relying heavily on getting a match on time.

If rs!ReminderTime = CurrentTime
If rst!RemTime = CurrentTime

depends on how long your routine takes to run, but I can envisage it could take more that a second in which case you may not get that equivalence.

[Salty Mariner]

CJ,

Thank you for taking a look at my code and offering up a critique of what i may have been doing wrong. This was my first attempt at using timer events against recordsets to try and trigger actions. I sort of knew what I wanted to accomplish so I set out to research various methods of accomplishing this. I found several examples that did things close to what I wanted to achieve. So, I started assembling things, copied some snippets of other peoples code, and eventually found out what worked, what didn't and ended up with something that mostly seemed to work. I agree that it could use some streamlining.

I used the Module level variables to have something that would retain the value. I like your idea of using static recordset variables, checking them re-setting them if needed when the timer fires If they have have lost their values. That was an avenue I had not considered.

As for my formatting dates and times, I was doing this to match the data types in the tables I was checking. I will give your suggestion a try since there is no real need to do type conversion. This is likely my inexperience showing here as I was trying to match same with same so it made sense for me when I was putting this all together.

Finally the events are firing within a second. I have my timer firing once every second. I have it changing a label caption as a clock. It serves as my visual cue that the timer code is still running. Not sure if that is good or bad It seemed to make sense for me.

Again, thanks for taking the time to look at this.

[CJ_London]

Static variables can only be declared within a sub or function, they cannot be global.

date and time are stored as decimal numbers. The value before the do is the number of days since 31/12/1899. The number of seconds is after the dp and is the number of seconds divided by 86400 ( the number of seconds in a day)

So what you see in a table is just a formatted view of that number

[CJ_London]

if I can expand on one of my critiques

Some of your operations will take time - certainly more than a second. So your loop here may not pick up a reminder time

 '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

for example if you have two reminder times of say 12:00:00 and 12:00:01, the second one won't be picked up because by the time the first one has been actioned, the time has moved on to 12:00:02

I don't know what the function you call does, but if it includes a message box as you do further down the code, the user is unlikely to be able to click 'OK' within a second - if they've gone to lunch or in a meeting, might be an hour or more before they return to their computer in which case all notifications timed before they click the message box will not be generated.

[Salty Mariner]

if I can expand on one of my critiques

Some of your operations will take time - certainly more than a second. So your loop here may not pick up a reminder time

 '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

for example if you have two reminder times of say 12:00:00 and 12:00:01, the second one won't be picked up because by the time the first one has been actioned, the time has moved on to 12:00:02

I don't know what the function you call does, but if it includes a message box as you do further down the code, the user is unlikely to be able to click 'OK' within a second - if they've gone to lunch or in a meeting, might be an hour or more before they return to their computer in which case all notifications timed before they click the message box will not be generated.

Thank you for this CJ.

I have made most/all of the tweaks recommended above and so far so good. I'll be leaving this running on my desktop for a few days while interacting with it to watch out for any more issues. Since this timer runs on my applications menu form it's probably some of the most critical code to get right and run error free. Basically this application serves many functions revolving around documents, crew, and equipment to be used on a fleet of Oilfield Service vessels. The timer event checks for crew document expirations, vessel document expirations, and Equipment Expirations These are basically fixed reminders. There is also the option to have user configurable reminders for example sending the daily report at a specified time.

You do bring up a good point about the functions displaying a message box and breaking the code execution. I do have a message box display which gives the user a choice to display a form with the records (if any are found). I might think about doing away with the message box and display the form as a modeless popup instead allowing the code to continue running. I'm not really anticipating lots of reminders but you do bring up a valid point that I had not considered.

[Welshgasman]

Surely all these expiration occur on a certain date?, not 03:00 on a certain date?
So they could be checked when the DB is started, or daily?

[Salty Mariner]

Surely all these expiration occur on a certain date?, not 03:00 on a certain date?
So they could be checked when the DB is started, or daily?

Our expirations occur through out the year at random dates. Time is not not really a consideration for them.

Expirations for documents and equipment are checked daily and turned on by default. The user can however change the notification times to their liking, or choose to disable them altogether. Maybe they want to see them at 08:00 or Noon I let them decide when to view them. My company has had a problem with documents and equipment expiring and slipping through the cracks only to be found at the most embarrassing of times (like during an audit).

I also allow users to create their own custom reminders. Sure, they could do this in Outlook but I'd the majority don't know how. Mostly for simple things like a reminder to send the midnight report (which is supposed to go out just before midnight). They end up getting scolded by the office when they are late or forget altogether. Since this will be the instrument through which they compose those reports an optional reminder to do so within the application made sense. Not knowing ahead of time and having flexibility to change things was the reason for the timer events to trigger reminders instead of a fixed reminder that fires when the DB is started.

[Micron]

Maybe consider having a db that is launched by Task Scheduler, which then sends out notifications/reminders when that db opens. This means that you're not relying on users opening a db in order to trigger the events. It might mean you need a dedicated pc for this; one that is exempt from IT push updates that would reboot it. The upside to this method is that if you come across other processes that could benefit from TS events you can add them.

[ArviLaanemets]

Se really, all you need is to check your database for some events with closing term, and send out meils to certain persons when such are found. Then why such heavy programming?

I hope you have split your database to DB to front- and back-end (backend being in Access, or e.g. in SQL Server). All you need is:
In your backend database have a procedure, which runs the query to get the list of events with term closing date matching certain criteria, and then sending out the meils (or SMS, but when the messages are sent at night, it will be overkill) to responsible persons.

In case the BE being Access file (or not full license SQL Sever DB), you have to create a scheduled task (on some server which has access to storage you keep your BE on), which at time you determine runs the script, which runs the stored procedure in your BE database. You can set it to run daily e.g. at nighttime.

In case the BE is DB on full license SQL Server, you simply create a Job in SQL Server, which also daily runs the stored procedure (probably again at nighttime) in your BE DB.

[Salty Mariner]

Se really, all you need is to check your database for some events with closing term, and send out meils to certain persons when such are found. Then why such heavy programming?

I hope you have split your database to DB to front- and back-end (backend being in Access, or e.g. in SQL Server). All you need is:
In your backend database have a procedure, which runs the query to get the list of events with term closing date matching certain criteria, and then sending out the meils (or SMS, but when the messages are sent at night, it will be overkill) to responsible persons.

In case the BE being Access file (or not full license SQL Sever DB), you have to create a scheduled task (on some server which has access to storage you keep your BE on), which at time you determine runs the script, which runs the stored procedure in your BE database. You can set it to run daily e.g. at nighttime.

In case the BE is DB on full license SQL Server, you simply create a Job in SQL Server, which also daily runs the stored procedure (probably again at nighttime) in your BE DB.

Yes, the DB is split. It is currently only running on one vessel...my vessel as I am still developing it. I actually only got up and running on my vessel a few weeks ago and have been busy changing things (mostly interface type stuff) as I get input from others. This issue was my big sore thumb sticking out.

I don't think SQL server will be needed, at least in the short term. Since this will be running on a vessel (Think small ship) each vessel will be an independent DB unto itself in. We have a satellite internet connection, but it is often slow and unreliable. I would like at some point in the future to find a way to synchronize at least personnel data between the other vessels and the office but have yet not figured out how to do this using our present communication equipment. I'm open to suggestions. About the best I can think of at present would be some sort of way to Load the needed tables from a remote DB (say at the office) make changes to them and run a query that would package up an update file to send back to the central DB for updating. the mechanics of this are likely beyond my current skill level (not that I can't learn. Also, I cannot find much reference material on how to do this. A shipping company I used to work for had their payroll program running like this (also an Access program) At first the updates were handled through email with a small DB that ran in the background that was appropriately named the "PayMail Updater". Later the corporate developer changed it to tables that were loaded remotely and you had a pause for however long it took for the tables to populate and the PayMail DB went away. I was never able to examine his code so I don't know exactly how he accomplished this. I was just the end user. It probably would open up a whole new can of worms with things like table structures and how to avoid duplicate Primary Keys on separate but similar DB's. Interesting food for thought at the moment.

[ArviLaanemets]

Yes, the DB is split. It is currently only running on one vessel...my vessel as I am still developing it.

A scheduled task can be run on personal computer/laptop also. The only caveat is, it must working at time the task must run.

... Later the corporate developer changed it to tables that were loaded remotely and you had a pause for however long it took for the tables to populate and the PayMail DB went away.

Doesn't look very effective way for this!
1. It looks the updating was done from same FE where all users worked (or BE was used, but all data was locked). The best way would be another FE (or even separate DB with it's own FE and BE) designed specially for this;
2. Probably the updating procedure did all the reading data, calculations, and writing data to table(s) as a single operation. When updating the table in this way takes too much time, then instead of this the procedure must read data, make all needed calculations, and save gotten data in separate table(s) (e.g. in updating FE or DB). When the procedure starts, as 1 step it clears all data from those temporary tables (for case something went wrong with previous update), processes rest of data reading, and writes data into temporary table(s). After that it updates real table(s) - either by insert/delete/update queries, or by clearing all data from original table(s) and inserting it anew (usually fastest way, but can be used only, when no autonumeric keys are used). Locking original tables is needed only for this part of procedure. And as last step of procedure, it clears temporary tables again - until next update, those temporary table(s) remain always empty.

... how to avoid duplicate Primary Keys on separate but similar DB's. Interesting food for thought at the moment.

Again, using updating DB (keeping all this in single FE can you easily reach DB size limits).
If there is a possibility the same info is read from clients DB repeatedly, then the updating DB must contain a table, where all PK's of all tables you read in from client DB's are registered along with matching PK values in main DB (e.g. tblKeys). This allows either ignore those entries in case the client's PK is registered, or to force the existing data in main DB to be updated (the choice is yours);
(on fly)
The temporary tables must contain a field for matching PK of main DB, which will ignored at 1st step of update. And probably also a similar field for every FK too (maybe you can cope without it, but it makes the converting of data more streamlined);
As 1st, the tables without any FK's must be updated. After that, the rest of tables in order, which ensures that the PK the FK is linked to was read previously;
The updating of main DB must be done table-wise, starting with temporary tables without FK's;
From operated temporary table, all records (not having the value of it's PK registered in tblKeys) are inserted into main DB table. And in case tblKeys is used, new values for client table PK's together with matching main table PK values are registered in tblKeys. After that, the matching temporary table is updated - the field for main PK is filled from main table. After that, the all FK's linked to this PK in other temporary tables are updated (or the field for main FK is filled);
And so on! When ordering of updating is done properly, for temporary tables having FK's there the matching main PK is present - so you can enter the new data into main table with proper values for FK's.