Can you nest a recordset within another recordset?

[cgdrake]

I tried to run the code below, but got an error saying that I was not providing a required parameter, on the line marked in red--it runs fine up to that point. I'm trying to nest looping through one recordset within another (sections with projects on them, an expansion of the simple recordset question y'all helped me with earlier).


Private Sub overlay_no()
Dim rstSections As ADODB.Recordset
Set rstSections = New ADODB.Recordset
Dim rstRehabs As ADODB.Recordset
Set rstRehabs = New ADODB.Recordset
Dim sectionSQL As String, rehabSQL As String
Dim anssecid As Integer, count As Integer

rehabSQL = "select * from rehab_proj_join where pvmt_analysis_section_id=rstSections!anssecid"

sectionSQL = "select pvmt_analysis_section_id as anssecid from section_info order by pvmt_analysis_section_id"

rstSections.Open sectionSQL, CurrentProject.Connection

rstSections.MoveFirst

count = 1

Do Until rstSections.EOF

Debug.Print rstSections!anssecid

rstRehabs.Open rehabSQL, CurrentProject.Connection
rstRehabs.MoveFirst

Do Until rst.Rehabs.EOF

Debug.Print rstRehabs!proj_date
count = count + 1
rstRehabs.MoveNext

Loop

rstSections.MoveNext

Loop

End Sub

 

[KenHigg]

The trick to finding an error like this is to debug the code and step through the code one line at a time and look at what the varibles are at different points. Given that, I think your problem with this code is that the first sql string is not valid when it come time to execute it. Try setting it up further down:

Private Sub overlay_no()
Dim rstSections As ADODB.Recordset
Set rstSections = New ADODB.Recordset
Dim rstRehabs As ADODB.Recordset
Set rstRehabs = New ADODB.Recordset
Private Sub overlay_no()
Dim rstSections As ADODB.Recordset
Set rstSections = New ADODB.Recordset
Dim rstRehabs As ADODB.Recordset
Set rstRehabs = New ADODB.Recordset
Dim sectionSQL As String, rehabSQL As String
Dim anssecid As Integer, count As Integer



sectionSQL = "select pvmt_analysis_section_id as anssecid from section_info order by pvmt_analysis_section_id"

rstSections.Open sectionSQL, CurrentProject.Connection

rstSections.MoveFirst

count = 1

Do Until rstSections.EOF

Debug.Print rstSections!anssecid


rehabSQL = "select * from rehab_proj_join where pvmt_analysis_section_id=" & rstSections!anssecid


rstRehabs.Open rehabSQL, CurrentProject.Connection
rstRehabs.MoveFirst

Do Until rst.Rehabs.EOF

Debug.Print rstRehabs!proj_date
count = count + 1
rstRehabs.MoveNext

Loop

rstSections.MoveNext

Loop

End Sub

I may not have this exactly right -

 

[cgdrake]

The first SQL statement work, the one for the sections because the statement Debug.Print rstSections!anssecid works fine. The program coughs when I get to opening the second recordset. I will try setting things up. Thanks for the help.

So you can nest recordsets then?

 

[KenHigg]

Sure, I posted one here in post number 7

http://www.access-programmers.co.uk/forums/showthread.php?t=99777