VBA Loop Problem

[yianni]

I am trying to loop through my table but when I run the program it starts on ID_fk 171 instead of 1 and I do not know why. Hope you can help!
It starts on 171 and goes thru 361 which is the end.






'--------------------------------




Private Sub cmdADOlooping_Click()
On Error GoTo ErrorHandler


Dim strSQL As String
Dim rs As New ADODB.Recordset


'we will be opening our table
strSQL = "SELECT UniqueCustomersWithPickups.ID_fk, UniqueCustomersWithPickups.PC, UniqueCustomersWithPickups.FirstVisit, UniqueCustomersWithPickups.ClientType FROM UniqueCustomersWithPickups ORDER BY UniqueCustomersWithPickups.ID_fk, UniqueCustomersWithPickups.PC;" 'define the SQL result that you want to loop

rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic


With rs


'Ensure recordset is populated
If Not .BOF And Not .EOF Then



'not necessary but good practice
.MoveLast
.MoveFirst
Debug.Print rs.Fields("ID_fk") & " " & rs.Fields("PC") ' placed here to see if I am on first record in my table I Am!



While (Not .EOF)
'print info from fields to the immediate window

Debug.Print rs.Fields("ID_fk") & " " & rs.Fields("PC")

.MoveNext
Wend

End If
.Close
End With


ExitSub:
Set rs = Nothing
Exit Sub
ErrorHandler:
Resume ExitSub
End Sub

[isladogs]

Not sure why you are creating a recordset just so you can print the output to the immediate window.
Why not just view this in the table or a query?

Anyway, try removing the PC field from the ORDER BY
Do you need the lock optimistic?

Have you considered using a DAO recordset instead?

[CJ_London]

ID_fk

fk implies the field is a family (or foreign) key which would not be the PK for the field (though it could start from 1) - are you sure you are ordering by the right field?

[yianni]

Yes it is the correct key. Suggestions did not work. Thanks anyway.

[June7]

Are you sure the query returns the desired records? Did you test it in Access query object?

Set a breakpoint on the first Debug. Step through the line so it executes. What is the value of ID_FK?

[John_G]

I am trying to loop through my table but when I run the program it starts on ID_fk 171 instead of 1 and I do not know why. Hope you can help!

The immediate pane has only a limited number of lines that it will display at any one time, and it is not very many (about 200). Additional lines are scrolled upward and disappear.
I think what is happenening is that the rows for ID_fk 1 - 170 are being written to the immediate pane, but are scrolling upward out of the display area, so you do not see them. You can see only the last 200 or so lines that were written.

[isladogs]

Cross posted with answers at https://www.access-programmers.co.uk...d.php?t=298732
Please follow forum guidelines. Say you have cross posted and provide the link yourself