Mass Update Query, Need Vb Help

Whew - :)

Glad that's working. Could have almost done the 100 queries by now huh? :p

I'm just a skip down the road if you ever need to hire any of this out - :)
 
Shoot me an email at flacrwdog at yahoo. If you are good with SQL stored procedures I probably have some work I can throw at ya
 
Hello Ken,
Your help a few months back was a big help. Would it be too much to ask for an upgrade? :D

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
 
on a slightly different tack, this is why it is better to think about things like this, and store a lookup value in a table

lets say you have a uom lookup table which stores the fact that

uom 1 is a "tonne"
uom 2 is a "50 kg bag"
uom 12 is a "thousand"

then you can easily change everything with one modification in the uom table. If this includes other characteristics such as scale factor

(eg with something like house bricks), you sell in units, but price per thousand eg you sell 50000 bricks priced at £100/thousand

then you can sell 50000 bricks, but because the uom stores a scale factor of 1000, then the system "knows" you actually sold 50 units at £100 each.

much more maintainable in a situation like yours.
 

Users who are viewing this thread

Back
Top Bottom