CODEWITHSUNDEEP
ms-accessvba
shoegazer100
shoegazer100

Reputation: 13

Form not refreshing when query def updated

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

Upvotes: 0

Views: 765

Answers (1)

June7
June7

Reputation: 21379

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.

Upvotes: 1

Related Questions