Loop thru Columns

[foxhound]

Hello, I did a search and could not find an answer to the following. Any suggestions would be greatly appreciated.

How would I write code to select the first record of column 1, loop thru the first record of each additional column in a table then go to 2nd record and loop thru the columns again? This pattern would continue until rs.EOF ---something like this…

Set rs = CurrentDb.OpenRecordset("Select * from customers”)

With rs
.MoveFirst
End With

Do Until rs.EOF

Do Until last column
Do whatever I want here with column 1, record 1
Do whatever I want here with column 2, record 1
Do whatever I want here with column 3, record 1
Loop
rs.movenext

Loop

Thanks in advance for any help

 

[dk]

Hi,

Here is some basic code for looping through a recordset using ADO. This code will work fine in Access 2000 onwards.

Sub LoopRecords()

    Dim adoRS As ADODB.Recordset, adoField As ADODB.Field

    Set adoRS = New ADODB.Recordset

    adoRS.Open "SELECT * FROM Customers", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly


    While Not adoRS.EOF

        For Each adoField In adoRS.Fields

            'Do whatever with the field here e.g.
            Debug.Print adoField.Value

        Next adoField

        adoRS.MoveNext

    Wend

End Sub

 

[foxhound]

DK, that was exactly what I was looking for. Thank you!