Refer to a recordset with a variable???

  • Thread starter Thread starter Frank B
  • Start date Start date
F

Frank B

AGT is a PUBLIC DAO recordset that is open.

Off in a subroutine I only have the name of the recordset
(e.g. Z$ = "AGT"). Clearly, I can access data in the
recordset by hardcoding:

Y = AGT("ID") ' which properly assigns the ID value to Y.

What I wish I could do is:

Y = Z$("ID") ' to accomplish the same assignment to Y.

My subroutine serves it's purposes for about 15
recordsets, and I currently must use a case statement to
resolve that the value of Z$ refers to AGT wherein the
assignment is hardcoded. A cluttered solution, that works.

Can the fields in a recordset be refernced with the name
of the recordset being a variable??? I am aware, the
fields can be referenced with a variable, but how do you
reference the recordset itself with a variable?

Thanks,
Frank
 
Probably the easiest way to do this is to pass the
recordset ByRef to the subroutine.

Public Sub DoSomething(rst as DAO.Recordset)


That way, you could:

Call DoSomething AGT


Otherwise, try the Eval function. I havent' tried this,
so you might need to play with it.



Chris
 
Chris - thanks for the effort, and yes, a recordset cam be
passed to a subroutine as a parameter. However, that is
not the question I asked, and it is not possible in my
situation. The string "AGT" is found in a record in a
table. It happens to be the exact name (by design) of a
recordset that is already open. The subroutine must
extract data from the open recordset. The sub must
service about 15 recordsets. As I explained in my post, I
currently use a case structure to locate and map the
string to the hardcoded assignment statement. This works
just fine, but I wondered if there was a way to use the
string (per my original post).

How do you access a recordset using a variable???

Frank
 
Frank B said:
Chris - thanks for the effort, and yes, a recordset cam be
passed to a subroutine as a parameter. However, that is
not the question I asked, and it is not possible in my
situation. The string "AGT" is found in a record in a
table. It happens to be the exact name (by design) of a
recordset that is already open. The subroutine must
extract data from the open recordset. The sub must
service about 15 recordsets. As I explained in my post, I
currently use a case structure to locate and map the
string to the hardcoded assignment statement. This works
just fine, but I wondered if there was a way to use the
string (per my original post).

How do you access a recordset using a variable???
Click to expand...

Hmm. I haven't tried this, but could you add all these recordset
objects to a collection, and index the collection by the name? Then you
could use the string you get from your table as the index to the
recordset you want.
 
Hi Dirk,... thank you for your suggestion.

Your suggestion is a bit more elegant than using the case
statement I use now. I think a simple global array where
each entry contains the duplet "Index" and
associated "recordset" would similarly accomplish what you
suggested. In my way, or yours, while we are solving the
task at hand, my original question remains unanswered. Is
it possible to simply use a variable? We can reference
the fields in a recordset with a variable, but what about
a variable for the recordset itself? Maybe, it just can
not be done.... I expected the answer might be
embarrasingly simple. (or, at least some MS guru would
advise it can not be done)

Thanks,
Frank
 
Frank B said:
Hi Dirk,... thank you for your suggestion.

Your suggestion is a bit more elegant than using the case
statement I use now. I think a simple global array where
each entry contains the duplet "Index" and
associated "recordset" would similarly accomplish what you
suggested. In my way, or yours, while we are solving the
task at hand, my original question remains unanswered. Is
it possible to simply use a variable? We can reference
the fields in a recordset with a variable, but what about
a variable for the recordset itself? Maybe, it just can
not be done.... I expected the answer might be
embarrasingly simple. (or, at least some MS guru would
advise it can not be done)

Thanks,
Frank
Click to expand...

I'm always reluctant to say something "cannot be done", but my opinion
is that this can't be done. You may be able to refer to some scalar
property or field value of the recordset by using the Eval() function,
but not, I think, the recordset itself.
 
Back
Top