Calculating order totals

[Guest]

I posted this in the Query section but I think it really should be here. So
here's the short version. I track photography orders. Photos has one record
per order, PhotoList has one record per picture order (based on pic # and
size). Price of each pic is based on the TOTAL number ordered of that size
(we give a discount for 6 or more photos ordered of the same size but NOT
necessarily the same photo). I'd like the form to calculate the price for
me. Here's a run through:

Order #1, pic # 3345, size 5x7, quantity 2 (price would be $7 ea)
Order #1, pic # 3346, size 5x7, quantity 2 (price still $7 ea)
Order #1, pic #3350, size 5x7, quantity 3 (price now changes to $5 ea for
ALL the 5x7s ordered - the ones above and any more on this order)

I'm thinking the easiest way would be to have a button on the form that
calculates the line item prices and the order total after I enter everything?

 

[Guest]

I posted this in the Query section but I think it really should be here. So
here's the short version. I track photography orders. Photos has one record
per order, PhotoList has one record per picture order (based on pic # and
size). Price of each pic is based on the TOTAL number ordered of that size
(we give a discount for 6 or more photos ordered of the same size but NOT
necessarily the same photo). I'd like the form to calculate the price for
me. Here's a run through:

Order #1, pic # 3345, size 5x7, quantity 2 (price would be $7 ea)
Order #1, pic # 3346, size 5x7, quantity 2 (price still $7 ea)
Order #1, pic #3350, size 5x7, quantity 3 (price now changes to $5 ea for
ALL the 5x7s ordered - the ones above and any more on this order)

I'm thinking the easiest way would be to have a button on the form that
calculates the line item prices and the order total after I enter everything?

I think you probably need a table that lists the price breaks. Something
like this:

Size Qty PricePer
5x7 1 7
5x7 5 5
5x7 10 4
8x10 1 9
8x10 5 7

Then you need a few lines of code to do your calculation. Put the following
code in your form's AfterUpdate event:

Private Sub Form_AfterUpdate()
Dim strSQL As String
' Update the table with new prices.
strSQL = "UPDATE Orders SET PriceEach = " & GetPriceEach(Trim(Me.size),
Me.Order) & " WHERE Orders.Order = " & Me.Order
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
Me.Requery
End Sub

'and then the following function anywhere in your form's module:

Private Function GetPriceEach(ByVal size As String, ByVal Order As Integer)
As Integer
Dim intQty As Integer
Dim intPrice As Integer
Dim intPriceBreak As Integer
' Lookup the total quantity of pictures on this order woth this size.
intQty = DSum("qty", "orders", "size = '" & size & "' and [Order] = " &
Order)
' Lookup the price break qty for this size/qty
intPriceBreak = DMax("qty", "pricebreaks", "size = '" & size & "' and
qty <=" & intQty)
' Lookup the price.
intPrice = DLookup("priceeach", "pricebreaks", "qty = " & intPriceBreak
& " and size = '" & size & "'")
GetPriceEach = intPrice
End Function

You'll obviously need to substitue your own control, table, and field names
with the ones I used.

Barry

 

[Guest]

I posted this in the Query section but I think it really should be here. So
here's the short version. I track photography orders. Photos has one record
per order, PhotoList has one record per picture order (based on pic # and
size). Price of each pic is based on the TOTAL number ordered of that size
(we give a discount for 6 or more photos ordered of the same size but NOT
necessarily the same photo). I'd like the form to calculate the price for
me. Here's a run through:

Order #1, pic # 3345, size 5x7, quantity 2 (price would be $7 ea)
Order #1, pic # 3346, size 5x7, quantity 2 (price still $7 ea)
Order #1, pic #3350, size 5x7, quantity 3 (price now changes to $5 ea for
ALL the 5x7s ordered - the ones above and any more on this order)

I'm thinking the easiest way would be to have a button on the form that
calculates the line item prices and the order total after I enter everything?

I think you probably need a table that lists the price breaks. Something
like this:

Size Qty PricePer
5x7 1 7
5x7 5 5
5x7 10 4
8x10 1 9
8x10 5 7

Then you need a few lines of code to do your calculation. Put the following
code in your form's AfterUpdate event:

Private Sub Form_AfterUpdate()
Dim strSQL As String
' Update the table with new prices.
strSQL = "UPDATE Orders SET PriceEach = " & GetPriceEach(Trim(Me.size),
Me.Order) & " WHERE Orders.Order = " & Me.Order
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
Me.Requery
End Sub

'and then the following function anywhere in your form's module:

Private Function GetPriceEach(ByVal size As String, ByVal Order As Integer)
As Integer
Dim intQty As Integer
Dim intPrice As Integer
Dim intPriceBreak As Integer
' Lookup the total quantity of pictures on this order woth this size.
intQty = DSum("qty", "orders", "size = '" & size & "' and [Order] = " &
Order)
' Lookup the price break qty for this size/qty
intPriceBreak = DMax("qty", "pricebreaks", "size = '" & size & "' and
qty <=" & intQty)
' Lookup the price.
intPrice = DLookup("priceeach", "pricebreaks", "qty = " & intPriceBreak
& " and size = '" & size & "'")
GetPriceEach = intPrice
End Function

You'll obviously need to substitue your own control, table, and field names
with the ones I used.

Barry

 

[Guest]

Thanks, Barry! You pointed me in the right direction (didn't think about the
AfterUpdate event - duh!) and now it works like a charm!

Lauri