Loop through Query Records

[DaveLondon]

Hi there,

I'm trying to write a button click module in Access 2000 that will loop through all the records in a query. The code I have so far is:

Dim rs As Recordset
Dim strSql As String

strSql = "SELECT ISIN FROM Qry_Unique_Stocks;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

Do While Not rs.EOF
Debug.Print rs!ISIN
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

I keep getting a type mismatch error. I've tried debugging the SQL but no joy I know the content of the loop isn't doing much at the moment. It will eventually pass the value of ISIN into another query. Any help in getting this first part to work is greatly appreciated !

Cheers, Dave.

 

[SkipVought]

Type error missmatch on WHAT statement?

Does your SQL string actually execute in a query?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveLondon]

The debugger doesn't highlight it. I just get the message box when I click the button.

Yes the SQL executes fine.

 

[SkipVought]

What happens when you STEP thru the code?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Dim rs As [!]DAO.[/!]Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

This is the code I would use. Note this requires a reference to the DAO object library.

    Dim rs As DAO.Recordset
    Dim db as DAO.Database
    Dim strSql As String
    
    strSql = "SELECT ISIN FROM Qry_Unique_Stocks;"
    set db = Currentdb
    Set rs = db.OpenRecordset(strSql)
    
    Do While Not rs.EOF
        Debug.Print rs!ISIN
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing

Duane
Hook'D on Access
MS Access MVP

 

[DaveLondon]

That has worked

Many thanks !

Dave.

 

[SkipVought]

BTW, You would be better served posting MS Access VBA questions in forum705.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}