ADO Recordset object- When does it die?

[Elena]

Quick Question:

If you open a recordset object in an event procedure, does it die when the procedure ends?

For example, I am opening a recordset when the form loads to fill a combo box. Can I still use the same recordset in the combo box LostFocus event procedure? Or do I have to open it for each procedure it is used in?

Thanks,
Elena

 

[vb5prgrmr]

That depends upon where you declare it. If you declare it in the form_load procedure then it is gone when you exit the procedure. If you declare it in the general declarations section of the form then it is still in scope.

Good Luck

 

[AndyWatt]

It's a matter of "scope". If you Declare an object (in this case a Recordset) within a procedure it will only be visible within that procedure. If you Declare and object (or for that matter any data type) as "Private" in the declarations section of a Module or Form then it is visible throughout that module/form. If you declare something a Public in the declarations section then it will be visible to any other part of the program, broadly speaking (there are a couple of caveats to this, but it gives you the idea, I hope)

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[AndyWatt]

vb5prgrmr

Dang, you said it shorter! And faster!

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[Elena]

Let me see if I understand this. If I declare the recordset as in:

Public rstRecords as Recordset

in my declarations section, and then I open it in the event procedure, it will still be open when the procedure ends? Is that right?

One other question.

I have a database with 5 tables, and they are all relatively small. I think the largest one has 194 records. I plan to use it locally. Would it be prudent to just open all the recordsets I plan to use in a standard module at one time? It seems like a lot of unnecessary coding to open and close them for each procedure.

Thanks,

Elena

 

[AndyWatt]

> Private rstRecords As Recordset

Yes, it will stil be open unless you Close it.

> Would it be prudent ...

Perhaps. It would depend on what you want to do with them.

Personally I open one Connection per application, and then Open and Close Recordsets as needed.

If you're only going to use a particular Recordset once and once only, then I would not keep it open for any longer than needed.

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[BobRodes]

I wouldn't use a standard module, unless you are using it for other reasons. Let me go over the different variable scopes for you:

First, we're talking here about a standard exe program, not an activex dll or exe, which have other scoping issues that I will leave out (unless you ask).

In a form module, you can declare a variable either in the general declarations section at the top of the module, or inside a procedure. If you declare inside a procedure, it's called a "local" variable and is only available inside that procedure and only while the procedure is running.

If you declare in the general declarations section, you can declare the variable as either public or private (dim is the same as private). Private variables declared here are called "module-level" variables, and they are available to any procedure in the module (i. e. the same code window), and they last as long as the module does. With a form module, this means as long as the application is running. They are NOT visible to other modules, such as other forms. A public variable is visible to all modules in the application, but other modules have to precede the variable with the name of the module, e. g. "Form1.VariableName".

Now, Standard modules are a bit different. Public variables in standard modules are visibile to the entire app, for as long as it runs. You don't have to precede the variable name with the name of the module, as you do in other types of modules (however, you can if you like). Private variables in standard modules are visible only to the standard module. Local variables have the same rules as in other modules.

HTH

Bob