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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thach1ef2Author Commented:
Side note, the first time I get an error is in the debugger sub routine which attempt to use rs!Account.
0
aikimarkCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.