VBA Alternative to a Slow Query Chain (3 Viewers)

[ChrisMore]

you can try moving the zz_onhand_temp table to the FE (each FE) or move it to another Backend.

i did look at your Inventory_Warning_Query, and Production_ID and Product_Code from zz_onhand_temp table is being linked.
so i think you only need to insert these 2 fields in your Insert Query code and just leave out all the rest of the fields (and calculated ones).
all other fields are not required since on your code, you are only checking if there is record in inventory_warning_query.

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 the "Quantity Required" for production. The code isn't only checking if there is a record in inventory_warning_query, it also opens the report after the message box appears to notify the user of insufficient stock.

I am wondering if I can simplify this code though, because I am running the query chain twice with this approach... First to check for a record, then to open the report. Can a report open hidden if no record or view report if there is a record?

 

[arnelgp]

you can also check if table zz_onhand_temp has some record on it?

ProCnt = DCount("*", "zz_onhand_temp")

 

[ChrisMore]

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