Recordset Loop

[Excel_Kid1081]

Hello-

I am trying to create a VBA code that loops through each field in a table and for record cleans the data (string data with extra spacing).....

Here is the code I got so far....nothing too crazy but having trouble...

Sub Test()
Dim db As Database
Dim rs As DAO.Recordset
Dim fldLoop As DAO.Field
Dim icount As Long
Set rs = CurrentDb.OpenRecordset("tblTest1", dbOpenDynaset)
'Loop through all records in each column
For Each fldLoop In rs.Fields
        
    With rs
        Do Until rs.EOF
            .Edit
            !fldLoop = Clean(fldLoop)
            .Update
            rs.MoveNext
        Loop
    End With
Next fldLoop

rs.Close
Set fldLoop = Nothing
Set rs = Nothing
Set db = Nothing
End Sub

Any ideas are much appreciated!

Thanks!

EK

 

[Scooterbug]

Why not just use an update query?

UPDATE tblTest1 SET fldLoop = Clean(fldLoop);

 

[MarkK]

- I think you'll want your outer loop to the be the one that traverses records. Then, for each record traverse the fields collection.
- Your code grabs the first field, runs to the end of the recordset and for each ensuing field rst.eof is still true so the inner loop doesn't run again.
- And you would achieve a big speed improvement if you did use an update query, but you want to clean ALL the fields. Will you know what the fields are in advance?