Simple Recordset Won't Work--Please Help

[cgdrake]

Since my previous attempt (posted in a previous thread with no response) didn't work, I decided to punt and simplify things a bit. So I wrote a routine that merely opens a recordset of section numbers, loops through them, and prints the section number in the debug window.

The program runs, but it only prints 0s in the immediate window. All section numbers are integers from 1 to approximate 6015. The sectionSQL variable that creates the recordset works--it's extremely basis, plus I tested it as a regular query before putting it into the VBA module.

Can someone please help? Code is below. Thanks.


Private Sub section_no()

Dim anssecid As Integer
Dim rstSections As ADODB.Recordset
Set rstSections = New ADODB.Recordset
Dim sectionSQL As String


sectionSQL = "select pvmt_analysis_section_id as anssecid from section_info"
rstSections.Open sectionSQL, CurrentProject.Connection

Do Until rstSections.EOF

Debug.Print anssecid


Loop


End Sub

 

[ajetrumpet]

...........

Private Sub section_no()

Dim anssecid As Integer
Dim rstSections As ADODB.Recordset
Set rstSections = New ADODB.Recordset
Dim sectionSQL As String


sectionSQL = "select pvmt_analysis_section_id as anssecid from section_info"
rstSections.Open sectionSQL, CurrentProject.Connection

[color=red]should you use [U].movefirst[/U] here I wonder??[/color]

Do Until rstSections.EOF

    Debug.Print anssecid [color=red]<---this is an integer variable.
                   does it have an assigned value yet?
                   It will print 0 by default if you haven't given it a value.[/color]

[color=red]need a [U].movenext[/U] action here or you will send 
the object into an endless loop![/color]

Loop


End Sub

 

[Rabbie]

In Addition to Adam's comments you have anssecid defined as a Integer and as a field in your query. Try this

Private Sub section_no()


Dim rstSections As ADODB.Recordset
Set rstSections = New ADODB.Recordset
Dim sectionSQL As String


sectionSQL = "select pvmt_analysis_section_id as anssecid from section_info"
rstSections.Open sectionSQL, CurrentProject.Connection
rstSections.movefirst
Do Until rstSections.EOF

Debug.Print rstSections!anssecid

rstSections.movenext

Loop


End Sub

Good luck

 

[cgdrake]

That worked, thanks guys! I'm doing a happy dance that I've run a recordset successfully.

I've gotten spoiled to the SCAN . . . ENDSCAN from Visual FoxPro where it assumes the record movement. VFP also lets you see your cursor when debugging. Not being able to see what's going on every step is hard for me.

 

[Rabbie]

Happy to help