Subform Refresh in a Tabbed Main Form

[theosgood]

I am using a tabbed form. I set criteria in one Tab, then open another Tab with a subform (record source is VBA module) that needs to refresh when the Tab has focus based on criteria set in the first Tab. When the Tab opens all fields say #delete# . If I use the Records menu, Refresh All, the records appear with the criteria set by a flag in another Tab. However, if I go back to the first Tab and change the criteria, go back to the subform Tab, the same results appear even after Refresh All.

I’m sure it’s something simple I’m missing, but how do I get the subform to refresh when the Tab has focus based on the current criteria?

Details:
Tab 1 – has a flag to set. Fields on form
Tab 2 – Product Selection Method, uses a subform based on a query with 2 buttons. Button 1 (Product Hierarchy) stores the query results in a temp table, closes Tab 2, Opens Tab 4. Button 2 (Product Vendor), closes Tab 2, Opens Tab 3.
Tab 3 – Vendor Selection Method, uses a subform based on a query with one button. The button stores the query results in a temp table, closes Tab 3, Opens Tab 4
Tab 4 – Product Selection, uses a subform based on a query built in VBA that uses the temp table created in either Tab 2 or 3 and applies the criteria based on flag set in Tab 1.
After hitting button in Tab 2 or 3, Tab 4 opens after running open code:

Public Sub HierClose_SkuAdd()
Me.AddSkuTab.Visible = True
Me.AddSkuTab.SetFocus
Me.HierarchyTab.Visible = False
End Sub

On the Main form, I tried adding Me.Refresh to Tab 4 On Click. In the subform I’ve tried to add Me.Refresh to a number of properties. I’ve put the Query module in On Current, On Load, On Focus, On Open. Still the same result. I’ve added a number of properties to the end of the code that generates the query for the subform to try refreshing. No luck.

Private Sub Form_Current()
Dim vSQL As String 'query string
Dim vExclude7 As String 'exclude7 value

vExclude7 = Forms("frmOfferCreate").Exclude7

vSQL = "SELECT *"
vSQL = vSQL & " FROM [tblActive Offer Listing-Temp]"
vSQL = vSQL & " WHERE [tblActive Offer Listing-
Temp].[Event#]=DLookUp(""[Event#]"",""tblTempEventRecord"")"
vSQL = vSQL & " AND [tblActive Offer Listing-Temp].[Offer Number]=DLookUp(""[Offer
Number]"",""tblTempOfferNumber"")"

If vExclude7 <> "0" Then 'If 0 ignore last And statement
vSQL = vSQL & " AND [tblActive Offer Listing-Temp].[CentEnding] <> ""97"""
End If


vSQL = vSQL & " ORDER BY [tblActive Offer Listing-Temp].[Sku]"

CurrentDb.QueryDefs("qryDept-Sub-Class Filter Select Activate").SQL = vSQL
'DoCmd.OpenForm "frmSku Select Activate"
'Me.Refresh
'Me.Active
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acRefresh, , acMenuVer70
End Sub

[RuralGuy]

It might help you to know that deleted records are not cleared from the Recordset with a Refresh. It requires a Requery of the Recordset to do that.

[theosgood]

Requery doesn't seem to solve the problem. It comes back with no records instead of #delete#, then Refresh All makes them visible. I've tried putting Requery in both modules, I've added it to different events in the subform, On Activate, On Load, etc. I added it to On Click on the Tab in the main form. In all cases it appears the query runs when the main form is loaded. In some cases it runs but it does not pick up the criteria change when you switch tabs and still needs to refresh. Except for refreshing, the query module works with the On Load event in the subform, but even adding that to the tab or other subform events doesn't seem to pick up criteria changes. All my other subforms, based on stored queries, work fine, it's only this one with the query module that is giving me a headache. Is this a scope issue? Is there anything else to try? Is there a way to write the stored query with dynamic criteria (field centending with criteria, IIf(Exclude7 = -1, “<>”97””, “>”0””) and variations I've tried, do not work)?

[RuralGuy]

A static query (stored) can reference a control on an open form and the control need not be visible.

[theosgood]

Thanks. That got the query working without the code module and now the query refreshes when you change the criteria flag.

[RuralGuy]

Excellent! Thanks for posting back with your success.