VBA中使用函数替代模块执行DML操作及特殊字符移除问题

Got it, let's work through this step by step. First, I spotted a couple of tweaks needed for your fn_RemoveSpecialChars function—right now it's set to return a Boolean, but we need it to spit out the cleaned-up string. Once we fix that, we can use it directly in DML statements like UPDATE without writing a loop-heavy module.

Step 1: Fix the Custom Function #

Here's the corrected version of your function, adjusted to return the cleaned text instead of a boolean value:

Function fn_RemoveSpecialChars(strText As String) As String
    Dim output As String
    Dim c As String
    Dim i As Integer
    
    For i = 1 To Len(strText)
        c = Mid(strText, i, 1)
        ' Keep only letters (upper/lower) and numbers
        If (c >= "a" And c <= "z") Or (c >= "0" And c <= "9") Or (c >= "A" And c <= "Z") Then
            output = output & c
        End If
        ' Special characters are just skipped
    Next i
    
    fn_RemoveSpecialChars = output ' Return the cleaned string
End Function

Step 2: Use the Function in DML Operations #

You don't need a separate module to loop through records—Access lets you call VBA functions directly in SQL queries. Here are two reliable ways to run the update:

Option 1: Use DoCmd.RunSQL #

This is the simpler method for quick updates:

Sub CleanMyTableNames()
    On Error GoTo ErrorHandler
    
    ' Run the UPDATE query, using our function to clean the Names field
    DoCmd.RunSQL "UPDATE MyTable SET Names = fn_RemoveSpecialChars(Names);"
    
    MsgBox "All records cleaned successfully!", vbInformation
    Exit Sub
    
ErrorHandler:
    MsgBox "Oops, something went wrong: " & Err.Description, vbExclamation
End Sub

Option 2: Use DAO Execute (Recommended) #

For better error handling and control over the operation (like seeing how many records were updated), use DAO:

Sub CleanNamesWithDAO()
    Dim db As DAO.Database
    On Error GoTo ErrorHandler
    
    Set db = CurrentDb()
    
    ' Execute the UPDATE with error checking enabled
    db.Execute "UPDATE MyTable SET Names = fn_RemoveSpecialChars(Names);", dbFailOnError
    
    MsgBox "Successfully updated " & db.RecordsAffected & " records!", vbInformation
    
Cleanup:
    Set db = Nothing
    Exit Sub
    
ErrorHandler:
    MsgBox "Update failed: " & Err.Description, vbExclamation
    Resume Cleanup
End Sub

How This Works #

Access' SQL engine integrates seamlessly with VBA custom functions. When you run the UPDATE statement, Access automatically applies your fn_RemoveSpecialChars function to every value in the Names field, replacing the original value with the cleaned version—no manual record looping required.

内容的提问来源于stack exchange，提问作者natwar lal