Add values to releted table with VBA.

[kyle18]

Hi there,

Tried to search, but no luck. Not easy to find such question.

I have two related tables. (related with AcqID)
tblAcq and tblAcqDetail

In tblAcq I have information about the delivery and in tblAcqDetail delivered articles, price and quantity.
tblAcq
[AcqID] ; [Contractor]; [TransportCosts]; [CustomDuties]; [SumWithoutVAT]; [LimSUM](this total cost of shipment Goods+other cost)
I use VBA code to calculate [SumWithoutVAT] (sum of goods), then [VAT],
everything works well,
Next is I need to calculate actual cost for each product and record in. [tblAcqDetail].[LimPrice].
To calculate it I divede [TransportCosts]+[CustomDuties]/ by [SumWithoutVAT], and the result (percentage) I need add to cost of each product and store it in [LimPrice].

For example product cost 0.95 Euro I bought 100 pcs, transport is 5 EUR.
it means actual product cost is (0.95*100=95, then 5/95 approx. 0.052, then 0.95*0.052+0.95=1.0026. So actual product cost is 1.00 Eur.
Thanks for help.

Lower is my VBA Code.
Private Sub btn_Calculate_qryAcqDetail_Click()

On Error GoTo ErrorMsgBox

[SumWithoutVAT] = DSum("[AcqSum]", "[qryAcqDetail]", "[AcqID]=" & [Forms]![frmAcqDetail].[AcqID])
[VAT] = ([SumWithoutVAT] * [VAT%])
[AcqStatus] = "CALCULATED"

Dim perAcqLim As Single

perAcqLim = ([TransportCosts] + [CustomDuties]) / [SumWithoutVAT]

[LimPrice] = ([AcqPrice] * perAcqLim) + [AcqPrice]

Exit Sub
ErrorMsgBox:
MsgBox ("Calculation Failed")
End Sub

Private Sub Close_Doc_Click()

On Error GoTo ErrorMsgBox

Dim curAcqLim As Single

'Total cost of delivery
curAcqLim = [SumWithoutVAT] + [TransportCosts] + [CustomDuties]
[LimSum] = curAcqLim
[AcqStatus] = "CLOSED"

Exit Sub
ErrorMsgBox:
MsgBox ("Calculation Failed")
End Sub

 

[JHB]

What is the question or problem?

 

[kyle18]

Basically

I have a field [LimPrice] in [tblAcqDetail]

I need to calculate it by adding to a field[acqPrice] cost of transport which is calculated in [tblAcq] by (transport divided by cost of shipment). But I don't understand how. Because transport cost is calculated once for whole shipment but [LimPrice] is cost for each article in related table.

Sorry It is not easy to explain it.

 

[JHB]

..
Sorry It is not easy to explain it. ..

Then maybe a print screen with relevant value and how you want to do the calculation could help.

 

[pr2-eugin]

Sorry It is not easy to explain it.

No explanation necessary ! Stop confusing yourself by storing calculations in tables. As you can see its only a giant heap of unnecessary coding. Store the needed raw/basic information, perform calculations on the go.