Loop Through Sales Order and Update Pricing, etc...

[Kaloyanides]

I need to do something that's beyond my capabilities. I wonder if anyone here is willing to assist?

I have a sales order entry form. Orders get keyed before we have the final sell price. Before the order is officially confirmed and printed, I'd like to click a button to update the order with the correct pricing. As it is now, the system grabs the current price that's in the system. Then, we purchase and enter the items we're selling into the system. An update is run and the sell prices get updated. The order, that was previously generated is populated with old pricing. All of this is done between 3am and 7am everyday. Time is of the essence.

This is all built around a sales order template I found or purchased online.

As each line product/field is entered/populated, the following code is run in the After Update.

Private Function UpdateLine()
On Error GoTo UpdateLine_error


Select Case Nz(PricingMethod, 0)
Case 0 'don't charge tax
Me![InvoiceItemNetAmount] = MyRound((Nz(Me![InvoiceItemUnitPrice], 0) * Nz(Me![InvoiceItemQuantity], 0)) * (1 - Nz(Me![InvoiceItemDiscount], 0)), 2)
Me![InvoiceItemTaxAmount] = Null
Me![InvoiceItemTotalAmount] = Nz(Me![InvoiceItemNetAmount], 0)
Case 1 'Prices EXCLUDE tax (calc net and add tax)
Me![InvoiceItemNetAmount] = MyRound((Nz(Me![InvoiceItemUnitPrice], 0) * Nz(Me![InvoiceItemQuantity], 0)) * (1 - Nz(Me![InvoiceItemDiscount], 0)), 2)
Me![InvoiceItemTaxAmount] = MyRound(Nz(Me![InvoiceItemNetAmount], 0) * Nz(Me![InvoiceItemTaxPercent], 0), 2)
If Me![InvoiceItemTaxAmount] = 0 Then
Me![InvoiceItemTaxAmount] = Null
End If
Me![InvoiceItemTotalAmount] = Nz(Me![InvoiceItemNetAmount], 0) + Nz(Me![InvoiceItemTaxAmount], 0)
Case 2 'Prices INCLUDE tax (calc gross and determine tax amount)
Me![InvoiceItemTotalAmount] = MyRound((Nz(Me![InvoiceItemUnitPrice], 0) * Nz(Me![InvoiceItemQuantity], 0)) * (1 - Nz(Me![InvoiceItemDiscount], 0)), 2)
Me![InvoiceItemNetAmount] = MyRound(Me![InvoiceItemTotalAmount] / (1 + Nz(Me![InvoiceItemTaxPercent], 0)), 2)
Me![InvoiceItemTaxAmount] = Nz(Me![InvoiceItemTotalAmount], 0) - Nz(Me![InvoiceItemNetAmount], 0)
If Me![InvoiceItemTaxAmount] = 0 Then
Me![InvoiceItemTaxAmount] = Null
End If
End Select


UpdateLine_Exit:
Exit Function


UpdateLine_error:
Select Case Err
Case Else
MsgBox Err & "-" & Error$, vbCritical + vbOKOnly, "Error in module UpdateLine"
Resume UpdateLine_Exit
End Select
End Function


THEN!


If the check box for Pieces (instead of the full case) is selected, the following is run to grab the piece price:

Private Sub ckUnit_Click()
If Me.ckUnit = True Then
Me![InvoiceItemUnitPrice] = Nz(Me![InvoiceItemDescription].Column(8), 0)
UpdateLine
End If
If Me.ckUnit = False Then
Me![InvoiceItemUnitPrice] = Nz(Me![InvoiceItemDescription].Column(9), 0)
UpdateLine
End If
End Sub


THEN!

The following is run if a discount has been applied to a specific item:

Private Sub InvoiceItemDiscount_AfterUpdate()
On Error GoTo InvoiceItemDiscount_AfterUpdate_error


Dim X As Double
X = Nz(Me![InvoiceItemDiscount], 0)
Do Until X <= 1
X = X / 100
Loop

If X = 0 Then
Me![InvoiceItemDiscount] = Null
Else
Me![InvoiceItemDiscount] = X
End If
UpdateLine

InvoiceItemDiscount_AfterUpdate_Exit:
Exit Sub


InvoiceItemDiscount_AfterUpdate_error:
Select Case Err
Case Else
MsgBox Err & "-" & Error$, vbCritical + vbOKOnly, "Error in module InvoiceItemDiscount_AfterUpdate"
Resume InvoiceItemDiscount_AfterUpdate_Exit
End Select
End Sub


LASTLY!

The following is run if tax is required:

Private Sub InvoiceItemTaxPercent_AfterUpdate()
On Error GoTo InvoiceItemTaxPercent_AfterUpdate_error


Dim X As Double
X = Nz(Me![InvoiceItemTaxPercent], 0)
Do Until X <= 1
X = X / 100
Loop

If X = 0 Then
Me![InvoiceItemTaxPercent] = Null
Else
Me![InvoiceItemTaxPercent] = X
End If
UpdateLine

InvoiceItemTaxPercent_AfterUpdate_Exit:
Exit Sub


InvoiceItemTaxPercent_AfterUpdate_error:
Select Case Err
Case Else
MsgBox Err & "-" & Error$, vbCritical + vbOKOnly, "Error in module InvoiceItemTaxPercent_AfterUpdate"
Resume InvoiceItemTaxPercent_AfterUpdate_Exit
End Select
End Sub



Somehow I need to update all of this. Not just for one row but every row in the order.

Like I said, beyond me!

Thanks for taking the time to read if you made it is far....

Any help would be hugely appreciated!!!

[aytee111]

Thanks for all the information. However, it has left me unsure of what the one thing is that you are trying to do (and please enclose code using the # icon, it makes it easier to read).

You have purchased items at a certain price and would like the orders to be updated with that price automatically, is that it? Which would include all the code you provided.