loop macro, edit records as it finds them (1 Viewer)

[sammers101]

I am trying to do a loop that alters records as it goes. My tables are setup as Orders->OrderDetails. I have a currency field named ProfitPrice within OrderDetails.

Currently I have this working code using macros:

pass parameter prmOrderID

SetLocalVar varOrderSubTotal = 0
For Each Record IN SELECT OrderDetails.OrderID, OrderDetails.ExtendedPrice FROM OrderDetails; 
       Where Condition = [OrderID]=[prmOrderID]
      Set Local Var varOrderSubTotal=[varOrderSubTotal]+[ExtendedPrice]

SetReturnVar retOrderSubTotal=[varOrderSubTotal]

I want to add to this, soonce it has found an matching order I want it to edit that record's ProfitPrice Field. I tried the following code but it says The identifier OrderDetails.ProfitPrice could not be found

pass parameter prmOrderID

SetLocalVar varOrderSubTotal = 0
For Each Record IN SELECT OrderDetails.OrderID, OrderDetails.ExtendedPrice FROM OrderDetails; 
       Where Condition = [OrderID]=[prmOrderID]
      Set Local Var varOrderSubTotal=[varOrderSubTotal]+[ExtendedPrice]
      [COLOR="Blue"]Edit Record
          SetField
                 OrderDetails.[ProfitPrice]=5.99[/COLOR]

SetReturnVar retOrderSubTotal=[varOrderSubTotal]

ProfitPrice will be a calculation using data from both the order table and the OrderDetails Table, but I was just testing it with 5.99

 

[liddlem]

Why not create an update query to do it without code?

 

[sammers101]

I figured it made more sense to build on what I already have no?
I think I should be able to do it with an update query, but is it possible to bypass the alert box asking if I am sure I want to update x records? I already have my own alert boxes.

 

[pbaldy]

I agree that an update query would be more efficient. Try this for the warnings:

http://www.baldyweb.com/SQLWarnings.htm