Command or Action "SortingAndGrouping" isn't available now

[G-Man]

On some forms in a database, I get this error when I try to apply a sort
order to the form.

Other forms I can do this fine.

The rest of the error diologue says:
--You may be in a read-only database or an unconverted database from earlier
version or
--The type of object the action applies to isn't currently selected or isn't
in active view.

The form can be edited in design view just fine. It's an Access 97 form
being edited in Access 97. It's not read-oly either.

As far as the second bullet . . . I open the form in design view, click on
the field I want to sort (in one form it's a date field . . . but it happens
in another form as well, in a text field) and the error pos up.

Database has been compacted and repaired as well. Same error.

What am I missing??

 

[Allen Browne]

That's weird.

How are you applying the sort:
- Code in a command button?
- Right-clicking a field?
- The built-in toolbar button?

The SortingAndGrouping dialog applies only to reports, so the 2nd point is
kinda relevant. However, the reason why this error would fire is unclear.

If there is code, post it. If not, perhaps we can talk you through getting
Access to recreate the form with the undocumented SaveAsText/LoadFromText.

 

[G-Man]

Apples only to reports? This is not also available in forms?? Now I'm a bit
confused

 

[Allen Browne]

This code makes sense:
DoCmd.OpenReport "Report1", acViewDesign
RunCommand acCmdSortingAndGrouping
You open a report in design view, and then toggle the Sorting And Grouping
dialog.

But referring to the sorting'n'grouping dialog in the context of a form
would certainly give the error you described.

What I'm not clear about is what is triggereing this error message for you.

 

[G-Man]

You know Alan . . . .I certainly know the difference between forms and
reports! But I have many reports that are based on forms, and I think I was
getting mixed up. I have used this in Reports many times. But I was trying
to use it in a form, and never realized it worked in reports only. Duh.

All my forms are based on queries. But sort order in queries doesn't always
transfer to forms based on those queries, does it? So, the best way to make
sure the right sort order is applied to records appearing in a for is????

Sorry about the confusion!

 

[Allen Browne]

If you base a form on a query, it should follow the sort order specified in
the query, unless you have something in the OrderBy property of the form (or
in the OrderBy property of the query or the table.)

Forms are pretty consistent with that, whereas reports follow the order
specified in the Sorting'n'Grouping dialog.

Here's another trick altering the sort order of your reports, without having
to show the Sorting'n'Grouping dialog:
Sorting report records at runtime
at:
http://allenbrowne.com/ser-33.html

 

[G-Man]

Thanks Alan. I was told the same. So I have always used Sortingand Grouping
on reports. I guess I got mixed up because I have taken over many databases
created by others and the reoprts and forms they created are almost identical
and I was getting very mixed up. I have always based my forms on queries and
used the sort order in query, but I know reports dont reliably work with that
- - - I have never really used the Orderby because i base all my ofrms on a
unique query. I'll look at your link. Thank you so much for your assistance!

 

[G-Man]

Just looked at your link. What a gold mine! Thanks. I will refer to it
often, I'm sure.