Scope of Recordsets in a Form

[Sorrells]

NOTE: In many respects this is another way of expressing my recently entered topic: Test for Open Recordsets.

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

 

[Nero]

I may be wrong ( I'm sure someone will correct me) but do you need to declare your database and recordset as public and not private in a module?

 

[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

 

[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??? [sad]

 

[chrismcbride]

I am unclear on how it is you have gone about dimensioning your recordset variables, but this is what I think...
-If you declare a private variable in the general declarations section of a form module, it will stay active for the life of that form's session and be available to all functions and sub procedures within that form module...
Option Compare Database
Option Explicit
Dim rst As Recordset
Dim rst2 As Recordset
Dim dbs As Database
Dim strSql As String
...
- If you dimension a private variable in a function or sub procedure, then it is only available to that function or sub procedure.
- If you want to dimension a variable in a module other than the form module that will call or use the variable it must be a public variable (Public varID_Number As Long)

I will send you an example of a test db that illustrates how you might go about what you describe, if you send me your address.

Alternatively you should look up scope in the help menu and see what M/S has to say...
HTH
Chris

 

[Sorrells]

chrismcbride et al,

My code is as you indicated chrismcbride.

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 in the General - Declarations section of the module, however,

If the END keyword is also in the class module to jump out of a procedure and if it 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

 

[chrismcbride]

Sorry I do not understand what you are tring to say. Probably the Monday blues.
Re-state with examples or post some code.
Chris