VBA Module to Find/Replace

[babaroga]

[FONT=&quot]Hello Everybody,

I need some help to solve my big headache, I have table with a lot of records and every record contain unique number. However couples hundred of records have new number and I need to find and replace. I can do that one by one, but this is waste of time specially when I have those numbers in multiple tables.

I have one table tbl_status with two column:

OLD_num and NEW_num

Something like this:
P2345 P3445
P1000 P3446
P123 P3447

Than I have table with data tbl_Articles


Now I will like to make VBA function to loop thru table tbl_Article and if find any of number listed in table tbl_status under field OLD_num to replace with NEW_num

I hope I did explain well, English is my second language. [/FONT]

 

[smig]

I think something is wrong with you db design if at some point you need to change the unique key field for records.

if your tables are linked in the relationships window make sure to set the link to Update hirarchical. this will cause a change in the main table key to be reflected in the linked tables.
now you can run an update query on the main table and update the key to New_Num

 

[babaroga]

[FONT=&quot]tbl_status is just temporary table, I have excel list with old numbers and new numbers, so I copy that list in this temp table.


[/FONT]

 

[babaroga]

it look like this working:

UPDATE tbl_Articles INNER JOIN tbl_Status ON tbl_Articles.Number = tbl_Status.OLD_num SET tbl_Articles.[Number] = tbl_Status.New_num;

 

[smig]

if you need to run this code more then once it make sense to write it as code.
if you only need this as one time update it's easier to use the query grid