SubReport in a navigation subform is not reloading after query change and requery

Question

I have a subreport in a navigation sub form that uses a multiselect list box to define criteria to query and display in a sub report on the same form. I am using this requery

Forms![Main]![NavigationSubform].Form![ETIC By Customer Report].Report.Requery to change reports in this fashion throughout the project and it works perfectly fine, but when I use it in the event code it does not crash it simply does not update the report view. If I open up the report individually everything is fine and works appropriately.

Private Sub Command83_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim unitstr As TempVar
Set db = CurrentDb()
Set qdf = db.QueryDefs("ETIC by Customer")
For Each i In [Forms]![Main]![NavigationSubform].[Form]! [Combo11].ItemsSelected
     strCriteria = strCriteria & "," & Chr(34) & [Forms]![Main]![NavigationSubform].[Form]![Combo11].ItemData(i) & Chr(34)
   Next i strCriteria = Mid(strCriteria, 2)
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything." _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
TempVars!unitstr = strCriteria
strSQL = "SELECT DISTINCTROW DPASDataWorkTable.[Work Order Id], DPASDataWorkTable.[Asset Id], DPASDataWorkTable.[Approval Dt], DPASDataWorkTable.[Item Desc], DPASDataWorkTable.[Work Order Status Cd], DPASDataWorkTable.[Closed Dt], DPASDataWorkTable.ETIC, DPASDataWorkTable.Remarks, DPASDataWorkTable.[Parts Bin Location], DPASDataWorkTable.[Completed Deferred], DPASDataWorkTable.[Floor Technician], LIMSData.[MGMT CD], DPASDataWorkTable.[Date Opened (By W/O)], DPASDataWorkTable.[Remarks For FMA], DPASDataWorkTable.Priority, LIMSData.[MASTER MGT CODE], LIMSData.User, LIMSData.[PRG CAT], LIMSData.UNIT, LIMSData.[VEH MAKE TYPE], LIMSData.Org, LIMSData.Status, LIMSData.Shop, LIMSData.[Physical Location], LIMSData.Driveable, [Concatinated Tasks].[Line Items]," & _
"[Concatinated Tasks].[Shop Code], DPASDataWorkTable.[Current Shop], LIMSData.[Physical Location], [MEL Table].[MEL Key], DPASDataWorkTable.[Remaining Hours] AS [Total Remaining], DPASDataWorkTable.[Est Hours] AS [Total Estimated],LIMSData.UNIT As [UNIT]" & _
"FROM [MEL Table] INNER JOIN (LIMSData INNER JOIN ((DPASDataWorkTable INNER JOIN [Concatinated Tasks] ON DPASDataWorkTable.[Work Order Id] = [Concatinated Tasks].[Work Order ID]) INNER JOIN DPASDataSubTable ON DPASDataWorkTable.[Work Order Id] = DPASDataSubTable.[Work Order Id]) ON LIMSData.[Local Key] = DPASDataWorkTable.[Local Asset Key]) ON [MEL Table].[MEL Key] = LIMSData.[MEL Key] " & _
"WHERE LIMSData.Unit IN (" & strCriteria & ") and DPASDataWorkTable.[Work Order Status Cd]=""O-Open"";"

Forms![Main]![NavigationSubform].Form![ETIC By Customer Report].Report.Requery
qdf.SQL = strSQL
Set db = Nothing
Set qdf = Nothing
End Sub

wazz wazz · Accepted Answer · 2017-06-16T11:10:26

I think you need to switch these 2 lines:

Forms![Main]![NavigationSubform].Form![ETIC By Customer Report].Report.Requery
qdf.SQL = strSQL

to

'update the query (sql) first.
qdf.SQL = strSQL
'then requery the form.
Forms![Main]![NavigationSubform].Form![ETIC By Customer Report].Report.Requery

Also, from MSDN: "If you do not remove a TempVar object, it will remain in memory until you close the database. It is a good practice to remove TempVar object variables when you are finished using them." Link: TempVars.

SubReport in a navigation subform is not reloading after query change and requery

1 Answers