Initializing Recordsets as Class Variables during class Initialization prompts "Record Deleted" When accessing recordsets in Sub

Question

I am refactoring an old MS Access Database, where the code distributes a number of enrolled Students among a list of internship openings into a distribution table. The code was cluttered among several forms and modules and called three times. I consolidated the code into a class "Distribution" but encounter now a "Record deleted" error which i don't understand, as it is dependent on the location of the recordset initializiation.

The code before worked like this.

    public sub distribute()
    dim students as recordset
    dim openings as recordset
    dim distribution as recordset

    set students = Currentdb.OpenRecordset("Einschreibung_studenten_liste_verteilung", db_open_dynaset)
    set openings = Currentdb.OpenRecordset("Select*From pzeitraum_temp Order by Rnd(-Timer()*[id]",db_open_dynaset)
    set distribution = Currentdb.OpenRecordset("praxiseinteilung_tmp", db_open_dynaset)

    'do a bunch of stuff, comparing students and openings and insert into distribution if a set of requirements are met

The logic spanned about 200 lines and was nested to about 6 or 7 levels. I separated the logic into several smaller subs, which now get called from the distribute sub. I thought the easiest and most concise way to keep functionality was to make the recordsets class variables and set them during class initializiation:

Class "Distribution" Pseudocode:

   dim students as recordset
   dim openings as recordset
   dim distribution as recordset 

   Private Sub Class_Intialize()
   set students = Currentdb.OpenRecordset("Einschreibung_studenten_liste_verteilung", db_open_dynaset)
   set openings = Currentdb.OpenRecordset("Select*From pzeitraum_temp Order by Rnd(-Timer()*[id]",db_open_dynaset)
   set distribution = Currentdb.OpenRecordset("praxiseinteilung_tmp", db_open_dynaset)
   End Sub

   Public Sub Distribute()

   'do stuff
   routine1
   routine2
   'do some more Stuff
   End Sub

   Private Sub routine1()
   'do stuff with recordset
   routine3
   'do some more Stuff
   End Sub

   Private Sub routine2()
   'do stuff with recordset
   End Sub

   Private Sub routine3()
   'do other stuff with recordset
   End Sub

When I tested the code it ran, but didn't produce sensible results. During Debugging i noticed, that the first

    openings.FindFirst("Key =" & Searchvalue)

resulted in a

   openings.NoMatch = True

but the Record exists.I inserted a loop at the start of the distribute sub, going through all openings records simply printing the key.

 With openings
      Do While .EOF = False
           Debug.Print(!key)
           .MoveNext
      Loop
 End with

This failed at the very first record, prompting a runtime Error "Record Deleted".

When I inserted the same loop at the end of the Class_Initialize Sub it ran without error and printed all the keys in the openings table. If I set the recordsets in the distribute sub and not during class initialization the code works and the recordsets have no issues.

But Why?

Since my goal is to refactor the code into a version where it is clear what it does and why it does it for easy maintenance i am hesitant to compromise.

Declaring the recordsets as class variables should make them available to all subs in the class. They should therefore be set during intialization. Setting them in a certain Sub begs the question why in this sub and which subs do have now access to these recordsets and why? This is exactly that kind of apparent randomness i am trying to avoid.

Edit1. Added "End Sub" to Class_Initialize Edit2.Corrected the Pseudocode for the "FindFirst" Method

Should Private Sub Class_Intialize() have a companion End Sub? — June7
– June7, Commented Apr 9, 2023 at 21:10

Gustav · Accepted Answer · 2023-04-10 06:24:10Z

0

Method FindFirst expects a literal where clause including the name of the field to search, so try:

' String key.
Value = "123"
openings.FindFirst "Key = '" & Value & "'"

' Numeric key.
Value = 123
openings.FindFirst "Key = " & Value & ""

answered Apr 10, 2023 at 6:24

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

June7 · Accepted Answer · 2023-04-11 23:33:04Z

0

I found my error and thank everyone who invested time in my question.

The reason for this behaviour was a grave oversight on my part. While the three recordsets are needed for the distribution, they of course need to be calculated beforehand.

I made the mistake of creating the Distribution1, Distribution2 and Distribution3 objects before I calculated the recordsets. The recordsets were therefore altered after object initialization and rightfully failed to give proper results. During refactoring I accidentally put step 3 between step 1 and 2.

I would have preferred a more meaningful error message but the fault was mine in the end.

edited Apr 11, 2023 at 23:33

June7

21.7k8 gold badges28 silver badges36 bronze badges

answered Apr 11, 2023 at 21:08

Steve Piller

638 bronze badges

Collectives™ on Stack Overflow

Initializing Recordsets as Class Variables during class Initialization prompts "Record Deleted" When accessing recordsets in Sub

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related