MovePrevious not working properly when using with Excel

The related question attached to this one explains my current situation. I'm having some strange issues with using the MovePrevious command. I was able to reproduce my issue once again 100% of the time.

In attempting to isolate the issue, I took GrahamSkan's code and made small edits to it which were closer to how my code is setup. An issue occurred before I could get to attempting to reproduce my issue with his code.

The following is GrahamSkan's code, except instead of having the debug lines in the same Sub, I've created a new sub which contain them. However when I do this, it's telling me that "rs" is not defined. I'm not sure why and it's probably very simple. This stuff is somewhat new to me. Here is the code :


Option Explicit

Public Sub Command1_Click()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strConnection As String
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestTable2.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    cn.Open strConnection
        rs.Open "Select * FROM [Data$]", strConnection, adOpenStatic, adLockOptimistic, adCmdText
        rs.Filter = "ID <> NULL AND Table <> NULL AND Rule <> NULL AND Account <> NULL AND Status = NULL"
            'Debug.Print rs.Fields("Account").Value
            Do While Not rs.EOF

                Debugger
                
            Loop
        rs.Close
    cn.Close
End Sub

Public Sub Debugger()
            
            Debug.Print rs!Account
            rs.MoveNext
            Debug.Print rs!Account
            rs.MoveNext
            Debug.Print rs!Account
            rs.MovePrevious
            Debug.Print rs!Account

End Sub

Open in new window

thach1ef2Asked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
Your subroutine's references to the recordset variable are 'out of scope'

Option Explicit

Public Sub Command1_Click()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strConnection As String
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestTable2.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    cn.Open strConnection
        rs.Open "Select * FROM [Data$]", strConnection, adOpenStatic, adLockOptimistic, adCmdText
        rs.Filter = "ID <> NULL AND Table <> NULL AND Rule <> NULL AND Account <> NULL AND Status = NULL"
            'Debug.Print rs.Fields("Account").Value
            Do While Not rs.EOF

                Debugger rs
                
            Loop
        rs.Close
    cn.Close
End Sub

Public Sub Debugger(parmRS As Recordset)
            
            Debug.Print parmRS!Account
            parmRS.MoveNext
            Debug.Print parmRS!Account
            parmRS.MoveNext
            Debug.Print parmRS!Account
            parmRS.MovePrevious
            Debug.Print parmRS!Account

End Sub

Open in new window

0
 
thach1ef2Author Commented:
Side note, the first time I get an error is in the debugger sub routine which attempt to use rs!Account.
0
 
thach1ef2Author Commented:
Jeez, that was a foolish mistake. I always put my declarations at the top outside of sub routines so that I can use them wherever I want. Totally didn't even notice that his were inside the sub.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.