Recent content by ChrisMore

I've kept the code but have incorporated the messagebox text into the report header. I'm happy to keep it like this because it speeds up the process furthermore. Thanks very much for your assistance on this :)

Yes, the HasData value is 0 but there is a record, the report then opens to display it

I moved the code from the report Open event to the NoData event and it now opens the report but the messagebox doesn't appear. Private Sub Report_NoData(Cancel As Integer) Cancel = True If Not Cancel Then MsgBox "It is projected there will be insufficient stock of one or more...

That's solved the error but the report isn't opening even though there is a record

I've made the changes and I'm now getting an error "The OpenReport action was canceled"

The temp table is only required to work out the BOM data. As shown on the attached diagram, it replaces 'Production_Units_BOM_Query1' to speed up the process. So inventory_warning_query is the only way to know if the warning report needs to open when there is insufficient stock

That's a simple solution, I've changed zz_onhand_temp to a local table. You're right about Production_Date and 'onhand' (I've moved this calculated field to the query where the projected inventory is actually calculated). The field 'BOM' is required so the Inventory Warning Report can display...

Thanks for the attached, that's very helpful. I've incorporated everything you suggested and it definitely has made the process much quicker. Everything is running much more smoothly across the whole Db, so thanks for the suggestion. Regarding my current set up where I am appending and then...

Hi @arnelgp, I have added the missing tables and reattached, but these are only required when updating the Production Units field and not the Production Date field. I should have removed the code requiring these tables because it's not relevant to the inventory warning process I am trying to...

This is more like what I had in mind when I created this thread, so thanks for suggestion it. What I've done so far by appending the data to a table and then deleting that data feels clunky and not optimal in the slightest. I've reattached the database and data, but this time have included the...

It will be needed every time the Production_Date field is updated. This is when the inventory warning check process is required. So it will append the data then open the report if a record is found in the Inventory_Warning_Query. The data will then need to be deleted after the process.

I've created a temp table (zz_onhand_temp) and I've used an append query to add the data, which would have been the result of the 'Production_Units_BOM_Query1. I also included the onhand field to be appended. Of course it takes a few seconds to run the append query but not too bad. The...

Yes, I think you're right, it's slowest when the onhand function is involved. I am unsure how to get around the onhand function because it is required when calculating the projected inventory. The whole thing can't work without the projected inventory being calculated.

I've attached a test version of database with the queries and the test data. 11 queries in total and Inventory_Warning_Query is the record source of the report. Due to deleting a lot of data to be able to attach the file, the query is running fast. I'd say it usually takes around 15 seconds min...

Hi all, I have a query named Inventory_Warning_Query which is designed to display the products that have an inventory value projected to fall below zero, when factoring in a new production date. The results are shown in a report and act as a warning when the production date is entered so it can...