Hi. I'm trying to write a generic function that can take in a table of any size and Trim and remove all carriage returns and line feeds from everything. The thing is while I know how to loop through every record and edit a specific record, I haven't done every field in every record before. Right now, I have code that looks like this:
It runs without errors, and I'm certainly seeing all the items displayed correctly in the immediate window. But when I go back into the table, the linebreaks and spaces are still there. My guess is that I'm not saving the changes. I've tried moving r.Edit and r.Update into the For Each fld loop, same result. So, I'm a bit confused about where I'm messing up. Help would be appreciated.
Function RemoveCrLf(strTable As String) Dim r As DAO.Recordset Set r = CurrentDb.OpenRecordset("SELECT * FROM [" & strTable & "]") If Not (r.BOF And r.EOF) Then r.MoveFirst Do Until r.EOF r.Edit For Each fld In r.Fields If Not IsNull(fld) And Not (fld = "") Then fld = Trim(Replace(Replace(fld, Chr(10), ""), Chr(13), "")) Debug.Print fld End If Next fld r.Update r.MoveNext Loop End If r.Close Set r = Nothing End Function