Scope of Class Module Recordsets

[Sorrells]

I am consistently receiving an Access Error, "Object variable or With variable not set". I am beginning to believe that the scope of an open recordset is limited to a procedure but I hope this is not the case.

I use several recordsets in many procedures within the class module. I am declaring the database and the recordsets in the Declarations Section of the module. For example:

Option Compare Database
Option Explicit
Private dbs As Database
Private rst_RO As Recordset 'recordset of the Ordered Room

Then in the Form's OnOpen Event I am setting the database and all of the recordsets for example:

Private Sub Form_Open(Cancel As Integer)
Set dbs = CurrentDb
Set rst_RO = dbs.OpenRecordset("Room_Ordered", dbOpenDynaset)

My thought was that the recordsets would remain open until the Form's OnClose Event where I close the database and the recordsets.

However, I am being shot dead in the water with the above error!

I have looked in this forum and found one suggestion that the recordsets be opened on the Form's OnLoad event for them to have a scope covering the entire class module. Is there agreement to this? Or is the scope of a recordset limited to a single procedure?

I will greatly appreciate any responses to this question!

Regards, Sorrells

Regards, Sorrells

 

[cmmrfrds]

Try declaring your objects/variables as public instead of private.

 

[Sorrells]

Again, I may be incorrect but I understand the PRIVATE keyword to have a scope of the entire class module but not outside it. This is what I want. I am able to use variables declared as PRIVATE in the same way throughout the class module.

I really think the problem or answer lies somewhere else. At any rate there is a lot going on in the program, and if possible during this phase of development, I'd like to restrict the recordsets to the Class Module only.

Thanks, Sorrells Regards, Sorrells

 

[Sorrells]

Help! It would seem to be that a person well experienced with VBA should be able to answer this question. I have now placed the SET Recordsets and CLOSE Recordsets into two functions respectively but am not sure this approach will work.

Does anyone know of the scope of the Set Recordset in a form class module??? Regards, Sorrells

 

[cmmrfrds]

Yes

 

[Sorrells]

Do you wish to share your knowledge? Regards, Sorrells

 

[LittleSmudge]

Well this lot works well for me.
Form_Open does the SET
Form_Current sets up Text5 to display the first in the Recordset
Then cmdNext_Click moves the Recordset on to the next record and re-displays Text5




Option Compare Database
Option Explicit

Dim dbs As Database
Dim rstRO As DAO.Recordset 'recordset of the Ordered Room

Private Sub Form_Open(Cancel As Integer)
Set dbs = CurrentDb
Set rstRO = dbs.OpenRecordset("SELECT * FROM MyTbl", dbOpenDynaset)
End Sub

Private Sub Form_Current()
Text5 = rstRO!MyNum
End Sub

Private Sub cmdNext_Click()
rstRO.MoveNext
Text5 = rstRO!MyNum
End Sub

Private Sub Form_Close()
rstRO.Close
End Sub





'ope-this-'elps

G LS

 

[Sorrells]

LittleSmudge et al.,

I think I found the answer to this problem. Let me know if my reasoning is incorrect.

Yes the scope of Set Recordset is throughout a class module if the Recordset has been declared as PRIVATE, however,

If the END keyword is also in the class module to jump out of a procedure and this is executed, the setting of the Recordset, like variables available in the form, lose their value and thus if the Recordset is used again, Access will not find it and generate the error.
Regards, Sorrells