word find and replace VBA or Update

[mole999]

I stole this from here, thought I could fathom how to adapt it for my use haha

Sub Entry_GotFocus()
'Private Sub ChangeUoM_Click()
Dim db As Database
Dim rs As Recordset
Dim strTABLE As String
Dim strFIELD As String
Dim strOLD As String
Dim strNEW As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Table2", dbOpenDynaset)
DoCmd.SetWarnings False

With rs
.MoveFirst
While Not .EOF
strTABLE = Table3 '!ERPTable
strFIELD = Entry '!ERPField
strOLD = !OldValue
strNEW = !NewValue
DoCmd.RunSQL "UPDATE " & strTABLE & " SET " & strTABLE & "." & _
strFIELD & " = '" & strNEW & "' WHERE " & strTABLE & "." & _
strFIELD & " = '" & strOLD & "'"


.MoveNext
Wend
End With
rs.Close
DoCmd.SetWarnings True
Set rs = Nothing
Set db = Nothing
'End Sub
End Sub

I get an error 3144 syntax error in this string DoCmd.RunSQL "UPDATE " & strTABLE & " SET " & strTABLE & "." & _
strFIELD & " = '" & strNEW & "' WHERE " & strTABLE & "." & _
strFIELD & " = '" & strOLD & "'"

I was trying to apply it to a file, with some unassuming names, the test table is called table3, the record entry is called 'entry', the newvalue and oldvalue reside in table 2.

newvalue and old value are text fields, entry is a memo field, there could be five or six values in each record/entry field and i want to replace them all.

2nd option would be on the neatest way to write an update query, and i will use 50 of those linked together to do what I need,

why am I doing this, well, some of my inputters will not turn caps lock off so the spell check is buggered, I can convert the case across so i get it in sentence case, but some words must be in upper case and auto correct won't fix as its not being typed, hence this route. It doesn't need to be pretty, just save me hours of work before turning it into a weekly report of some 40 pages, oh yes also at remote sites so can't control the input

Access 2003

 

[Rabbie]

Firstly you should change
strTABLE = Table3 '!ERPTable
strFIELD = Entry '!ERPField

to

strTABLE = "Table3" '!ERPTable
strFIELD = "Entry" '!ERPField

 

[mole999]

Rabbie, thanks for that, now it steps though and I can see each look up value is looked at, but NO changes are being written, any other thoughts

 

[Rabbie]

Change this code

DoCmd.RunSQL "UPDATE " & strTABLE & " SET " & strTABLE & "." & _
strFIELD & " = '" & strNEW & "' WHERE " & strTABLE & "." & _
strFIELD & " = '" & strOLD & "'"

to

msgbox "UPDATE " & strTABLE & " SET " & strTABLE & "." & _
strFIELD & " = '" & strNEW & "' WHERE " & strTABLE & "." & _
strFIELD & " = '" & strOLD & "'"
DoCmd.RunSQL "UPDATE " & strTABLE & " SET " & strTABLE & "." & _
strFIELD & " = '" & strNEW & "' WHERE " & strTABLE & "." & _
strFIELD & " = '" & strOLD & "'"

This will let you see the actual SQL you are going to run.

Try running this as a separate query from the query design tab to check it actually does what you want.

 

[mole999]

result is

UPDATE Table3 SET Table3.Entry = '1/2' WHERE Table3.Entry = '1 / 2'

which to me looks fine.


I feel that this would work fine on a single text entry and not in a long string of text

 

[Rabbie]

Did you actually run the the update query on its own as I asked you to?

Does table3.Entry contain just '1 / 2' or does it contain other text as well? If it des contain other text then you will need something like "WHERE Table3.Entry like '*1 / 2*'"

 

[mole999]

probably didn't run it quite as expected, was in the forms VBA and stepping through with F8 could see all the changes in the msgbox and Locals window. It was run without other events being triggered, its a purpose made form that only focuses on the one field that i want to modify.

It is hidden in a string, each value, or may not exist at all. I get so far, but don't know enough to be able to cleanly read and interpret.

so wild cards are the way to go

how best to apply them ?