Requerying Query Without also Requerying its Subqueries

[Ganymede]

I have a form with a listbox (List11). The recordsource for List11 is a query (NPRsListBoxFinal). The same form contains a textbox (Text7) with a change event that applies a filter to List11 based on the input in Text7. See the code below. (The code works fine. This is just for background.)

Private Sub Text7_Enter()
On Error GoTo Err_Text7_Change
Dim strSource As String
strSource = "SELECT  NPRId, AppealDeadline, CCN, HospitalName, FYE, IssueName, Client, AssignedTo, Complete, Docs, Group, Year  " & _
"FROM NPRsListBoxFinal " & _
"Where CCN Like '*" & Me.Text7.Text & "*' " _
& "Or HospitalName Like '*" & Me.Text7.Text & "*' " _
& "Or IssueName Like '*" & Me.Text7.Text & "*' " _
& "Or Client Like '*" & Me.Text7.Text & "*' " _
& "Or AssignedTo Like '*" & Me.Text7.Text & "*' " _
& "Or Year Like '*" & Me.Text7.Text & "*' "
Me.List11.RowSource = strSource


Exit_Text7_Change: Exit Sub
Err_Text7_Change: MsgBox Err.Number & " " & Err.Description
Resume Exit_Text7_Change
End Sub

NPRsListBoxFinal is the combined product of several nested subqueries. One of those subqueries (the one that populates the "Group" field) is very cumbersome. Let's just call that Subquery1. Because Subquery1 is so resource-intensive, whenver I apply search criteria in Text7, it takes several seconds for List11 to refresh, because it is basically reruning Subquery1 with every keystroke. I considered changing Text7 to an "on enter" event, but even when I do that it still takes about 8 seconds for List11 to update. I know that Subquery1 is the problem because the delay disappears when I remove Subquery1 from NPRsListBoxFinal.


Here's my question. Is there a way to requery NPRsListBoxFinal without also requerying one or more of its nested queries (e.g., Subquery1)? For instance, perhaps I can program NPRsListBoxFinal to fully requery when the form opens, but to only partially requery when text is entered into Text7. Is there any other workaround?

[Gicu]

Try changing the NPRsListBoxFinal into a make-table query (temporary local table) in the open or load event of the form and use that table as the row source for the list box. You might need to compact the front-end more often....

Cheers,

[Welshgasman]

Not liking your naming convention at all.
How do you remember what holds what 6 months down the line?

[Ganymede]

Try changing the NPRsListBoxFinal into a make-table query (temporary local table) in the open or load event of the form and use that table as the row source for the list box. You might need to compact the front-end more often....

Cheers,

Thank you. I did consider that option. The difficulty I had was reverting NPRsListBoxFinal back to a query after the fact. When I ran NPRsListBoxFinal as a make table query, it stayed as a table permanently.

[Ganymede]

Not liking your naming convention at all.
How do you remember what holds what 6 months down the line?

These are just temporary placeholders until I can figure out how this form I am working on is going to work conceptually. I have no intention of keeping the names as they are. I agree that would be a nightmare.

[Ganymede]

Thank you. I did consider that option. The difficulty I had was reverting NPRsListBoxFinal back to a query after the fact. When I ran NPRsListBoxFinal as a make table query, it stayed as a table permanently.

Disregard. I figured it out. For some reason the first time I tried it the Maketable query disappeared and all that was left was the table itself. But that issue has not repeated. Marking as resolved. Thanks again!