I have a subform that is based on a query. The query has the ORDER BY built into it (as it relies on form fields). When a field on the subform is sorted, then when I come back into the form from a different calling form, that order is retained (the user selected sort). How do I programmatically PREVENT the user selected sort from being maintained when the form is closed?

The scenario is:
[new absence form] calls [fill absence] with an absence id# to "filter the records". [user may perform a sort here]

[fill absence] can be called directly from menu.

It is only when they perform a user-based sort, then return into the [fill absence] form from the [new absence] form that this issue occurs.

I've tried everything I can think of with setting orderby = "", setting it off, trying to reset to the query level orderby, trying to clear it on startup, and turning off the apply form changes to "all views" etc, at both the master form, and subform level, and can't get this working.

For example, I tried this on a close form event (for [Fill Absences]):
Private Sub Form_Close()
On Error GoTo Err_cmdClose_Click

    Me.Available_Substitutes.Form.OrderBy = ""
    DoCmd.Close acForm, "[Fill Absences]", acSaveNo

Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
End Sub

The [Available Subs] subform retains the user order by. If I put a clearing of the orderby on the subform, it appears it fires the subform's on-current event immediately afterward and causes another issue (can't see parent form's fields which doesn't make any sense???) The only time it appears to work is if I add a [close] button and put the exact same logic in there. However, the user may do some other action and get out (click a button, or hit alt-f4).

I would appreciate any help.

Thanks MUCH!!