what condition will determine whether a recordset is currently open?

[aquinori]

i'm having problem in closing my recordset while it is used in many purposes..

if i'd only knew a condition that would help me determine whether a current recordset is open.. it would help me a lot...

[twinnyfo]

aquinori,

You are kind of stuck between a rock and a hard place. First, there is no condition or property for a recordset that will indicate whether or not it is open, because if it is open, there it is, if it is closed, any action taken on that recordset will generate an error.

You may be able to trap the error before your code stops, however. For example, VBA will give error number 3420 of you try to do something with a recordset that is closed. In such a case, you test for that error, and if so, tell the code to resume where it left off (or notify you of the error and just stop without breaking anything).

However, the best advice I can give is to manage your recordsets very carefully. You should never close your recordset until you are sure you will never use it again in the particular sub or function. Additionally, although this is not a recommended practice, your code will respond better to a recordset left open (or just ignored) than one which is closed and then some action taken on it.

It's a good practice to wait until the end of a sub (or a particular loop) to close your recordsets or set them to nothing. This will free up memory, too.

Not sure if I really "answered" your question, but this info might help. Without seeing any of the code you have in question, we can't fix any particulars......

[Killer42]

One handy technique is to create a global variable corresponding to each recordset - let's say a Boolean "flag" variable. When you open the recordset, set the flag to True. When you close it (after testing the flag to see whether it's open) you set the flag to False. Any process that needs to know can now check that flag to see whether the recordset is open.

Another way to manage multiple processes which may want to access a common recordset is to use a counter rather than a simple flag. Each time you want to open the recordset, add 1 to the counter. Open the recordset only if the counter now = 1 (otherwise, it's already open). Likewise, each time a process is finished with the recordset, subtract 1 from the counter. When it hits 0, close. As long as that counter is > 0 you know the recordset's available for use.

You may come up with some other scheme yourself - don't be afraid to make up your own "attributes". :-)