Object Not Set Error

[gary.newport]

I have just opened a database I am working on and am encountering an error that did not exist previously and I cannot solve.

I have the same issue on two forms and it relates to a recordset variable called rst_units. Basically, vba is stopping with a Run-time error '91': Object variable or With block variable not set.

The two code pieces are:

Private Sub txt_course_Change()
    Dim rst_units As dao.Recordset
    Dim tbc As TabControl
 
    txt_class.RowSource = "qry_classes_by_course"
    txt_class.Value = ""
 
    [B][COLOR=darkred]Set rst_units = cdb.OpenRecordset("SELECT unit_id FROM tbl_course_units WHERE course_id=" & CInt(txt_course.Value))[/COLOR][/B]
    
End Sub

Sub unit_analysis(course_id As Integer, course As String)
    Dim rst_course_units As dao.Recordset
    Dim rst_units As dao.Recordset
    Dim unit_id As Integer          ' Will hold the unique identifier for each unit
    Dim units As Integer            ' Will hold the total number of units
    Dim unit_no As Integer          ' Will hold the individual unit number
    Dim passes As Integer           ' Will hold the total number of passes
    Dim merits As Integer           ' Will hold the total number of merits
    Dim distinctions As Integer     ' Will hold the total number of distinctions
 
    ' Let's identify how many units have been assigned to this course
    Set rst_course_units = cdb.OpenRecordset("SELECT * FROM tbl_course_units WHERE course_id=" & course_id)
 
    If Not rst_course_units.EOF Then
        rst_course_units.MoveLast
            units = rst_course_units.RecordCount
        rst_course_units.MoveFirst
 
        lbl_information.Value = lbl_information.Value & "<font color=""blue"">The course <b>" & course _
            & "</b> has " & units & " units associated with it</font><br>"
 
        count_classes course_id
 
        Do Until rst_course_units.EOF
 
            unit_id = rst_course_units!unit_id
            Set rst_units = cdb.OpenRecordset("SELECT * FROM tbl_units WHERE unit_id=" & unit_id)
 
            unit_no = rst_units!unit_no
            passes = rst_units!no_pass
            merits = rst_units!no_merit
            distinctions = rst_units!no_dist
 
            lbl_information.Value = lbl_information.Value & vbTab & "<font color=""purple"">Unit " & unit_no & " has " _
                & passes & " PASS, " & merits & " MERIT and " & distinctions & " DISTINCTION criteria</font><br>"
            rst_course_units.MoveNext
        Loop
    Else
        lbl_information.Value = lbl_information.Value & "<font color=""blue"">The course <b>" & course _
            & "</b> has 0 units associated with it</font><br>"
 
        count_classes course_id
    End If
    rst_course_units.Close
    [B][COLOR=darkred]rst_units.Close[/COLOR][/B]
 
    Set rst_course_units = Nothing
    Set rst_units = Nothing
End Sub

These are on two separate forms and the second code works on my home system but not here at work (or at least, it did).

Both forms begin their coding with:

Option Compare Database
Dim cdb As dao.Database

The lines reporting the error are bold and in red above.

Any ideas?

 

[pr2-eugin]

You have declared the object but have failed to set it. You do not have to use an object at all, simply use CurrentDb.OpenRecordset instead of cdb.OpenRecordset

 

[SteveH2508]

Set cdb as CurrentDb needs to be added before the line in red - you are not initialising the database object.

 

[gary.newport]

Such an obvious thing and the reason why it had worked at home: the code I had written at home had the SET declaration whilst this new section did not. Thank you.

pr2, the reason for using the cdb is simply that on a few of my code pages I have lots of calls to the currentdb and the line lengths get severe. So to save my little fingers and make the code easier to read I went for this option.

One thing that is curious still is that the .close statements still throw up the error; even though the rst_units is set - and used! If I remove the rst_units.close line then everything works fine but I don't like not closing properly (I still set each recordset to Nothing, so reducing memory usage somewhat). Any ideas?

 

[pr2-eugin]

Because in a way, rst_units is out of scope ! You have set/opened the recordset object inside the If, but trying to close it outside the If, which makes it unreachable; based on the compiler interpretation - the object was never opened, so how can you close it? Try using rst_units.close inside the If.

 

[gary.newport]

I had considered this so tried the close in the IF and, as you said it would, it worked. Thank you

 

[pr2-eugin]

No problemo ! Good luck !