Form not refreshing when query def updated

Question

I have a form that contains a subform which displays a datasheet view of a query. There is a button on the main form that when clicked will revise the query's SQL. When the button is clicked, the query is revised as expected, but the results do not update in the subform unless I close and reopen the main form.

I've tried to requery the subform and the main form. I've tried to refresh the subform and the main form. I've tried various combinations of the two, but nothing will update the data shown in the subform except closing and reopening the main form.

Any insights or suggestions?

This is the code that I have in the main form, and it runs when a button is clicked.

    Dim f as Form
    Set f = Me.frm_Subform.Form

    ' Update the query def
    Dim sSQL As String
    Dim sqlArray As Variant
    sSQL = CurrentDb.QueryDefs("myQuery").SQL
    sqlArray = Split(sSQL, "ORDER BY")
    sSQL = Left(sqlArray(0), Len(sqlArray(0)) - 3)
    sSQL = sSQL & " AND (STAFF.FULLNAME <> 'JOHN DOE') "
    sSQL = sSQL & "ORDER BY" & sqlArray(1)
    CurrentDb.QueryDefs("myQuery").SQL = sSQL

    ' Refresh the subform
    F.Requery
    Me.Requery
    F.Refresh
    Me.Refresh
    F.Repaint
    Me.Repaint

Still curious if anyone knows though why modifying the query and refreshing/requerying won't update the data in the subform. — shoegazer100

June7 June7 · Accepted Answer · 2017-04-19T16:41:47

Why modify query? Just to change the sort order? Build a form set for Datasheet view and apply sort criteria to the OrderBy property using DoCmd.SetOrderBy or

Me.OrderByOn = False    
Me.OrderBy = "your criteria"
Me.OrderByOn = True

And if you want to dynamically apply filter criteria, use code similar to the OrderBy.

Form not refreshing when query def updated

1 Answers