Sort Order

[razorkat99]

I have searched through and read as many posts as possible to find my answer, but am not coming up with my scenario to determine the resolution. Going back to my db that contains the address book I got the record selection to work correctly when I click on "A", "B", "C", etc., but the problem is with the sort when I open the form.

Because Access is sorting my records by how they are entered in the Customer Data Entry form, which is no specific order, the first record that comes up in my Call Log form when I open it starts with an "M" instead of "A". If I click on the letter "A" in the address book on this form it will sort the drop down selection box in order from A-Z. I want it to OPEN alphabetically not by 1st record in the table. I have tried entering SORT BY code in the On Open property field of the Call Log form, but that doesn't seem to work.

Based on what I have read I have two options: Sort the data in a query or write an event procedure (preferred). I tried writing an On Open event procedure based on examples in Access and here, but no luck.

Table where data is: tblLocation
Field to sort by: Location
Form: CustCallsDataEntryForm

Any assistance would be appreciated. I posted here since I am asking a form question and don't know if I should use a query or VB to get it to work properly.

 

[MSAccessRookie]

I am not too sure about Visual Basic, but in the Data Section of the Properties of any Form are two attributes ("Record Source" and "Order By"). The "Record Source" reflects the Table/Query that the input to the Form is based on. The "Order By" reflects the order that the data in the form will be available to the form for presentation. Here you could say something like "ORDER BY CustomerName ASC"

 

[razorkat99]

I tried that yesterday and it didn't work. Thanks anyway.

Any other thoughts? Anyone?

 

[boblarson]

Instead of the table, base the recordsource on a query, and put the sort in it.

If you aren't sure how to change your table to a query, here's a visual (this is an Access 2007 sample, but it is essentially the same for all versions):
http://www.btabdevelopment.com/main...dsourcefromtabletoquery/tabid/75/Default.aspx

 

[razorkat99]

Worked like a charm! I was afraid I was going to have to change a bunch of form and subform relationships if I had to use a query. Thanks again.

 

[boblarson]

Glad it worked for you. Good luck with the rest of your project.