Loop Recordset (1 Viewer)

[mark curtis]

Hi folks,

I have created a recordset and what i want to do is check the field value "SiteRAG" to see if it matches some criteria and if so do some action where the field "SiteID" = the same as a label on a form.

Please see CAPS in middle of code:

Dim db As Object
Dim rs As Object
Dim intCount As Integer, intRecordCount As Integer, intID As Integer
Dim strSQL As String
Dim strRAG As Long
Dim fldItem As Field

strSQL = "SELECT tblSite.SiteID, tblSite.SiteRAG, tblSite.Active " _
& " FROM tblSite " _
& " WHERE (((tblSite.Active)=Yes));"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)


intRecordCount = 0
rs.MoveFirst

CHECK THE FIRST ROW FIELD SiteRAG & IF = TO CRITERIA THEN
DO SOMETHING BASED ON A LABEL MATCHING THE SiteID
ELSE
MOVE TO NEXT ROW

Loop
rs.Close
db.Close


Thanks for any help
Mark

 

[Banana]

You may want to explicitly dim your db and rst as DAO.Database and DAO.Recordset instead of Object; that will speed up your code.

As to looping,

With rst
   Do Until .EOF = True
       If MyCriteria = !TestField Then
           'Do something
       End If
       .MoveNext
  Loop
End With

 

[pbaldy]

Also, if part of your code is checking to see if a field matches a criteria, why not just add that to your WHERE clause so that you know every record in the recordset meets that criteria?