Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004

    Unanswered: Sort records in a form on opening

    I'm a total newbie and everything I've done so far has been by just clutzing around till the database does what I want it to do, so I don't even know if you can do this.

    I'd like my database to auto sort records in a form (based on invoice number) when users open it.

    Can it be done? I have Zero experience with macros, VB, or expression building.

    Thanks to any who may reply.

  2. #2
    Join Date
    Mar 2004
    San Diego
    The easiest way of doing this would be to just do the sort at the query level, and then use the sorted query as the recordsource for the form. There are ways to do it in code, but this is by far the easiest way that I know of.

    Does that help?

  3. #3
    Join Date
    May 2004


    I'm a total newbie but I seem to remember something I read in Access for Dummies. If you click the form then the properties button, go to the data tab and somewhere near the top is an entry called Order By or words to that effect. If you enter the name of the field you want sorted by it should do it. You can also put "fieldname DESC" and it'll put them in descending order.

    Wow, I've learned enough to attempt an answer.

  4. #4
    Join Date
    Jun 2004
    OMG! I figured it out. I feel like such a pro!

    The above suggestions did not do what I needed it to do but there is a Microsoft Knowledge Base Article (#207769) that addresses the issue.
    Article 207769

    First back up your Data!
    1. Start Microsoft Access and open the sample database Northwinds.mdb or the sample project NorthwindCS.adp.
    2. Open the Employees form, and then note the order in which the records appear.
    3. Ensure that the First Name field has the focus, and then click the Sort Ascending tool on the Form View toolbar.
    4. Close the form, reopen it, and note that the records are still in ascending order by first name.
    5. Open the form in Design view, and then in the property sheet for the form, click the Event tab.
    6. Create the following event procedure for the form's On Open property:Private Sub Form_Open(Cancel As Integer)

    Me.OrderBy = ""

    End Sub

    7. Close the module. Save and close the form.
    8. Reopen the form, and then change the sort order for one of the fields by clicking the Sort Ascending or Sort Descending button on the Form View toolbar. Close the form.
    9. Reopen the form and note that the sort order change that you made before you closed the form has not been reapplied.
    Basically, open your database instead of the northwind database, and make sure to fill in the field you want sorted between the quotes.


  5. #5
    Join Date
    Dec 2003
    Dallas, TX
    However as stated above, doing so in your query would have only taken just ONE step. That would have done all the same for you as well, but much simpler and easier. There are so many ways to do things in Access, just up to the user to choose which. Good to hear you accomplished it still.

    have a nice day,

  6. #6
    Join Date
    Nov 2003

    Angry Simply cannot get the Form property Order By to work...

    I even have based my sub form on a query that in itself sorts descending, which works when running/using the query "elsewhere".
    But basing my sub form on the descendingly sorting query gives me ascending sort order in the sub form, even if I also manually sort descending by the view/decending menu button, saving the form, AND using "fieldname desc" in the form properties...

    I tried writing "fieldname desc", fieldname desc, [fieldname] desc, "[fieldname] desc"... Nothing works.

    When using SQL Select sentence with order by clause directly in the row source property, the sorting works, but not because of the property setting...

    What am I doing wrong? Or not doing?

    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts