Sorting data for a Form

[connolly_tm]

This may be a very naive question, but ... I have a Form that has a table source -- I need to present certain data elements to the user who will work with the form in a sequence (e.g., alpha by CustomerLastName). The underlying table is sorted in CustomerNumber and PlantCityZIP code order. When the Form opens, how can I get the data to re-sort for the user in CustomerLastName order?

Appreciate this resource immensely -- tmc

 

[pbaldy]

Check out the OrderBy property of the form, plus you can base the form on a query that includes the desired sorting.

 

[connolly_tm]

Thank you Uncle Gizmo,

I am pretty new to Access and am having trouble getting the OrderBy instruction to be accepted -- I wrote it OrderBy = [tablename].[fieldname] and then when I open the Form, I get a message saying the database cannot find the Object OrderBy = [tablename].[fieldname]. and no sorting is performed

Then I tried =OrderBy([LastName]) and this does not perform the sorting I intended.

I tried to find OrderBy in my Access books, but it doesn't seem to be there. Is it one of those VBA actions? DO those require specific formulas and characters?

Thank you for helping me.

tmc

 

[gemma-the-husky]

using a query is the best.

then just at the bottom, pick the fields you want, and set the sort order as ascending/descending etc.

its also better in that oyu can then apply any other filter conditions to select only certain rows, or indeed just include certain columns.

also a stored query gets saved, and will execute more quickly on subsequent uses. (although imperceptible for smaller databases)

 

[connolly_tm]

[FONT=&quot]Thank you Uncle Gizmo,[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]The statement I placed in the On Load line for the form is:[/FONT]
[FONT=&quot]OrderBy = “[USCustomerReps].[LastName]” – where USCustomerReps is the name of the table that is used by the form and which contains the field LastName. This gives me the “cannot find” result.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Then I tried [/FONT][FONT=&quot]=OrderBy([LastName]) and this does not generate the “cannot find” result and it also does not sort the information by LastName.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Thank you[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]tmc[/FONT][FONT=&quot][/FONT]

 

[connolly_tm]

Hi Dave

Oddly enough, I tried a query to pull the records I need for this user and made the LastName the only sorted field (A to Z) and when the form opens, the LastNames are not in alpha order. Then I changed the query to Make a Table with the LastName sorted and again -- not in alpha order. That's when I figured I should come to the Forum as there must be something about Forms that is preventing me from getting this User's task well presented. Thank you for your suggestion -- much appreciated. tmc

 

[connolly_tm]

Uncle Gizmo,

Thank you -- apologize that I didn't quite understand your first suggestion -- so I tried to implement the second one -- obviously didn't perceive the intricacies. Appreciate your patience.

Regards,

tmc