Help with loop (I think)

[gbeegle]

Have a form that is basically an order form for candy. When a candy is selected it will post it with price and then a new row is automatically added for another selection if needed. And does this each time a candy is added to the order.

Customer discounts are calculated in each line item of the order form. Not the Grand Total.

So now if we have an order created and the customer was set to get say 10% off, and then we want to retrieve that order to modify for whatever reason, say like change the discount amount....... When we change the discount rate field it only applies the change to the current candy line in the order form not all the lines........I was thinking I need a loop.........am I way off and can someone offer any assitance?

 

[the_net_2.0]

so what do you want to do? an ideal invoice form like that will have line items, and only one box, outside of the form that displays the line items, to display the discount if there is one.

a discount for an invoice should not be in every record that correspond to line items. discount rates should be located in one table, ideally without any relationships, and looked up when needed.

why do you think you need a loop? what's the goal?

 

[gbeegle]

Thank you greatly for replying, The discount is located in the Customer Table. For each customer has different discount rates. The discount is referenced in the line items because they want to show that for each item.

My goal is that when someone should open up an existing order to change the discount amount (probably because the order is going to have more items added to it), when changing that field it ONLY changes one line item in the invoice (the currently selected line item) not all of the line items.

I wanted to know if there is a way that by changing the discount rate in the existing order can all the previous line items entered also change?

 

[boblarson]

You could call an update query and limit it to that order ID. But I would have a box or something that you call to edit the discount and then apply it to all of the items with that order id.

And I stored individual discounts with the Point of Sale system I created for St. Vincent's Hospital. I have a sample copy somewhere in one of the threads here on the site. I believe I had it so you could apply individual discounts to individual items, do it on the entire order, or a combination of both.

 

[gbeegle]

Bob,

Thanks for replying. I am sure I could learn greatly from your St. Vincent example. I understand what you mean by an update query working.

Am I wrong in thinking that if I used an "after update" event on the discount rate field (which is located outside the line items area) where it would loop through each line item for that order and make it to the changed discount rate for that particular order?

 

[boblarson]

Found the thread I had it on. It is here.

Also, you could use the after update event of the discount rate and run an update query:

Dim strSQL As String
 
strSQL = "Update [OrderLineItemsTable] Set [DiscountRateFieldNameHere] = " & Me.TheControlOnTheformNameHerek & " WHERE [OrderID]= " & Me!OrderID
CurrentDb.Execute strSQL, dbFailOnError

 

[gbeegle]

Bob,

THANK YOU SO MUCH!!! it worked very nicely. Thank you for your expertise and helping me find a solution. I am very grateful.

 

[boblarson]

Glad we could assist.

 

[SpentGeezer]

mmmmmm CANDY!!!.....