Searching every row

[surferxx]

How do you write a procedure that searches every row in a table from top to bottom for a criteria like 'If Field5 = 'xxx' then do the following'. Do I have to use recordsets. What exactly are recordsets. Im finding that concept difficult to understand. Can someone pls help
Thanks

 

[jwhite]

Glad I'm not the only one!

After some perserverance I'm starting to get the hang of it myself. I refer OFTEN to Sybex Access 2002 Dev/Ent handbooks, amongst others (check Amazon for a good deal). Below is a sample routine that should work - just add your actions to take.

This is the manual way to do it, and could be the only way. However, Queries can handle an aweful lot if designed properly. For example, you can search for the specific value ('xxx') in a field, and replace that or other fields with updated value for each record match.

Hope that helps.

Public Sub DoSomething()

    On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim i As Integer

    'Define using current database, and open table
    Set db = CurrentDb
    Set rst = db.OpenRecordset("yourtablename", dbOpenSnapshot)
    
    'Make sure table has records, and set to first record.
    With rst
        If .RecordCount > 0 Then
            .MoveLast
            .MoveFirst
        End If
    End With
    
    'Loop through each record
    With rst
        For i = 1 To rst.RecordCount   ' 1 to Count of records
            If .Field5 = "xxx" Then
                'do whatever
            End If
            'Move to the next record
            .MoveNext
        Next
    End With
     
ExitHere:
    'Clear variables used from memory
    Set rst = Nothing
    Set db = Nothing
    i = 0
    Exit Sub
    
ErrHandler:
    'Always include error trapping.  this is the bare minimum...
    MsgBox Err.Number & ": " & Err.Description
    Resume ExitHere

End Sub

 

[gemma-the-husky]

why can't you set the test value as a criteria in a query - then you know your query result recordset ONLY has matching rows

I think in general Access bulk queries are both more effiicient than using rst's and are not likely to provide a possibility for coding errors!

Having said that there are some times when you do need to use a recordset to read the items

 

[surferxx]

Awesome fdcusa!...that was perfect...thanks loads...I am going to chk out those books u suggested...looks like very clear information!!