Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 8th, 2004, 03:20 PM
Registered User
Join Date: Jun 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default fixing orderby in subform from user-initiated sort

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 Sub

    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!!

Similar Threads
Thread Thread Starter Forum Replies Last Post
User selection to sort records in a Crystal Report MisterN Crystal Reports 1 January 24th, 2007 01:03 PM
how to sort cross tab.sort based on row total joxa83 Crystal Reports 7 March 2nd, 2006 09:12 AM
Applet not initiated in Tomcat 3.3 xquizit Apache Tomcat 2 November 28th, 2005 12:29 PM
Subform allows end-user to make duplicate record pkaptein1 Access 7 May 25th, 2005 11:34 AM
ADO/SQL Server RecordSet and the OrderBy Property Dataman Access 3 February 9th, 2004 11:11 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.