Sorting records in a sub-form

[John S]

Hi,

Thanks to Clifford, Armen and Geoff for the recent advice. I have taken-up
all the suggestions made and the db works well with one ‘People’ table and a
‘Status’ field.

I have come across another teething problem. I want to sort records in
ascending order by a certain field on a subform when the user clicks a
Command button on the main form.

I have set the Command button to run the following code:

“Private Sub Command19_Click()
On Error GoTo Err_Command19_Click

Forms!Events!Volunteers.Form!Name.SetFocus
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 1, 0, acMenuVer70

Exit_Command19_Click:
Exit Sub

Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click

End Subâ€

I get an error message saying that the “command or action ‘sort ascending’
isn’t available nowâ€. I believe this is because the command button has
retained the focus.

I should be grateful if anyone can advise me of a better way to do this.

Regards,
John.

 

[Allen Browne]

Set the OrderBy property of the subform, e.g.:

With Me.Volunteers.Form
.OrderBy = "SomeField"
.OrderByOn = True
End With

 

[John S]

Allen,

Many thanks - this seems to work...

BUT - not for a ComboBox on the sub-form?

The ComboBox is called "Name" and is made up of Surname & FirstName.

John.

 

[Douglas J. Steele]

Try renaming the combo box. Name is a reserved word, and using reserved
words for your own objects can lead to issues.

Allen's actually got a great list of names to avoid (and a link to a free
utility to check your application for compliance) at
http://www.allenbrowne.com/AppIssueBadWord.html

 

[John S]

Doug:

Thanks for the link, its very helpful.

I had already tried renaming. Whatever name I use I get the "Enter Perameter
Value" prompt when the button is clicked.

Is this because of the Row Source of the Combo Box? Which is:
SELECT Personnel.PersonnelID, [Surname] & ", " & [Firstname] AS Expr1,
Personnel.Surname, Personnel.FirstName, * FROM Personnel ORDER BY
Personnel.Surname, Personnel.FirstName;

Regards,
John.

 

[boblarson]

To change the sort order of a combo box you will need to modify the RowSource
query and then reassign it.
--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

Doug:

Thanks for the link, its very helpful.

I had already tried renaming. Whatever name I use I get the "Enter Perameter
Value" prompt when the button is clicked.

Is this because of the Row Source of the Combo Box? Which is:
SELECT Personnel.PersonnelID, [Surname] & ", " & [Firstname] AS Expr1,
Personnel.Surname, Personnel.FirstName, * FROM Personnel ORDER BY
Personnel.Surname, Personnel.FirstName;

Regards,
John.

Try renaming the combo box. Name is a reserved word, and using reserved
words for your own objects can lead to issues.

Allen's actually got a great list of names to avoid (and a link to a free
utility to check your application for compliance) at
http://www.allenbrowne.com/AppIssueBadWord.html