Not sure I can explain this very well but here goes:
I'm connecting into a database and opening up a recordset :

<%strQuery = "SELECT dbo.TWkSheetToDetLink.Det "%>
<%strQuery = strQuery & "FROM dbo.TWkSheetToDetLink INNER JOIN dbo.TWorksheetRpts ON dbo.TWkSheetToDetLink.RptNo = dbo.TWorksheetRpts.RptNo "%>
<%strQuery = strQuery & "WHERE (dbo.TWorksheetRpts.RptNo = '45917')"%>
<%Set MyDetRS = adodataConn.Execute(strQuery)%>

This will return 3 results (NU701,NU702 and NU703)

What I want to be able to do is to loop thru these results and open another (and set) 3 recordsets, something like this :

<%Dim MyDetNo%>
<%MyDetNo=0%>
<%DIM MyNewDet%>
<%Do While not(MyDetRS.eof) %>
<% response.write MyDetRS("Det") %>
<% strQuery = "SELECT dbo.VWorksheet.* FROM dbo.VWorksheet "%>
<% strQuery = strQuery & "WHERE (IJobN = 45917) AND (IDetCode = '" & MyDetRS("Det") & "')"%>
<% MyNewDet = "MyDet" & MyDetNo%>
<% Set MyNewDet = adoDataConn.Execute(strQuery)%>
<% MyDetno = MyDetNo + 1%>
<% MyDetRS.MoveNext%>
<%LOOP%>

I've declared a variable (MyDetNo) and increment it's value by 1 for every row the original recordset returned and am, trying, to use this as a unique name for the 3 new recordsets I'm opening.

It seems to work fine until I try to reference these recordsets later on the page. Resulting in the error :

Microsoft VBScript runtime error '800a01a8'

Object required: 'MyDet0' (MyDet0 being the name of the first recordset).

Any offers or am I barking mad?

<% MyNewDet = "MyDet" & MyDetNo%>
<% Set MyNewDet = adoDataConn.Execute(strQuery)%>

Ok.. You can't define different recordsets this way. You're setting MyNewDet to be a String type in the first line, then expecting it to be a RecordSet type in the second.

What are you specifically trying to accomplish? Loop through the first query, and display some data from the second query based upon values obtained in the first?

That makes sense.

What I'm trying to is this : A user selects a type of report and attached to this report they'll be 1 or more 'tests' assigned to each report. The amount of tests changes dynamically. So the first recordset is being used simply to find out which report they have selected and how many tests are assigned to it.

The second part (the loop) is to build individual recordsets for each test against the report they chose. And I'm trying to give (set) a unique name to each, so I can reference them later in the code (populating a table with the values that they contain).

Any offers?

Thanks for your response by the way

This sounds earily familiar to a project I'm on right now. Bear with me, this is going to get thick....

We have a system that presents several different transactions to our associates for deductions and reimbursements from/to their paycheck. These transactions are rule-based, and can be dynamically assigned by record manipulation in a DB. What I did was create several tables: tblRules, tblRuleList, tblRuleXLAT

The first is the definition of the rule, and the ASP function that is linked to the rule. The second table is the list of rules and which transaction they are linked to. The last one, tblRuleXLAT (XLAT - Translation), contains values for the rules used by the rule functions themselves. This last one is optional.

When the associate logs on, I have a function that checks eligibility and returns back the ID of the transaction and whether their eligible or not. That eligibily function queries the DB for all active transactions and loops through them, and queries the tblRules joined against the tblRulesList tables to find out if there are any rules. If there are rules, I pull the rule function name from tblRules table and call it with the ASP function EVAL (There are two similar functions, EXEC and EVAL, not widely documented, they both execute ASP code in the fly. Not positive on the order of this, but one would evaluate the statement "a = b" as True (or False), the other would set the value of a to be the value of b).

Then I created a bunch of ASP functions called Rule Functions. The rule functions have a special naming convention and are in a special inc-ruleFuncs.asp include file. Each of my rule functions has several required parameters, such as a default value from tblRules, the rule that called it (so I can hit tblRulesXLAT for additional values), the transaction it's linked to, etc.

When the rule function returns to the eligibility function, it simply returns a True or False. If any transaction returned a False, then the entire transaction is ineligible, and any that return all True is eligible. A new array is built with all of the transaction IDs and their eligibility status. This array is returned to the calling page. That page can then loop through the final returned array to decide what to is available. I use the IDs of the "True" results in a query to get more detailed information to display on the page, and the "False" IDs I use to pull error messages from another function based on the failed rules so the user knows why they are ineligible.

Since only one rule can be called at a time I have a recordset for the eligibility function, and one for all of the rule functions to share.

I hope this gives you some ideas... it may not be the perfect route, but it works remarkably well for me...

Thanks for your response, the solution I found was, as it turned out, to be fairly simple. All I had to do was instead of assigning the recordset to a string was to assign it to an array. So as oppose to MyDet0 = recordset it was changed to MyDet(ArrayCounter) = recordset and all was well.

Thank you for replying and I'll have a look into those two functions you mentioned.

Love and peace.