Programmatically constructing a recordset name issue

[megmoo75]

I have several recordsets that I need to perform similar functions with. What I would like to do is create a common function and dymamically tell Access which recordset it should be processing. Does anyone know if there is a function like INDIRECT in Excel? I though EVAL might work, but haven't had any luck.

Basically, I want to construct the recordset name and then tell Access to treat the constructed name as if it's the actual recordset. So, if my recordset is named rs1, I want to get Access to return true or false for rs1.EOF, however, I want to use a variable (intCounter) to determine if I'm dealing with rs1 or rs2 or rs3 ....

Something like eval("rs" & intCounter & ".EOF&quot and get that to return TRUE or FALSE.

Anyone with ideas?

Thanks in advance.

 

[TonyJollans]

Hi megmoo75,

Don't think you can do exactly that, but this should work for DAO (don't know about ADO though):

Dim rst As DAO.Recordset
Set rst =

DataBase

.Recordsets("rs" & intCounter)
If rst.EOF Then

' Whatever you want to do

Enjoy,
Tony

 

[TonyJollans]

Hi megmoo75,

Or just ...

If

DataBase

.Recordsets("rs" & intCounter).EOF Then

' ...

Enjoy,
Tony

 

[megmoo75]

I am getting a "Compile Error: variable not defined" error which indicates that "Database" in "Database.Recordsets..." is not defined. Perhaps I'm missing a reference that I should have set?

I do have the "Microsoft DAO 3.6 Object Library" set, among other references.

Any ideas?

Thanks!

 

[TonyJollans]

Hi megmoo75,

You should replace Database with a reference to your database ..

Dim mydb as DAO.Database
Set mydb = CurrentDb
if mydb.Recordsets("rs" etc

Enjoy,
Tony

 

[megmoo75]

Ooops! Duh - I feel like an idiot. The results are close enough that I can do what I need. I'm not able to do this:

mydb.Recordsets("rs" & intCounter).EOF

But, I can do this and get a result:

mydb.Recordsets(intCounter).EOF

Where intCounter appears to be the ordinal position of the recordset in my list of recordsets.

Thank you so much!!

 

[CajunCenturion]

One possibility is place all of your recordset into a collection, and then you can reference the desired recordset by using the key value, which can easily be a variable.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein