Having to step back into VBA programming on a couple database after several years away. Rusty!!!
Dataset: data files from global business partners, some of whom send data using foreign characters.
Situation: I've done my research and programming and have built a procedure to scan the necessary fields character by character, do a lookup against an ASCII table to replace English equivalents, and then update the field with the new data.
I've referenced some old code on using .Edit and .Update in other dbs and that was working just fine. This is not translating so well.
I have stepped through a test table of 10 lines and the debug.print details the correct(ed) English character data. However, upon execution and completion of the code, and opening the table, the foreign characters still exist.
Like I said - rusty. This is undoubtedly something simple but I'm just not seeing it. Any help is appreciated!
Ross
Dataset: data files from global business partners, some of whom send data using foreign characters.
Situation: I've done my research and programming and have built a procedure to scan the necessary fields character by character, do a lookup against an ASCII table to replace English equivalents, and then update the field with the new data.
I've referenced some old code on using .Edit and .Update in other dbs and that was working just fine. This is not translating so well.
I have stepped through a test table of 10 lines and the debug.print details the correct(ed) English character data. However, upon execution and completion of the code, and opening the table, the foreign characters still exist.
Like I said - rusty. This is undoubtedly something simple but I'm just not seeing it. Any help is appreciated!
Ross
Code:
Public Sub TransForeign()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strOld As String
Dim strNew As String
Dim strCNChar As String
Dim strNewChar As String
Dim N As Integer
Dim X As Integer
Dim rCount As Integer
Dim NewChar As Variant
Set db = CurrentDb
strSQL = "SELECT [Bill_To_Name] FROM tblPOS_Import_Prelim Order by [Bill_To_Name];"
Set rs = db.OpenRecordset(strSQL)
rs.MoveLast
rCount = rs.RecordCount
rs.MoveFirst
For X = 1 To rCount
strOld = rs("Bill_To_Name")
N = Len(strOld)
For N = 1 To N
strCNChar = Mid(strOld, N, 1)
NewChar = DLookup("[Replace_ASCII]", "t_ASCII", "[ASCII_Code] = " & Asc(strCNChar))
If IsNull(NewChar) Then
strNew = strNew & strCNChar
Else
strNew = strNew & Chr(NewChar)
End If
Next N
rs.Edit
rs![Bill_to_Name] = strNew
rs.Update
strNew = ""
strOld = ""
rs.MoveNext
Next X
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit Sub
Exit_TransForeign:
Exit Sub
Err_TransForeign:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_TransForeign
End Sub