Trim/Remove line breaks from every field in every record (1 Viewer)

NeutronFlux

Registered User
Joined
Aug 20, 2012
Messages
78
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:
PHP:
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
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.
 

mdlueck

Sr. Application Developer
Joined
Jun 23, 2011
Messages
2,625
You are never handing the cleaned up string back to the daoRS object to have it update the actual table. You are merely setting VBA variable fld, which I will point out you never declared that one. Once you receive cleaned up fld variable, you need to use the daoRS object to place the new value back into the table.
 

DJkarl

Registered User
Joined
Mar 16, 2007
Messages
1,028
I would do it a bit differently, rather than go through each record, I would build a dynamic SQL string and execute that, on large number of records there will be a major performance benefit.
Code:
Function RemoveCrLf(strTable As String)
    Dim fld As DAO.Field, tbl As DAO.TableDef, db As DAO.Database
    Dim sql() As String
    Dim fCt As Integer
    
    fCt = 1
    
    Set db = CurrentDb
    Set tbl = db.TableDefs(strTable)
    
    ReDim sql(1 To tbl.Fields.Count)
    
    For Each fld In tbl.Fields
        sql(fCt) = "UPDATE [" & strTable & "] SET [" & fld.Name & "] = Trim(Replace(Replace([" & fld.Name & "], Chr(10), ''), Chr(13), ''))"
        fCt = fCt + 1
    Next fld
    
    For fCt = 1 To UBound(sql)
        db.Execute sql(fCt)
    Next
    
    Set tbl = Nothing
    Set db = Nothing
    
End Function
 

NeutronFlux

Registered User
Joined
Aug 20, 2012
Messages
78
Thanks for the responses.

@mdlueck
For passing the fld variable into the recordset, would this be the right code?
Code:
r(fld.name) = fld
@DJkarl That's interesting. I might use that approach once I get my method working. I want to take time to understand fully your code before putting it into use. Well, it's mostly just TableDef that I've never used before.

Anyway, I seem to have encountered another problem. Primary keys can't be edited, so an error is thrown. My current thoughts are either to use something like On Error Resume Next or finding some way to detect a primary key and skip it. Any advice?
 

mdlueck

Sr. Application Developer
Joined
Jun 23, 2011
Messages
2,625
@mdlueck
For passing the fld variable into the recordset, would this be the right code?
Coding DAO.Recordset example found here:
http://msdn.microsoft.com/en-us/library/office/bb243789(v=office.12).aspx

Anyway, I seem to have encountered another problem. Primary keys can't be edited, so an error is thrown. ... Any advice?
Are you not using Surrogate key in the application design? Why would you need to edit the primary key?
 

NeutronFlux

Registered User
Joined
Aug 20, 2012
Messages
78
Yup, I know about that article. The reason for my confusion was partly due to the fact that I'm accessing field names through variables rather than discrete names like what is shown in the article. Anyway, I tried out my code and it works, so that's good.

Are you not using Surrogate key in the application design? Why would you need to edit the primary key?
I think I'm using it. Surrogate key is a type of primary key, right? Anyway, I don't want to edit it, but the code by default goes through and tries to edit every field including the primary key, which is not allowed. For now, I'm using a Resume Next when that error is called to skip it, which works. I'm not sure if that's the proper way of doing things or whether that might introduce unexpected/unwanted results.
 

mdlueck

Sr. Application Developer
Joined
Jun 23, 2011
Messages
2,625
I think I'm using it. Surrogate key is a type of primary key, right?
That was a clickable link... click to see more.

Anyway, I don't want to edit it, but the code by default goes through and tries to edit every field including the primary key, which is not allowed.
Do you have a consistent naming standard for the ID field? id perhaps? If so, simply skip over that field if it happens to be the name the code has landed on.
 

JANR

Registered User
Joined
Jan 21, 2009
Messages
1,623
Anyway, I seem to have encountered another problem. Primary keys can't be edited, so an error is thrown. My current thoughts are either to use something like On Error Resume Next or finding some way to detect a primary key and skip it. Any advice?
Is it not easier to test fld.Type properties and test for text field since that's where Cr and Lf would be

Code:
If fld.Type = 10 Then
           'do replacement
End If
perhaps also in memo fields if that's required

fld.Type = 12

Just a thought

Edit:
In the use of DJKarls code example which uses tableDef collection

JR
 
Last edited:

mdlueck

Sr. Application Developer
Joined
Jun 23, 2011
Messages
2,625
Is it not easier to test fld.Type properties and test for text field since that's where Cr and Lf would be

Code:
If fld.Type = 10 Then
           'do replacement
End If
perhaps also in memo fields if that's required

fld.Type = 12
hhhmm, neat trick.

Furtehr you could use the DAO Constants in place of the hard coded numeric values:

A2007 Help File Path
Access 2007 Developer Reference > Microsoft Data Access Objects (DAO) Reference > DAO Reference > Enumerations

DataTypeEnum Enumeration

Has what looks like the complete list of DAO datatypes and their constant name/value.

Code:
If fld.Type = [B]dbText[/B] Then
           'do replacement
End If
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom