Open recordsets as public or global variables

[Azhar.iq]

Is there any way of opening recordsets as public or global in a form??

E.g. if i do something like

Set rs = db.OpenRecordset("TblCustomers", dbOpenSnapshot, dbReadOnly)

the variable rs should be available in all the private sub i have on that form, and i should be able to access records without opening database/recordsets for individual subs.

 

[Azhar.iq]

In addition to opening recordsets, i would also like this function to perform some checks whether the table contains specific data or not.

Scenario is, i have a form with about 4-5 buttons and each button on click, the system has to access one table to ensure there is data in table before proceeding to actual functionality. I made the form, but for each button i had to write the same code (to open database, recordset and checking of data). Is there any way i could do these three things in a public function or module and the actual code in private subs??

 

[pbaldy]

If you declare the variable at the top of the form (above first sub) it will be available to any sub on the form.

You can create a function that returns a Boolean value, and test that in the private subs:

If FunctionName() = True Then

 

[The_Doc_Man]

Sometimes I get accused of being a bit pedantic, but here I think I can offer some useful insights.

1. If you declare the recordset ANYWHERE in the form (class declaration area or top of a private subroutine), it exists only as long as the form is open. But, if such a thing makes a difference, you could declare the variable at the top of a general module's declaration area in which case the recordset persists if you close the form. The variables in a general module declaration area don't go away until Access closes (or if you are debugging and you do a code reset or take a trap that results in a code reset).

2. When using the recordset shared among many subroutines, particularly if they are called from events on the form, remember that you can't trust the position of the recordset's .BookMark property (that tells you where the recordset currently points). On entry to any subroutine that uses this proposed recordset, you will need to do a .MoveFirst, .MoveLast, .FindFirst, or something to let you know where you are.

3. You can certainly create a general module with tools to let you look into the recordset contents even if it is created in a class module. You would have to pass in the recordset as a variable if you do that because variables declared inside class modules normally don't exist from the outside of the class modules (i.e. not in scope of access). However, the VBA argument passage specification allows you to make a copy of the recordset (ByVal) or a usable pointer to the recordset (ByRef) as a formal parameter to a subroutine or function.

 

[Rx_]

Excellent and useful post, and you made me look it up! LOL - Deserves a thanks.
The definition of pedantic is someone who has a very narrow focus on a subject and tends to overly show off their knowledge by getting caught up in small details or by pointing out mistakes on the subject.
An example of someone who is pedantic is an English teacher who corrects grammar mistakes all the time.

 

[pbaldy]

1. If you declare the recordset ANYWHERE in the form (class declaration area or top of a private subroutine), it exists only as long as the form is open.

If you declare a recordset within a private subroutine, doesn't it cease to exist when the subroutine ends (i.e. it won't exist as long as the form is open)? Normal declaration with Dim of course, not using Static.

 

[Azhar.iq]

If you declare a recordset within a private subroutine, doesn't it cease to exist when the subroutine ends (i.e. it won't exist as long as the form is open)? Normal declaration with Dim of course, not using Static.

Exactly what i thought.