Complicated Problem

[Lurk]

Ok this is a complicated problem that I want to do in the Code Builder of Access...

I can't figure it out for a total and getting information from the subform

I have 2 check boxes that are taxes and if they are checked then they will be added to the price
then the total for that one will be quantity multiplying with the unitprice and adding the taxes
but if I have say 5 items, I want all of the items added together!

So you know what I mean here is a pic:

I want all the red items added to the total

I thought of this:
Private Sub Total_BeforeUpdate(Cancel As Integer)
If OrderDetail_TaxableGST = True Then
GST.Value = 7
Else
GST.Value = 0
End If
If OrderDetail_TaxablePST = True Then
PST.Value = 8
Else
PST.Value = 0
End If
Dim PTax, GTax, Tax
PTax = PST.Value/100
GTax = GST.Value/100
Tax = (Quantity.Value*UnitCost.Value)*(PTax+GTax)
Total.Value = (Quantity.Value*UnitCost.Value)+Tax
End Sub

But I don't know how to add all of the items!

Thanks!

 

[Emohawk]

Not really understanding your problem as the picture you posted does not show up at my end. Are you able to give me a bit more info?

 

[SC]

You may be able to do what you are trying to do is by using the Sum() function in the footer of the Sub Form and referencing this in the main form.

HTH

SC

 

[Rich]

This is probably better dealt with by a multiple Iif query, or a function which calculates the tax and returns it to the query, if you base the form on the query the calculated values will show after you've entered the relevant data.

 

[chrismcbride]

I assume that the Items are details in the subform and that the tax indicators are details of the master form???

If so you need to provide a calculated control that does unit_price * qty and put this in each detail record. An example from one of my sub forms...

=IIf([units_ordered]>0,(CLng((((((([unit_price]-([unit_price]*[unit_discount]))*[exchange])*(1+[unit_markup]))*[units_ordered])+[shipping])/[units_ordered])*100))/100,0)*[units_ ordered]

My example is a little complex due to user requirement (need line by line shipping costs as well as shipping costs for the whole order, have products in multiple currencies on the same order so I need line by line exchange rates etc). Also there is a test for actual units ordered and a clng that rounds off the final value to two decimal points. If you have a table field whose format is set to Currency, do not forget that there are digits possibly present in the stored value that are not shown when that value is displayed on a form or report. You must round off in some way so that your fields appear to add up correctly. Then in the footer of the sub form you add another calculated control which does a sum of the sub form values ...

(=Sum(IIf([units_ordered]>0,(CLng((((((([unit_price]-([unit_price]*[unit_discount]))*[exchange])*(1+[unit_markup]))*[units_ordered])+[shipping])/[units_ordered])*100))/100,0)*[u nits_ordered]))

Make this control invisible and then reference it's value in a control source of a text field on the master form...

=IIf(IsNull([frmQuote_Sub].[Form]![total]),0,[frmQuote_Sub].[Form]![total])

Once this is done you can add logic to calculate the amount of the tax in other boxes...

=IIf([Forms]![frmQuote]![chkGST]=True,(CLng((([frmQuote_Sub].[Form]![total]+[ship]-[cfi_amount]-[discount_amount])*0.07)*100))/100,0)


...and then do a sum of the form controls...

=IIf(IsNull([frmQuote_Sub].[Form]![total]),0,((([frmQuote_Sub].[Form]![total]+[gst_amount]+[pst_amount]+[ship])-[discount_amount])-[cfi_amount]))

You may have to explicitly requery some of the calculated controls to get the behaviour that you want...

HTH
Chris