Sorting records in a sub-form

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.

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John S" wrote in message
...
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,

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.


"Allen Browne" wrote:

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John S" wrote in message
...
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.

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/Ap****ueBadWord.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"John S" wrote in message
news
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.


"Allen Browne" wrote:

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John S" wrote in message
...
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.

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.

"Douglas J. Steele" wrote:

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/Ap****ueBadWord.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"John S" wrote in message
news
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.


"Allen Browne" wrote:

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John S" wrote in message
...
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.

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

__________________________________


"John S" wrote:

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.

"Douglas J. Steele" wrote:

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/Ap****ueBadWord.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"John S" wrote in message
news
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.


"Allen Browne" wrote:

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John S" wrote in message
...
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.