Excel VBA - Using DAO - How to loop throguh recordset

Experts, I am using the following code to pull the results of a SQL query into excel, and add the record set into a combo box. The code below is working, however it only adds the very first result- how can I make it loop through every result (row) returned by the query and add it to the combo box?

Much appreciated!
Sub Database_connect()
Dim dbEng As dbengine
Dim oRs As Recordset
Dim oRow As Range
Dim oWspc As Workspace
Dim oConn As Connection
Dim oQuery As QueryDef
 
strConnection = "ODBC;DATABASE=databasename;UID=userid;PWD=password;DSN=DSNname"
strSQL = "SELECT * FROM accounts where term like " & """VS%"""
Debug.Print strSQL
Set dbEng = CreateObject("DAO.DBEngine.35")
Set oWspc = dbEng.CreateWorkspace("ODBCWspc", "", "", dbUseODBC)
Set oConn = oWspc.OpenConnection("DSNname", , True, strConnection)
Set oQuery = oConn.CreateQueryDef("tmpFindAccount")
    
oQuery.Sql = strSQL
Set oRs = oQuery.OpenRecordset
If oRs.EOF = True Then
    MsgBox "No More Records"
Else
    Worksheets("Sheet2").ComboBox1.AddItem oRs.Fields("fieldname")
End If
 
oRs.Close
oQuery.Close
oConn.Close
oWspc.Close
Set oRs = Nothing
Set dbEng = Nothing
 
 
 
 
End Sub

Open in new window

LVL 12
0taconAsked:
Who is Participating?
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.

TimCotteeHead of Software ServicesCommented:
Try this loop that iterates through until there are no remaining records.
Set oRs = oQuery.OpenRecordset
If oRs.EOF = True Then
    MsgBox "No More Records"
Else
 With oRs
   Do While
    Worksheets("Sheet2").ComboBox1.AddItem .Fields("fieldname")
    .MoveNext
   Loop Until .Eof
End If

Open in new window

0
0taconAuthor Commented:
Thanks for the quick reply- it seems the do while loop needs a qualifier? (i.e. until there are no more records)
0
0taconAuthor Commented:
This seems to be working....though not sure if it's the correct implementation:
With oRs
   Do
    Worksheets("Sheet2").ComboBox1.AddItem .Fields("fieldname")
    .MoveNext
   Loop Until .EOF
End With

Open in new window

0
TimCotteeHead of Software ServicesCommented:
That's fine, me just typing too much.

You can either use:

Do While Not .Eof
 ..
Loop

Or

Do
 ..
Loop Until .Eof

They equate to the same thing.
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
0taconAuthor Commented:
Fantastic- you're a legend, much appreciated!
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.