Average Cost Price Function

[gemma-the-husky]

Here's a semi working version.
I removed the AVCO's you had calculated in the delivery items table and set them to zero. .

So in module 1, I have broken down the calculation process to a series of steps, so that you can check that the calculation and code are working correctly.

Procedure process_order_intakes()

examines each record in the order_intakes table that hasn't already been processed.

It calls a function CalcAVCO using the productid, the current average price, and the quantity and price of this intake.

The function needs to obtain the total current stock, which involves a calculation to find all the intakes and despatches within the relevant date range which I haven't completed. You need to decide how to use the actual date. ie if your intake is 14/3/22, do you count sales up to only 13/3/22. What do you do with sales for 14/3/22? You also need to consider how to manage a starting position and opening stock for the date range. I wouldn't like to try to build this step into the query directly. (edit. I mean it's far easier to do this within code, that to try to build this into a single inline query - if indeed you even can do that)

The function then returns the new AVCO, which you need to save in the products table, and in the order intake table (or a different table) itself.
Note that you need to do this, as a further intake of the same material will need to use this AVCO for the next calculation.

I have commented out some "test" message boxes I added to check the progress. I also have commented out some code that needs to be considered.

It's a matter of taste whether you store the AVCO history at all, and where you store it. You couldn't store it IN the products table, as you need multiple history records for each product. You could store it with the product delivery records. It probably depends how you are most likely to want to use the history.

I hope this helps

[edit
I haven't included code to format, manage and check the date range used in the quantity on hand calculation.
I haven't included code to store the calculated AVCO in the relevant tables.
You could store an opening stock count and AVCO in the products table to reflect a collapsed history, if you will. That is included in the AVCO function.

 

[ChrisMore]

Here's a semi working version.
I removed the AVCO's you had calculated in the delivery items table and set them to zero. .

So in module 1, I have broken down the calculation process to a series of steps, so that you can check that the calculation and code are working correctly.

Procedure process_order_intakes()

examines each record in the order_intakes table that hasn't already been processed.

It calls a function CalcAVCO using the productid, the current average price, and the quantity and price of this intake.

The function needs to obtain the total current stock, which involves a calculation to find all the intakes and despatches within the relevant date range which I haven't completed. You need to decide how to use the actual date. ie if your intake is 14/3/22, do you count sales up to only 13/3/22. What do you do with sales for 14/3/22? You also need to consider how to manage a starting position and opening stock for the date range. I wouldn't like to try to build this step into the query directly. (edit. I mean it's far easier to do this within code, that to try to build this into a single inline query - if indeed you even can do that)

The function then returns the new AVCO, which you need to save in the products table, and in the order intake table (or a different table) itself.
Note that you need to do this, as a further intake of the same material will need to use this AVCO for the next calculation.

I have commented out some "test" message boxes I added to check the progress. I also have commented out some code that needs to be considered.

It's a matter of taste whether you store the AVCO history at all, and where you store it. You couldn't store it IN the products table, as you need multiple history records for each product. You could store it with the product delivery records. It probably depends how you are most likely to want to use the history.

I hope this helps

[edit
I haven't included code to format, manage and check the date range used in the quantity on hand calculation.
I haven't included code to store the calculated AVCO in the relevant tables.
You could store an opening stock count and AVCO in the products table to reflect a collapsed history, if you will. That is included in the AVCO function.

Thanks so much for your time in creating this, I very much like the direction this is going in.

I have been racking my brain trying to understand it, if I'm being honest. I think I am getting there but do have a few questions:

1. In the CalcAVCO function, what do you mean by openingstock? Is this the stock level before the order intake is processed?
2. I already have a function which calculates the current stock, at any given date, called Quantity_On_Hand. Can this be used in the CalcAVCO function instead of the formula currentstock = openingstock + totalintakes - totalsales? That way it wouldn't be necessary to have totalintakes and totalsales in the code as DSums.
3. Considering I have the Quantity_On_Hand function, can 'openingstock' not simply be a calculation of the on hand quantity as of the intake date minus the intake amount? If I'm wrong about question 1, then this wouldn't work.
4. I see you've added an OpeningStock field into the Products table, but when/how does this get updated? Maybe I've missed where this happens in the code. I would like to store the current AVCO in the products table and keep history in the deliveries table. I'm just struggling to understand the purpose of the OpeningStock field.
5. When would you suggest I run the process_order_intakes procedure? I currently have a purchase order deliveries form so could it be run when a new delivery is added, such as the AfterUpdate event of Goods_Delivery_Date?

 

[gemma-the-husky]

1. opening stock
Well, you can go back forever and add together all the intakes/outputs to evaluate the current stock.
Or, when you do a stock take you can store that current stock, and then you only need to sum transactions from that point on. So you need somewhere to store the opening stock quantity (and date - see 4 below). If you go back forever, you will be including stock you bought and sold 10 years ago. The more transactions you need to add, the slower it might be.

2. yes, if it works, The fact that you weren't getting the correct results seems to mean that there was something wrong. If you break it down it's easier to check.

3. No. It's a different thing. I don't mean current stock. See point 1.

4. There should be a Opening_Stock_Date as well. It's to inform the other calculations, as above.

5. Again, it depends on your structure. I think I would try to re-valuate the AVCO as you record each order intake. It wouldn't be the after_update event of a field. It ought to be the after_update event of a new record (when you save that record). Be careful about editing records - trying to replace the original transaction, with different values will impact all transactions since that date I think. If you don't do it this way, then it becomes harder to distinguish between whether records are processed or not. Do-able, but perhaps rather harder.

ie if you edit an existing record: If you recorded the price or quantity incorrectly then in theory changing that value would change all succeeding calculations. In practice it might be easier to include an adjustment as a new entry, rather than re-evaluate everything that you processed since the initial entry. If you have loads of sales transactions, you might have a very complex issue, especially if you have already declared an operating result and now change the figures that feed into that result.

 

[ChrisMore]

1. opening stock
Well, you can go back forever and add together all the intakes/outputs to evaluate the current stock.
Or, when you do a stock take you can store that current stock, and then you only need to sum transactions from that point on. So you need somewhere to store the opening stock quantity (and date - see 4 below). If you go back forever, you will be including stock you bought and sold 10 years ago. The more transactions you need to add, the slower it might be.

2. yes, if it works, The fact that you weren't getting the correct results seems to mean that there was something wrong. If you break it down it's easier to check.

3. No. It's a different thing. I don't mean current stock. See point 1.

4. There should be a Opening_Stock_Date as well. It's to inform the other calculations, as above.

I see what you mean now, I refer to 'opening stock' as 'stocktake' and I have a table which hold this data, including a stocktake date field (I also have a stocktake count table which the stocktake table is linked to but it seems I forgot to include it in the sample database).
The Quantity_On_Hand function calculates the stock value from the last stocktake and all the sale and purchase transactions thereafter. This function can be found in the sample database I attached. I haven't had any issues with the function and it has been accurately calculating our stock for well over a year now. I don't see it being the reason behind the issues I was having when running the loop code because the quantity on hand value was accurate in the calculation. Ideally I would like to incorporate the Quantity_On_Hand function within your CalcAVCO function to keep the method of calculating stock consistent with what's already in place. Would the Quantity_On_Hand function work within your code if it was to be amended?

5. Again, it depends on your structure. I think I would try to re-valuate the AVCO as you record each order intake. It wouldn't be the after_update event of a field. It ought to be the after_update event of a new record (when you save that record). Be careful about editing records - trying to replace the original transaction, with different values will impact all transactions since that date I think. If you don't do it this way, then it becomes harder to distinguish between whether records are processed or not. Do-able, but perhaps rather harder.

ie if you edit an existing record: If you recorded the price or quantity incorrectly then in theory changing that value would change all succeeding calculations. In practice it might be easier to include an adjustment as a new entry, rather than re-evaluate everything that you processed since the initial entry. If you have loads of sales transactions, you might have a very complex issue, especially if you have already declared an operating result and now change the figures that feed into that result.

I agree with this. I alluded to this earlier in this thread after what I found when testing my deliveries query with the AVCO field. It's why I wanted to create a loop code which re-evaluates all AVCO values when a new record is added. Instead of this, we will put in place a working procedure where the adjustment is made as a new entry so I will need to lockdown the intake records after they are saved and the process_order_intakes code is run.

 

[gemma-the-husky]

I think you must be pretty close. You have your original ideas, and an understanding of what I was suggesting. Just try different things until you get it working as you want. I find it easier to use a function to calculate things, rather than use an inline SQL/Query expression, as I find it easier to debug. One issue is that if your query stores the average price, but processing the items IN the query affects the average price that the query holds, then it must be unlikely that the average price extracted by the the query would be valid for all items - it must likely has been changed in the table, but the query would not auto-refresh. Therefore you probably need to continually re-compute the new average as you progress through the query items. In which case you most likely can't do this with a simple update or append query.

from your last post

I agree with this. I alluded to this earlier in this thread after what I found when testing my deliveries query with the AVCO field. It's why I wanted to create a loop code which re-evaluates all AVCO values when a new record is added. Instead of this, we will put in place a working procedure where the adjustment is made as a new entry so I will need to lockdown the intake records after they are saved and the process_order_intakes code is run.

comment re above remark
I gave you a loop to process multiple new transactions. You left out sales, as you thought it unimportant, but you need to include sales. If you have an old price calculated at £3, then your sales will show a cost price of £3. If you then change something historically that would make the £3 become £3.10 then it's not just the average that's affected. You need to change all of your sales records to reflect the changed average, which may affect your declared profits. You can't include existing values in your loop, where those values may be changed by the loop itself. You may be able to do that, but it becomes very complex, and you need to have a serious analysis of your process. It's not easy. Managing quantities is relatively easy. Managing prices and values is decidedly not easy.

also from your last post

I see what you mean now, I refer to 'opening stock' as 'stocktake' and I have a table which hold this data, including a stocktake date field (I also have a stocktake count table which the stocktake table is linked to but it seems I forgot to include it in the sample database).
The Quantity_On_Hand function calculates the stock value from the last stocktake and all the sale and purchase transactions thereafter. This function can be found in the sample database I attached. I haven't had any issues with the function and it has been accurately calculating our stock for well over a year now. I don't see it being the reason behind the issues I was having when running the loop code because the quantity on hand value was accurate in the calculation. Ideally I would like to incorporate the Quantity_On_Hand function within your CalcAVCO function to keep the method of calculating stock consistent with what's already in place. Would the Quantity_On_Hand function work within your code if it was to be amended?

comment re above remark
Yes but the quantity-on-hand isn't a static figure throughout the calculation. Every stock movement affects the quantity-on-hand. If you process 100 stock movements, using your QOH calculation, I assume you will re-calculate the QOH 100 times from first principles, and you have to be careful with the way the calculations include or ignore items in the current set of data. If you process the same 100 transactions, by reference to a known starting point, then the QOH calculation each time becomes simply the QOH +- This Movement, and then you store that value (in code) ready for the next item. So doing it this way means your QOH function is unnecessary. You could do it both ways though, to make sure you get the same result, and see if there is any speed difference. You thought your method was producing incorrect changing values, so assuming you are correct, there must be something wrong somewhere.

 

[ChrisMore]

I think you must be pretty close. You have your original ideas, and an understanding of what I was suggesting. Just try different things until you get it working as you want. I find it easier to use a function to calculate things, rather than use an inline SQL/Query expression, as I find it easier to debug. One issue is that if your query stores the average price, but processing the items IN the query affects the average price that the query holds, then it must be unlikely that the average price extracted by the the query would be valid for all items - it must likely has been changed in the table, but the query would not auto-refresh. Therefore you probably need to continually re-compute the new average as you progress through the query items. In which case you most likely can't do this with a simple update or append query.

from your last post


comment re above remark
I gave you a loop to process multiple new transactions. You left out sales, as you thought it unimportant, but you need to include sales. If you have an old price calculated at £3, then your sales will show a cost price of £3. If you then change something historically that would make the £3 become £3.10 then it's not just the average that's affected. You need to change all of your sales records to reflect the changed average, which may affect your declared profits. You can't include existing values in your loop, where those values may be changed by the loop itself. You may be able to do that, but it becomes very complex, and you need to have a serious analysis of your process. It's not easy. Managing quantities is relatively easy. Managing prices and values is decidedly not easy.

also from your last post


comment re above remark
Yes but the quantity-on-hand isn't a static figure throughout the calculation. Every stock movement affects the quantity-on-hand. If you process 100 stock movements, using your QOH calculation, I assume you will re-calculate the QOH 100 times from first principles, and you have to be careful with the way the calculations include or ignore items in the current set of data. If you process the same 100 transactions, by reference to a known starting point, then the QOH calculation each time becomes simply the QOH +- This Movement, and then you store that value (in code) ready for the next item. So doing it this way means your QOH function is unnecessary. You could do it both ways though, to make sure you get the same result, and see if there is any speed difference. You thought your method was producing incorrect changing values, so assuming you are correct, there must be something wrong somewhere.

Thanks again for all your helpful advice. I think I need to experiment with the code you created in order to understand how best to use it for our company. I'll spend some time doing that in the coming days and I'm sure I'll need to return to this thread when I get stuck. But, in the meantime, I really appreciate the time you given to this project.

 

[ChrisMore]

@gemma-the-husky, I'm off to a slow start unfortunately. I'm getting a compile error (wrong number of arguments or invalid property assignment) when I run the process_order_intakes code. It highlights 'CalcAVCO' in the code when the error appears. I haven't come across this error before, any ideas?

 

[gemma-the-husky]

The header for the procedure CalcAVCO shows the data types of the various parameters/arguments that it uses within the code.

You have to pass in variables or field names of the appropriate type when you call the procedure (sub or function). I think it needs 4 arguments, so you need to give it the 4 arguments it needs. The procedure itself isn't complete either so that needs attention as well.

 

[ChrisMore]

The header for the procedure CalcAVCO shows the data types of the various parameters/arguments that it uses within the code.

You have to pass in variables or field names of the appropriate type when you call the procedure (sub or function). I think it needs 4 arguments, so you need to give it the 4 arguments it needs. The procedure itself isn't complete either so that needs attention as well.

I've added those 4 arguments, the values are taken from the form controls where the intakes are entered. I'm not 100% sure if I've done it as you intended.

I'm still getting the compile error so I assume there is something I've missed. My amended version of your code is below, please can you point out what I'm missing or if I've got anything incorrect? Thanks very much.

Function CalcAVCO(itemcode As Long, intakedate As Date, intakeqty As Long, orderprice As Currency) As Currency

'calculate the new average cost for this item
'(current AVCO * currentstock) + (intakeqty * orderprice) / currentstock+intakeqty

Dim currentavco As Currency
Dim currentstock As Long
Dim openingstock As Long
Dim totalintakes As Long
Dim totalsales As Long
Dim average As Currency

itemcode = Forms![Purchase_Order_Deliveries_Pop_Up_Form]![Purchase_Order_Deliveries_Pop_Up_Subform].Form.[Product_Code]
intakedate = Forms![Purchase_Order_Deliveries_Pop_Up_Form]![Purchase_Order_Deliveries_Pop_Up_Subform].Form.[Goods_Delivery_Date]
intakeqty = Forms![Purchase_Order_Deliveries_Pop_Up_Form]![Purchase_Order_Deliveries_Pop_Up_Subform].Form.[Quantity_Delivered]
orderprice = Forms![Purchase_Order_Deliveries_Pop_Up_Form]![Purchase_Order_Deliveries_Pop_Up_Subform].Form.[Product_Price]

'need to error trap for nulls
currentavco = Nz(DLookup("avco", "products", "product_code = " & itemcode), 0)
'MsgBox "current Average = " & currentavco

openingstock = Nz(DLookup("openingstock", "products", "product_code = " & itemcode), 0)
'MsgBox "opening stock = " & openingstock

'you need to evaluate the date range you want to use, and format it for use within these statements

totalintakes = DSum("quantity_delivered", "purchase_orders_deliveries", "goods_delivery_date between #" & Format(#2/21/2022#, "dd/mm/yyyy") & "# and #" & Date & "#")
totalsales = DSum("Order_Quantity", "ACP_Sales_Query", "Production_Complete_Date between #" & Format(#2/21/2022#, "dd/mm/yyyy") & "# and #" & Date & "#")
'MsgBox "total intakes = " & totalintakes

'use the formula
currentstock = openingstock + totalintakes - totalsales

'break this down to check it's woking correctly.
average = ((currentavco * currentstock) + (intakeqty * orderprice)) / (currentstock + intakeqty)

MsgBox "New Average: " & average
CalcAVCO = average
End Function

Sub process_order_intakes()
Dim db As Database
Dim rst As Recordset
Dim newavco As Currency
Dim checked As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("Deliveries_query")

checked = 0
While Not rst.EOF
    'just check new order intakes. see query
   
    checked = checked + 1
    newavco = CalcAVCO(rst!Purchase_Orders_Deliveries_ID, rst!Product_Code, rst!Goods_Delivery_Date, rst!Quantity_Delivered, rst!Product_Price)
   
    'write an update statement to set the new average in the products table
    DoCmd.RunSQL "Update [Products] Set [AVCO] = newavco Where [Product_Code] = rst!product_code"
   
    'AND
    'write an update statement to store the average in the ordereditems table, or just save it by editing this record directly.
    DoCmd.RunSQL "Update [Purchase_Orders_Deliveries] Set [Average_Cost_Price] = newavco Where [Purchase_Orders_Deliveries_ID] = rst!purchase_orders_deliveries_id"

    rst.MoveNext
   
Wend

rst.Close
MsgBox "Checked " & checked & " Order Intake Records"

End Sub

 

[gemma-the-husky]

Well the header has 4 arguments

Function CalcAVCO(itemcode As Long, intakedate As Date, intakeqty As Long, orderprice As Currency) As Currency

the itemcode, as a long number - the reference code of the item being purchased
the intakedate, as a date - the date of the purchase
the intakeqty, as a long - the number of items purchased
the orderprice as a currency - the item cost of this intake

you are passing in 5 items, so the two declarations don't match, hence the error You have added in rst!product_code as another query argument
newavco = CalcAVCO(rst!Purchase_Orders_Deliveries_ID, rst!Product_Code, rst!Goods_Delivery_Date, rst!Quantity_Delivered, rst!Product_Price)

Now I look at it again, I am pretty sure I was wrong to use rst!Purchase_Orders_Deliveries_ID It should have been product_code. from the purchase order items table. However in order to use that field, you needed to drag the field product_code into the deliveries_query and then you can use rst!product_code INSTEAD OF rst!Purchase_Orders_Deliveries_ID, not as well as.

I think the compiler should be flagging this error, which it is, and it should be clear enough to understand the nature of the problem.

The syntax in your runsql statements is not correct though.
DoCmd.RunSQL "Update [Products] Set [AVCO] = newavco Where [Product_Code] = rst!product_code" will give you an error.
This should be
DoCmd.RunSQL "Update [Products] Set [AVCO] = " & newavco & " Where [Product_Code] = " & rst!product_code

The other update statement appears to be wrong as well.


The compiler won't flag these errors as your statements are syntactically correct. I expect these will just cause run time errors, as they won't resolve correctly at run time.

Also , I am not sure your date between range will deal with everything correctly in the relevant expressions. It may, but it may not. The syntax may be incorrect to manipulate the date range, and the logic to use the particular date range you are using may not be correct. I am just not sure. Hopefully you can check the results to confirm whether it all makes sense.

 

[ChrisMore]

Well the header has 4 arguments

Function CalcAVCO(itemcode As Long, intakedate As Date, intakeqty As Long, orderprice As Currency) As Currency

the itemcode, as a long number - the reference code of the item being purchased
the intakedate, as a date - the date of the purchase
the intakeqty, as a long - the number of items purchased
the orderprice as a currency - the item cost of this intake

you are passing in 5 items, so the two declarations don't match, hence the error You have added in rst!product_code as another query argument
newavco = CalcAVCO(rst!Purchase_Orders_Deliveries_ID, rst!Product_Code, rst!Goods_Delivery_Date, rst!Quantity_Delivered, rst!Product_Price)

Now I look at it again, I am pretty sure I was wrong to use rst!Purchase_Orders_Deliveries_ID It should have been product_code. from the purchase order items table. However in order to use that field, you needed to drag the field product_code into the deliveries_query and then you can use rst!product_code INSTEAD OF rst!Purchase_Orders_Deliveries_ID, not as well as.

I think the compiler should be flagging this error, which it is, and it should be clear enough to understand the nature of the problem.

The syntax in your runsql statements is not correct though.
DoCmd.RunSQL "Update [Products] Set [AVCO] = newavco Where [Product_Code] = rst!product_code" will give you an error.
This should be
DoCmd.RunSQL "Update [Products] Set [AVCO] = " & newavco & " Where [Product_Code] = " & rst!product_code

The other update statement appears to be wrong as well.


The compiler won't flag these errors as your statements are syntactically correct. I expect these will just cause run time errors, as they won't resolve correctly at run time.

Also , I am not sure your date between range will deal with everything correctly in the relevant expressions. It may, but it may not. The syntax may be incorrect to manipulate the date range, and the logic to use the particular date range you are using may not be correct. I am just not sure. Hopefully you can check the results to confirm whether it all makes sense.

Thank you for explaining this. I'm not familiar with coding terminology, such as arguments, but now you have explained it I understand that better. I've updated the runsql statements and they are running without errors. You are right about the date not being correct so I updated it to a starting date which is more suitable to my data. I've run an initial test of the code and it seems to be working. The AVCO is what I expected it to be at least so that's a good start.

I did make one change to this part of the code:

totalintakes = DSum("quantity_delivered", "purchase_orders_deliveries", "goods_delivery_date between #" & Format(#2/21/2022#, "dd/mm/yyyy") & "# and #" & Date & "#")
totalsales = DSum("Order_Quantity", "ACP_Sales_Query", "Production_Complete_Date between #" & Format(#2/21/2022#, "dd/mm/yyyy") & "# and #" & Date & "#")

I added an extra criteria so they both only sum itemcode within the date range because without this it sums all transactions for all items together.

totalintakes = DSum("quantity_delivered", "ACP_Intakes_Query", "product_code = " & itemcode & " And goods_delivery_date between #" & Format(#1/1/2022#, "dd/mm/yyyy") & "# and #" & Date & "#")
totalsales = DSum("Order_Quantity", "ACP_Sales_Query", "product_code = " & itemcode & " And production_complete_date between #" & Format(#1/1/2022#, "dd/mm/yyyy") & "# and #" & Date & "#")

 

[gemma-the-husky]

I'm pleased you are making progress. It's good that you are getting results that you can verify, as you need to be sure this is working correctly without intervention when you go "live".

Rather than use a fixed date of #1/1/22#, it would be a good idea to store the last stock take date in a settings table. Then after your next stock take, you can just update the last stock date counts, and reset the date in the table rather than have to search for it in the database code, and change it everywhere.

It's easy to miss some dates if you have to edit then manually, but equally importantly, it's better not to have to release unnecessary code changes, as then you have to make sure all users are using the correct version. (you should do that anyway, but it's good to get in the habit of not needing to change the code when you are really making a data change.)

 

[murphybridget]

Thanks very much for this info, I was definitely over complicating it thinking I needed a function. I have been able to use your calculation to get the AVCO via a calculation field in a query. It works great when a new purchase record is added as the last record in the query but not so well when the record slots in between other purchases (i.e. in the possible scenario that a purchase record needed updating as the goods received date was initially entered incorrectly). As you can see in the screenshot of the query below, the record with the price £7.53 in the 'Average_Cost_Price' field needs to be updated to £7.57 as per the ACP field.

The AVCO calculation involves three fields and two of them are calculation fields. The ACP field is your calculation (new AVCO) = (existing qty * current AVCO) + new total cost) / (existing inventory qty + new inventory qty). The calculation requires the current AVCO (I've named this field 'Previous ACP') meaning another field is required to store the AVCO. 'Previous ACP' uses the stored AVCO in the calculation. This means when a new record is added in between the other records, the ACP field calculation concertinas down as each stored AVCO is updated.

View attachment 98776
I haven't been successful in creating some code to automatically update the stored AVCO. I can't use an update query as each record needs to be updated one at a time in order for the ACP calculation to concertina down. The code below hasn't worked and I don't know if I'm on the wrong track with it. Note, the where condition in the code will change but is being used to test the existing data.

Dim dbs As DAO.Database
Dim rstDeliveries As DAO.Recordset
Dim strSQL As String

On Error GoTo ErrorHandler

Set dbs = CurrentDb

strSQL = "SELECT * FROM Deliveries_Query WHERE Average_Cost_Price Is Not Null"
Set rstDeliveries = dbs.OpenRecordset(strSQL, dbOpenDynaset)

If rstDeliveries.EOF Then Exit Sub

With rstDeliveries
Do Until .EOF
.Edit
![Average_Cost_Price] = ACP
.Update
.MoveNext
Loop
End With

rstDeliveries.Close
dbs.Close

Set rstDeliveries = Nothing
Set dbs = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

The screenshot below shows the result when running the code. Obviously not what I am looking for. I don't understand how it has arrived at the prices in the Average_Cost_Price field.
View attachment 98775

Are you or anyone else able to point me in the right direction to get this code working.

Many thanks.

Thank you for sharing.

 

[murphybridget]

I've added those 4 arguments, the values are taken from the form controls where the intakes are entered. I'm not 100% sure if I've done it as you intended.

I'm still getting the compile error so I assume there is something I've missed. My amended version of your code is below, please can you point out what I'm missing or if I've got anything incorrect? Thanks very much.

Function CalcAVCO(itemcode As Long, intakedate As Date, intakeqty As Long, orderprice As Currency) As Currency

'calculate the new average cost for this item
'(current AVCO * currentstock) + (intakeqty * orderprice) / currentstock+intakeqty

Dim currentavco As Currency
Dim currentstock As Long
Dim openingstock As Long
Dim totalintakes As Long
Dim totalsales As Long
Dim average As Currency

itemcode = Forms![Purchase_Order_Deliveries_Pop_Up_Form]![Purchase_Order_Deliveries_Pop_Up_Subform].Form.[Product_Code]
intakedate = Forms![Purchase_Order_Deliveries_Pop_Up_Form]![Purchase_Order_Deliveries_Pop_Up_Subform].Form.[Goods_Delivery_Date]
intakeqty = Forms![Purchase_Order_Deliveries_Pop_Up_Form]![Purchase_Order_Deliveries_Pop_Up_Subform].Form.[Quantity_Delivered]
orderprice = Forms![Purchase_Order_Deliveries_Pop_Up_Form]![Purchase_Order_Deliveries_Pop_Up_Subform].Form.[Product_Price]

'need to error trap for nulls
currentavco = Nz(DLookup("avco", "products", "product_code = " & itemcode), 0)
'MsgBox "current Average = " & currentavco

openingstock = Nz(DLookup("openingstock", "products", "product_code = " & itemcode), 0)
'MsgBox "opening stock = " & openingstock

'you need to evaluate the date range you want to use, and format it for use within these statements

totalintakes = DSum("quantity_delivered", "purchase_orders_deliveries", "goods_delivery_date between #" & Format(#2/21/2022#, "dd/mm/yyyy") & "# and #" & Date & "#")
totalsales = DSum("Order_Quantity", "ACP_Sales_Query", "Production_Complete_Date between #" & Format(#2/21/2022#, "dd/mm/yyyy") & "# and #" & Date & "#")
'MsgBox "total intakes = " & totalintakes

'use the formula
currentstock = openingstock + totalintakes - totalsales

'break this down to check it's woking correctly.
average = ((currentavco * currentstock) + (intakeqty * orderprice)) / (currentstock + intakeqty)

MsgBox "New Average: " & average
CalcAVCO = average
End Function

Sub process_order_intakes()
Dim db As Database
Dim rst As Recordset
Dim newavco As Currency
Dim checked As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("Deliveries_query")

checked = 0
While Not rst.EOF
    'just check new order intakes. see query
  
    checked = checked + 1
    newavco = CalcAVCO(rst!Purchase_Orders_Deliveries_ID, rst!Product_Code, rst!Goods_Delivery_Date, rst!Quantity_Delivered, rst!Product_Price)
  
    'write an update statement to set the new average in the products table
    DoCmd.RunSQL "Update [Products] Set [AVCO] = newavco Where [Product_Code] = rst!product_code"
  
    'AND
    'write an update statement to store the average in the ordereditems table, or just save it by editing this record directly.
    DoCmd.RunSQL "Update [Purchase_Orders_Deliveries] Set [Average_Cost_Price] = newavco Where [Purchase_Orders_Deliveries_ID] = rst!purchase_orders_deliveries_id"

    rst.MoveNext
  
Wend

rst.Close
MsgBox "Checked " & checked & " Order Intake Records"

End Sub

This one looks very challenging.