Recordset loop & change values (kinda...)

[opopanax666]

Hi everybody,



I have a table "tblProduced" which stores our produced goods (duh), along with a Product_ID got from "tblProducts" (which contains about 120 products). An "illuminated" spirit within the company decided to change this product list. To cut a long story short, I'm stuck having to change all the old Product_ID's in "tblProduced" to the new ones (trust me, I looked at it from all angles, and this is the only way)...

I have already set up a table ("tblConversion") listing the old ID's and respective new ID's.

I can't use an update query (120 values to be checked), but I was thinking I could use a recordset loop that checks the conversion table for every record in "tblProduced", and updates that value with the corresponding new value out of "tblConversion".

The problem is that I'm no good at recordsets, and the problem is too specific to find any help on the net. So I turn to you guys and girls for any help setting this up...

TIA!

[jzwp11]

Please see my next post

[jzwp11]

Is Product_ID in tblProducts an autonumber key field? I assume that it is not. Is Product_ID the primary key of tblProducts? I assume that it is. You will have to change the Product_ID in both tblProducts and tblProduced and any other table to which tblProducts is joined. I'm not sure why you say that you cannot use an update query. In either case, you will have to temporarily remove all of the joins to or from Product_ID of tblProducts and any other tables that reference Product_ID. Once you do that, you should be able to use your conversion table to make the updates. Once done, you will have to re-establish your relationships. First, I would make a backup copy of the database!


The UPDATE query for tblProducts would look like this:

UPDATE tblProducts INNER JOIN tblConversion ON tblConversion.OldProductID = tblProducts.Product_ID SET tblProducts.Product_ID = [tblConversion].[NewProductID];

And for tblProduced the UPDATE query would look like this:

UPDATE tblProduced INNER JOIN tblConversion ON tblConversion.OldProductID = tblProduced.Product_ID SET tblProduced.Product_ID = [tblConversion].[NewProductID];

[opopanax666]

Thank you so much for your solution. I don't use update queries that much, and I hadn't considered doing it like this. Thank you again!

[jzwp11]

You're welcome.