Ordering records

[Richard Hubbard]

I have a database containing music library data, with fields <Composer> and <Title>. I have a form that shows a these fields as lists, but I want to add a button at the top of each list to order the data by that field. How do I do it?

 

[Pat Hartman]

You don't need any code. Just place your cursor in the field you want to order by and press the A-Z or Z-A button in the toolbar.

 

[Richard Hubbard]

Well, I know you can do it that way, but I wanted to make it easier by having the sort buttons (or an equivalent) directly over the column, rather than at the top of the screen. Is this possible?

 

[pleeson]

This is some code from a DB I have written. Sorry if the code is a bit messy but this will work.

The code below applies to two buttons on top of each other. doorrefass and doorrefdesc.

Doorrefass is initially visible and when press sorts the column I place in the code. In this case Door_Refs. Once pressed the Doorrefdesc becomes visible.

Hope this helps you out.

EDIT: I forgot to mention that this applies to a continuous form.

Paul


'Sort functions - Doors Refs

Private Sub doorrefass_Click()

On Error GoTo doorrefass_Click_Err

DoCmd.Echo False, "Sorting records ..."

Screen.ActiveForm.AllowAdditions = False
DoCmd.GoToControl "Door_Refs"
DoCmd.RunCommand acCmdSortAscending

Me![doorrefdesc].Visible = True
Me![doorrefdesc].SetFocus
Me![doorrefass].Visible = False
DoCmd.Echo True, ""

Exit Sub

doorrefass_Click_Exit:
DoCmd.GoToRecord , , acFirst

Exit Sub

doorrefass_Click_Err:
Resume doorrefass_Click_Exit
End Sub

----------------------------------------------------------------

Private Sub doorrefDesc_Click()
On Error GoTo doorrefDesc_Click_Err

DoCmd.Echo False, "Sorting records ..."

Screen.ActiveForm.AllowAdditions = False
DoCmd.GoToControl "Door_Refs"
DoCmd.RunCommand acCmdSortDescending

Me![doorrefass].Visible = True
Me![doorrefass].SetFocus
Me![doorrefdesc].Visible = False
DoCmd.Echo True, ""
Exit Sub

doorrefDesc_Click_Exit:
DoCmd.GoToRecord , , acFirst

Exit Sub

doorrefDesc_Click_Err:
Resume doorrefDesc_Click_Exit
End Sub

 

[Richard Hubbard]

Thanks for that. I'll try it out when I get a moment.

Richard