VBA Alternative to a Slow Query Chain

[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

 

[arnelgp]

ok, maybe directly Open the report.
add code to your report's Open event to check if there are records to show.
if none just Cancel the report.
if there is, show your messagebox.

on your report Open Evernt:

Private Sub Report_Open(Cancel As Integer)
    Cancel = (Me.HasData = 0) ' the report has nothing to show
    If Not Cancel Then
        MsgBox "It is projected there will be insufficient stock of one or more inventory products to manufacture the allocated Production Units on this date." & _
            vbCrLf & "" & _
            vbCrLf & "Click Ok to view the projected inventory information for the affected inventory product/s, then reschedule production to a date when" & _
            " more stock is due to become available.", vbExclamation, "Insufficient Projected Stock"
    End If
End Sub

on your production_schedule_form (production_date_afterupdate event):

...
...
...
' arnelgp
' remove these lines
'ProCnt = DCount("*", "[Inventory_Warning_Query]")
'DoCmd.Hourglass False
'
'If ProCnt = 0 Then
'Else
'MsgBox "It is projected there will be insufficient stock of one or more inventory products to manufacture the allocated Production Units on this 'date." & _
'    vbCrLf & "" & _
'    vbCrLf & "Click Ok to view the projected inventory information for the affected inventory product/s, then reschedule production to a date 'when" & _
'    " more stock is due to become available.", vbExclamation, "Insufficient Projected Stock"
'DoCmd.Hourglass True
'DoCmd.OpenReport "Inventory_Warning_Pop_Up_Report", acViewReport
'DoCmd.Hourglass False
'End If

'just open the report (if no data to print, it will not show up)
DoCmd.OpenReport "Inventory_Warning_Pop_Up_Report", acViewReport

DoCmd.RunSQL "Delete * From [zz_onhand_temp]"

End Sub

 

[ChrisMore]

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

 

[arnelgp]

add this to the code:

...
...
'just open the report (if no data to print, it will not show up)
On Error Resume Next
DoCmd.OpenReport "Inventory_Warning_Pop_Up_Report", acViewReport

DoCmd.RunSQL "Delete * From [zz_onhand_temp]"

 

[ChrisMore]

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

 

[ChrisMore]

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 inventory products to manufacture the allocated Production Units on this date." & _
            vbCrLf & "" & _
            vbCrLf & "Click Ok to view the projected inventory information for the affected inventory product/s, then reschedule production to a date when" & _
            " more stock is due to become available.", vbExclamation, "Insufficient Projected Stock"
    End If
End Sub

 

[arnelgp]

remove first the NoData event, but test the HasData value:

Private Sub Report_Open(Cancel As Integer)
Msgbox Me.HasData
'    Cancel = (Me.HasData = 0) ' the report has nothing to show
'    If Not Cancel Then
'        MsgBox "It is projected there will be insufficient stock of one or more inventory products to manufacture the allocated Production Units on 'this date." & _
'            vbCrLf & "" & _
'            vbCrLf & "Click Ok to view the projected inventory information for the affected inventory product/s, then reschedule production to a date' 'when" & _
'            " more stock is due to become available.", vbExclamation, "Insufficient Projected Stock"
'    End If
End Sub

 

[ChrisMore]

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

 

[arnelgp]

maybe just reinstate your original code.

 

[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