Recent content by ChrisMore

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...

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...

@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?

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...

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...

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...

Thanks very much for your continued assistance :) I'm happy to also store the ACVO in the products table, that makes sense. But can I ask why it's necessary to store the history in a products subtable? The plan was to store it in the deliveries table, which is linked to the purchase items...

Thank you for having a look. 1. I removed the suppliers table because it wasn't relevant to the sample database. 2. I don't understand what you mean by this as the purchase order items table is in the query. Unless you mean customer order items, if so, I deemed it not necessary to the query...

@gemma-the-husky have you had a chance to look at the sample database I attached? I appreciate any help you can provide with this :)

That's probably the reason DMax didn't work for me then and I had more success with DLast. I have attached a test version of the database with the same data we have been focusing on in this thread. I have included my version of the PreviousACP function, however, feel free to start it again if...

I'm trying DMax in the new function in the hope that it will work correctly with the loop code. I've made a start with it but as this is my first function I'm a bit stuck. This is what I have so far: Public Function PreviousACP() As Long Dim lngACP As Long lngACP =...

I initially tried DMax in the expression but DLast worked better. Not sure why it did but I stuck with it. The expression does exactly what I expect it to do to calculate the average cost but maybe it isn't working with the loop code. Due to my lack of coding knowledge/experience, I...

I hope I am interpreting your questions correctly in my answers here. 1. The average price is both stored and calculated each time. It is stored in the Average_Cost_Price field and calculated using the 'Previous ACP' and 'ACP' calculated fields in the query. The Average_Cost_Price field is...

There must be an issue somewhere. As shown in the below screenshot, I have taken away some of the data from the Average_Cost_Price column so the SQL only returns the rows where the Product_Price value is £7.52. This means the Average_Cost_Price value should be £7.52 because the average price of...

Thanks, I added the format out of desperation to get the code to work. I have taken the format away but the code isn't doing what I want. It looks like the ORDER BY isn't working or there is something else incorrect. As you can see in the screenshot below, Average_Cost_Price on the highlighted...