Hello Ken,
Your help a few months back was a big help. Would it be too much to ask for an upgrade?
To refresh your memory, we put together a module that does a mass find/replace by referencing a parameter table that houses the Tablename, fieldname, old value to be replaced and the new value.
The approach works when the old value in the parameter table matches exactly what the module finds in the tables/fields/values it searches.
It would be great if this code could be enhanced to search WITHIN the table/field/old values and replace it with the new value.
For example, if the parameter table has a record of table=INVENTORY, Field=ITEM, old value=DARKBLUE, new value=NAVY, the module would look at every record in the Inventory.Item and search within the string/contents (dont want to limit it to just text fields, the find/replace could be numeric) of the field to find the old value and replace with the new value, i.e. a record in Inventory.Item of DARKBLUE JACKET would be replaced to be NAVY JACKET.
Below is the old code:
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("ChangesTable", dbOpenDynaset)
DoCmd.SetWarnings False
With rs
.MoveFirst
While Not .EOF
strTABLE = !ERPTable
strFIELD = !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
If this is an easy thing I'd be most appreciative!
Regards,
Michael