Applying a Function to an entire table

[cukarisol]

Hi Guys

I wrote a function (see below) that I use to clean up a text field in a query and strip away unwanted syntax. If I'd like to apply this function to multiple fields in a table/query, how best to do this (without having to reference every text field in a table/query)?

Thanks

******************************************************
Public Function TrmChar(ReplaceChar As String) As String

Dim Originals As Variant, Replacements As Variant
Dim i As Long


Originals = Array(Chr(169), Chr(191), Chr(218), Chr(63), Chr(47), Chr(59), "")
Replacements = Array("", "", "", "", "", "", "")
TrmChar = ReplaceChar
For i = 0 To 6
TrmChar = Replace(TrmChar, Originals(i), Replacements(i), Compare:=vbTextCompare)
Next


End Function

[HansV]

In an update query, you must include every field that you want to update explicitly, you can't use * to specify all fields.
If you have lots of fields to be updated, or if you have to generate such queries frequently, you could use VBA to generate the update query:

Code: Select all

Sub GenerateUpdateQuery(TableName As String)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim qdf As DAO.QueryDef
    Dim fld As DAO.Field
    Dim strQueryName As String
    Dim strSQL As String
    'On Error GoTo ErrHandler
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(TableName)
    For Each fld In tdf.Fields
        If fld.Type = dbText Then
            strSQL = strSQL & ", [" & fld.Name & "]=" & _
                "Replace(Replace(Replace(Replace(Replace(Replace(" & _
                "[" & fld.Name & "],Chr(169),"""")," & _
                "[" & fld.Name & "],Chr(191),"""")," & _
                "[" & fld.Name & "],Chr(218),"""")," & _
                "[" & fld.Name & "],Chr(63),"""")," & _
                "[" & fld.Name & "],Chr(47),"""")," & _
                "[" & fld.Name & "],Chr(59),"""")"
        End If
    Next fld
    If strSQL = "" Then
        MsgBox "Nothing to update!", vbInformation
        GoTo ExitHandler
    Else
        strSQL = "UPDATE [" & TableName & "] SET" & _
            Mid(strSQL, 2)
        strQueryName = "qryUpdate_" & TableName
        On Error Resume Next
        Set qdf = dbs.QueryDefs(strQueryName)
        On Error GoTo ErrHandler
        On Error GoTo 0
        If qdf Is Nothing Then
            Set qdf = dbs.CreateQueryDef(strQueryName, strSQL)
            dbs.QueryDefs.Refresh
        Else
            qdf.Sql = strSQL
        End If
    End If
ExitHandler:
    Set fld = Nothing
    Set qdf = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

Example of use:

GenerateUpdateQuery "tblEmployees"

[cukarisol]

Thanks Hans

Will put to good use