Test for Open Recordsets (1 Viewer)

[Sorrells]

I have a form in which I open all recordsets I use in the form in the Form OnOpen event. Since then, I have had two occasions where I must close the form abruptly at points where some recordsets appear not to be set as I am receiving the error in the Form OnClose event when closing the recordsets of "Object variable with block variable not set."

I attempted to work around this by setting all the recordsets again before closing them in this OnClose event but am receiving the same error when this code executes.

I am clueless as to why this is happening. The Form OnOpen event is the first procedure in the class module where I am setting these recordsets and they are not coded closed until the OnClose event of the form.

What am I doing wrong? Is there a way I can test the recordsets with an IF statement. What bugs me the most is that I am sure the recordsets ARE set and the database is set first to CurrentDB.

Any advice will be much appreciated.

 

[Sorrells]

I am continuing to be blockaded by this problem. I declare recordsets as Private in the declarations section of a form class module. In the Form's OnOpen Event I set the recordsets to tables.

It appears that the PRIVATE declartion may have effect throughout the class module but the SET statement does not.

It also appears that to keep the program at a memory minimum, I must close the recordset in every procedure where I set it.

Since I use the same recordset in many procedures within this Class Module, this seems to me to be very inefficient.

Is the above true? Is there a better way? I can't use functions as I perform different actions on the recordsets depending on the context of the progam.

Again, any help will be greatly appreciated. My time is short so please respond ASAP if you have an answer.

My thanks in advance, Sorrells

 

[Travis]

Is it possible that the recordset has already been closed before it reaches the OnClose Event?

What the error is telling you is that the recordset does not exist.

This is one of those times you can use "On Error Resume Next" followed by the code that closes the recordsets which you follow by setting each recordset = Nothing. This way you know they are closed.

 

[Sorrells]

Travis,

I do not close the recordset until the OnClose event of the form. I've searched through the form, but in fact the design intent is to keep all recordsets open until the form was closed, and this is via a command button.

I get the impression that you think the error should not be occurring as well. With the debugger, I can see that the SET statement is being executed.

I will trace execution one more time through the code then begin adding repetitive lines of code to get past this problem. It just doesn't seem right!!!

 

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