Run an update query in a loop 2

[soulprogrammer]

I have a table of "products" where each product belongs to one of 5 categories. The table looks something like this:

Desc. Attr1 Attr2 Category
Prod1 ... ... Cat1
Prod2 ... ... Cat1
Prod3 ... ... Cat1
.
.
.
Prod15 ... ... Cat3

I want to change Attr1 depending on the category. I'm looking to write a code that automates the above process. Something like:

For Each Category In Table "Products"
Select Case Category
Case Cat1
Update myquery...
.
.
.
End Select
Next

Can someone help me with the appropriate object for the For Each... Next statement, or a more efficient method of doing this?

 

[PHV]

Something like this ?
UPDATE products
SET Attr1=Switch(Category="Cat1","Value-1",...,Category="Cat5","Value-5",True,Attr1);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Crowley16]

phv's solution is the one you should use,
if you want to do it in code and not a query, then wrap the whole thing in a docmd.runsql("sqlString")

however if you want to do this with code only, then the object u need to use as the index in the for each loop needs to be a variant
And you'd need to put the categories into an array, not just call from table.
And then you'd need to open the table into a recordset, and change them individually...