If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Access > Sort records in a form on opening

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Jun 2004
Posts: 2
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.
Reply With Quote
  #2 (permalink)  
Registered User
Join Date: Mar 2004
Location: San Diego
Posts: 45
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?
Reply With Quote
  #3 (permalink)  
Registered User
Join Date: May 2004
Posts: 11

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.
Reply With Quote
  #4 (permalink)  
Registered User
Join Date: Jun 2004
Posts: 2
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.

Reply With Quote
  #5 (permalink)  
Registered User
Join Date: Dec 2003
Location: Dallas, TX
Posts: 998
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,
Reply With Quote
  #6 (permalink)  
Registered User
Join Date: Nov 2003
Location: Europe
Posts: 369
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.
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On