Update each record shipping total based on Order Value

M

mem

I am trying to figure out the best way to do this and I am coming up blank.
Here is what I need to do. I have an order form with a subform with the
order details. I want to figure out the shipping cost per line based on the
total retail value. I have code in place that does this but it needs to
update based on the total order retail value. If the order retail value is
less then $50 then the shipping is automatically $6.99, but if the order
retail value is more then $50 then shipping is 10%. I came up with code to
figure this out line by line after the quantity is updated. I want it to
recalculate each record either after update of each record or on exit of the
subform. How is the best way to do this?

I call my private sub shipttl after qty update.

Here is my code.

Private Sub ShipTtl()
If RetailTtl < 50 And Me.OrderTotal = 0 And Me.RetailTotal > 0 Then
Me.ShipTotal = (Me.RetailTotal / RetailCostTotal) * 6.99
If Me.RetailTax > 0 Then
Me.ShipTax = Me.ShipTotal * 0.06
Else
Me.ShipTax = 0
End If
ElseIf RetailTtl < 50 And Me.OrderTotal = 0 And Me.RetailTotal <= 0 Then
' Me.ShipTotal = -1 * ((Me.CostTotal / RetailCostTotal) * 6.99)
Me.ShipTax = Me.ShipTotal * 0.06
ElseIf RetailTtl < 50 And Me.RetailTotal >= Me.OrderTotal Then
If RetailCostTotal = 0 Then
'do nothing
Else
Me.ShipTotal = ((Me.RetailTotal / RetailCostTotal) * 6.99)
End If
If Me.RetailTax > 0 Then
Me.ShipTax = Me.ShipTotal * 0.06
Else
Me.ShipTax = 0
End If
ElseIf RetailTtl < 50 And Me.RetailTotal < Me.OrderTotal Then
Me.ShipTotal = (Me.RetailTotal / RetailCostTotal) * 6.99
If Me.RetailTax > 0 Then
Me.ShipTax = Me.ShipTotal * 0.06
Else
Me.ShipTax = 0
End If
ElseIf RetailTtl >= 50 And Me.RetailTotal >= Me.OrderTotal Then
Me.ShipTotal = (Me.RetailTotal) * 0.1
Me.ShipTax = (Me.RetailTax * 0.1) * 0.06
ElseIf RetailTtl >= 50 And Me.RetailTotal < Me.OrderTotal Then
Me.ShipTotal = (Me.OrderTotal) * 0.1
Me.ShipTax = (Me.RetailTax * 0.1) * 0.06
End If
End Sub

Public Function CountChecks()
RetailTtl = Nz(DSum("[retailTotal]", "[test]", "[OrderID] = '" &
Me![OrderID] & "'" & " AND [RetailTotal]>0"))
CostTtl = Nz(DSum("[CostTotal]", "[test]", "[OrderID] = '" &
Me![OrderID] & "'" & " AND [RetailTotal]=0"))
RetailCostTotal = -1 * (CostTtl) + RetailTtl
End Function
 
M

mem

10% of the retail line item only if the grand order total is greater then or
equal to $50.

Evi said:
10% of what?

Evi

mem said:
I am trying to figure out the best way to do this and I am coming up blank.
Here is what I need to do. I have an order form with a subform with the
order details. I want to figure out the shipping cost per line based on the
total retail value. I have code in place that does this but it needs to
update based on the total order retail value. If the order retail value is
less then $50 then the shipping is automatically $6.99, but if the order
retail value is more then $50 then shipping is 10%. I came up with code to
figure this out line by line after the quantity is updated. I want it to
recalculate each record either after update of each record or on exit of the
subform. How is the best way to do this?

I call my private sub shipttl after qty update.

Here is my code.

Private Sub ShipTtl()
If RetailTtl < 50 And Me.OrderTotal = 0 And Me.RetailTotal > 0 Then
Me.ShipTotal = (Me.RetailTotal / RetailCostTotal) * 6.99
If Me.RetailTax > 0 Then
Me.ShipTax = Me.ShipTotal * 0.06
Else
Me.ShipTax = 0
End If
ElseIf RetailTtl < 50 And Me.OrderTotal = 0 And Me.RetailTotal <= 0 Then
' Me.ShipTotal = -1 * ((Me.CostTotal / RetailCostTotal) * 6.99)
Me.ShipTax = Me.ShipTotal * 0.06
ElseIf RetailTtl < 50 And Me.RetailTotal >= Me.OrderTotal Then
If RetailCostTotal = 0 Then
'do nothing
Else
Me.ShipTotal = ((Me.RetailTotal / RetailCostTotal) * 6.99)
End If
If Me.RetailTax > 0 Then
Me.ShipTax = Me.ShipTotal * 0.06
Else
Me.ShipTax = 0
End If
ElseIf RetailTtl < 50 And Me.RetailTotal < Me.OrderTotal Then
Me.ShipTotal = (Me.RetailTotal / RetailCostTotal) * 6.99
If Me.RetailTax > 0 Then
Me.ShipTax = Me.ShipTotal * 0.06
Else
Me.ShipTax = 0
End If
ElseIf RetailTtl >= 50 And Me.RetailTotal >= Me.OrderTotal Then
Me.ShipTotal = (Me.RetailTotal) * 0.1
Me.ShipTax = (Me.RetailTax * 0.1) * 0.06
ElseIf RetailTtl >= 50 And Me.RetailTotal < Me.OrderTotal Then
Me.ShipTotal = (Me.OrderTotal) * 0.1
Me.ShipTax = (Me.RetailTax * 0.1) * 0.06
End If
End Sub

Public Function CountChecks()
RetailTtl = Nz(DSum("[retailTotal]", "[test]", "[OrderID] = '" &
Me![OrderID] & "'" & " AND [RetailTotal]>0"))
CostTtl = Nz(DSum("[CostTotal]", "[test]", "[OrderID] = '" &
Me![OrderID] & "'" & " AND [RetailTotal]=0"))
RetailCostTotal = -1 * (CostTtl) + RetailTtl
End Function
Click to expand...
Click to expand...
 
M

mem

Thanks I was able to figure it out. Here is what I did:

Msg = MsgBox("Do you want to update the totals?", vbYesNo)
If Msg = vbYes Then
If Me.frm_OrderDetail_Sub.Form.Dirty Then
Me.frm_OrderDetail_Sub.Form.Dirty = False
End If
Set R = Me.frm_OrderDetail_Sub.Form.Recordset
If R.RecordCount > 0 Then
CountChecks
R.MoveFirst
Do Until R.EOF
ShipTtl
R.Edit
R.Update
R.MoveNext
Loop
End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find shipping charges based on order size & State 2
Cancelling an update on a continuous form based on another value 2
hide multiple worksheets based on a formula value in one cell 1
Tab Index Order, Setting With VBA 0
How to update a field value based on the condition of a check box? 4
Copying or deleting row based on value found in other ws 7
Update query to set value based on position in recordset 2
Change DataGrid EditControl based On Column Value 2

Top