Automatically run report when a running total reaches a certain value

[MikeWaring]

Hi, I need to be able to my db to automatically run a report when the running total of a list of values reaches a certain amount.

At the moment, my database creates a daily list of products that we have sold that day and produces a report that shows the number of items sold and the total purchase cost of each item. We use this to create a csv file that we send to the supplier to reorder those products on the list.

However, what I need to do is to break this list up into sub lists, when the running total of the list reaches a predetermined value (say £250).

This will then allow me to send orders to the supplier in "bitesize" chunks which improves his delivery time to us as he himself is working on smaller orders throughout the day.

The list is sorted by the SKU, so on a particular day, if we have sold several identical products, our system already totals these into one line that shows the number of products and the total cost. This means we only place one order per day of each product.

We currently achieve this splitting by taking the full csv spreadsheet and then cutting / pasting it into several other sheets so that each sheet contains products to the total value of approx £250 each but this is clearly an inefficient and primitive way of doing it, hence the requirement to get Access to do this automatically.

The £250 figure won't of course need to be to the penny, we may have to somehow instruct the report to run when the value is between £230 to £290.

Would appreciate someone's help with this. I realise it sounds somewhat complicated but that's from a layman's perspective - I'm sure there's plenty of experts out there that can provide a solution!
Thanks in advance.

Mike

[June7]

"Automatic" is a matter of interpretation here. Some event must occur for something to happen 'automatically'. This event can be opening the project, opening a form or report, navigating records, entering data, button click, etc.

I have never seen code that will initiate an action based on value of a running calculation as you describe. So just did this test.

Created a textbox with expression in ControlSource like: =IIf(UserName="ANDREW",MsgBox("Test"),"")

Wow, it worked. So, presumably you could have expression like: = IIf(Amount=>250,MsgBox("SendOrder")
Problem is, you will get a message popup for every record that meets the criteria. That means if you substitute a custom function to create order in place of the MsgBox, it will run multiple times, meaning multiple orders.

Don't think what you want will be easy.

[MikeWaring]

"Automatic" is a matter of interpretation here. Some event must occur for something to happen 'automatically'. This event can be opening the project, opening a form or report, navigating records, entering data, button click, etc.

I have never seen code that will initiate an action based on value of a running calculation as you describe. So just did this test.

Created a textbox with expression in ControlSource like: =IIf(UserName="ANDREW",MsgBox("Test"),"")

Wow, it worked. So, presumably you could have expression like: = IIf(Amount=>250,MsgBox("SendOrder")
Problem is, you will get a message popup for every record that meets the criteria. That means if you substitute a custom function to create order in place of the MsgBox, it will run multiple times, meaning multiple orders.

Don't think what you want will be easy.

Hi June7, thanks for the rapid response.

I agree - it may not be that easy, but at least you've put me on the right road.

Since I posted the thread I've been thinking about this some more and have realised that I would also need to get the system to delete the records that contributed to each instance of the running total of £250, otherwise I assume it would keep cycling using the same records that made the intial £250.

In other words, If the total order value in the "master" list was say £1000, the report would need to run 4 times; after the 1st time, it would run a report then delete the first set of records that totalled £250, thus leaving the "master" list with a value of £750, then the next run cycle would leave it with a value of £500, the 3rd one with £250 and finally after the 4th report the list would be down to zero, thus ending the process.

Hope this makes sense- will this give you any more thoughts on how to achieve the required results?

I have to declare I'm a novice with VBA but have just bought 2 books on the subject to start learning, but I need quite a bit of time to follow the lessons fully to be able to get it into my head...meantime I still need to solve the issue at hand.

Once again thanks for your response and any further thoughts you may have.
Kindest regards
Mike

[June7]

I expect a solution could eventually be found but not a challenge I wish to tackle.

Consider this, let the function get called with each record => the cutoff but then code in the function places order only if some additional condition met.

[orange]

PMFJI, but I think there are a few questions that may help put your requirement into context.

You say you sell Products and when the values gets to a certain amount (£250) , you send a csv file to your Supplier to reOrder those Products.
It may just be the wording but you seem to be talking about Inventory and ReOrderPoint. So, it isn't really a value, but a quantity of Product that triggers the reorder. If you change the Selling price, seems to me your "value cut off"would need some tweaking.

If you want more "automatic", you may wish to review some designs involving Inventory, Sales, Products, reOrderPoint etc. and see if you can adjust your design to accommodate your new requirement. Note -- I am not suggesting a "full-blown" automated Inventory control system. I am suggesting that you may wish to incorporate ReOrderPoints and related processes in your design revisions/system modifications.

Also, depending on your set up, you might consider "automated email" to the Supplier possibly with some Order transaction. You may already have that, but, if so, it isn't clear.

I realize you have only skimmed over the requirement, and there is a lot of guess work on the readers' part. However, these comments are just for consideration as you plan for some system modification.

Good luck with your project.