Sort records in Access

[sail87]

I inherited an Access 2003 database and need to change the way a form is sorted. It seems to work like this. There is a table containing work records of work requests. The user fills out a form requesting the work and it is assigned a number using the autonumber feature and the date is also put in automatically, then it is entered in the table. There is another form that other users use to request all records designated as new. The form, via a macro, returns all the records. Somehow it stopped sorting by the autonumber. I tried selecting different fields while the form was in run mode (not design view) but could not select the fields date or autonumber. I can only sort on fields that contain user entered data. Does anyone know how to change this so it always sorts on either autonumber(preferably) or date? When I run a query against the table it sorts by these fields fine, but I don't want to rebuild the whole thing and would rather get the form to work as it used to when called by the macro.

Thanks for your help.

[puppydogbuddy]

I inherited an Access 2003 database and need to change the way a form is sorted. It seems to work like this. There is a table containing work records of work requests. The user fills out a form requesting the work and it is assigned a number using the autonumber feature and the date is also put in automatically, then it is entered in the table. There is another form that other users use to request all records designated as new. The form, via a macro, returns all the records. Somehow it stopped sorting by the autonumber. I tried selecting different fields while the form was in run mode (not design view) but could not select the fields date or autonumber. I can only sort on fields that contain user entered data. Does anyone know how to change this so it always sorts on either autonumber(preferably) or date? When I run a query against the table it sorts by these fields fine, but I don't want to rebuild the whole thing and would rather get the form to work as it used to when called by the macro.

Thanks for your help.

Try placing this code in the load event of the form: Replace "YourAutoNumberField" with the fields actual Name

Private Sub Form_Load()
 
On Error GoTo ErrorHandler
 
         Me.OrderBy = "YourAutoNumberField"   
 
         Me.OrderByOn = True                        ' Apply the sort order.
 
   Exit Sub
 
ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

[sail87]

Thanks for your help, that worked!

[ebs57]

I was not able to sort a report by date, either. My SQL was set up to sort a report based on a document name and the date but would only sort by the document name:

SELECT ID, Name, Date
FROM Documents Doc
WHERE Name <> ""
ORDER BY Doc.Name, Doc.Date;

My report output looked sumtin like dis:

Document 100 5/5/2007
Document 200 6/4/2007
Document 300 5/28/2007

I eliminated the ORDER BY clause from my SQL and played with the report's "Sorting and Grouping" button and that produced the same results. I then swapped the date and name ordering and it worked. For some reason I had to have the date sorted first then the document name. As usual, I don't understand why this worked but in my case whatever combination of keypresses makes database go me happy (or at least ameliorated).