Looping through a table's recordsets

[VegaLA]

Hi all,
I am tring to loop through a recordset based on a query and
each loop keeps showing the same value. The query itself has 5 records, but my loop keeps displaying the first record (5 times however). Can anyone please point out where I am going wrong in my code.

Thanks in advance,
Mitch...

Dim dbsDBase As DAO.Database
Dim rstServicers As DAO.Recordset
Dim strServicer As String
Set dbsDBase = CurrentDb
Set rstServicers = dbs.OpenRecordset("qryCurrServs")

With rstServicers

Do While Not .EOF

strServicer = DLookup("servicer", "qryCurrServs")
msgbox strServicer
.MoveNext
Loop

.Close
End With

 

[DJkarl]

Hi all,
I am tring to loop through a recordset based on a query and
each loop keeps showing the same value. The query itself has 5 records, but my loop keeps displaying the first record (5 times however). Can anyone please point out where I am going wrong in my code.

Thanks in advance,
Mitch...

Dim dbsDBase As DAO.Database
Dim rstServicers As DAO.Recordset
Dim strServicer As String
Set dbsDBase = CurrentDb
Set rstServicers = dbs.OpenRecordset("qryCurrServs")

With rstServicers

Do While Not .EOF

strServicer = DLookup("servicer", "qryCurrServs")
msgbox strServicer
.MoveNext
Loop

.Close
End With

The issue is with this line

strServicer = DLookup("servicer", "qryCurrServs")

You have no criteria in your Dlookup so it is returning the first record in the "servicer" field everytime.

You should just reference the field in the recordset.

strServicer = ![servicer]

 

[Dreamweaver]

The issue is with this line


You have no criteria in your Dlookup so it is returning the first record in the "servicer" field everytime.

You should just reference the field in the recordset.

strServicer = ![servicer]

DLookup("servicer", "qryCurrServs","[Field]=" & Limit By) Thats for an number
DLookup("servicer", "qryCurrServs","[Field]='" & Limit By & "'") For Text

The Limit by should come from you loop recordset asay servicer ID???

MICK

 

[MarkK]

All you really need is this...

Dim rst as DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qryCurrServs")
With rst
  Do While Not .EOF
    msgbox !Servicer
    .MoveNext
  Loop
  .Close
End With

You don't need both a recordset and a dLookup() on the same query.

 

[Dreamweaver]

All you really need is this...

Dim rst as DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qryCurrServs")
With rst
  Do While Not .EOF
    msgbox !Servicer
    .MoveNext
  Loop
  .Close
End With

You don't need both a recordset and a dLookup() on the same query.

Hadn't noticed they were the same LOL

 

[VegaLA]

So many options !!

Thanks Guys, I now have it working a treat !!

Mitch........