Why can't I sort on the Column?

[bhammer]

A2003
Datasheet view form bound to a table. First column is bound to primary key
ID and I cannot sort by it (context menu is greyed-out for
Ascending/Descending), Why? I can open the table and sort on that ID column,
but not in the form. In the form all other columns are sort-able.
what's going on?

 

[GBA]

check that the form's properties:
Allow Additions
Allow Edits
Allow Deletions

set to 'no' ?? may be cause

 

[bhammer]

No, that's not it. I have:

AllowEdits— Yes
AllowDeletions— Yes
AllowAdditions— Yes
DataEntry— No

Strange. I read in Help that calculated fields and memo fields cannot be
sorted on. But this a plain old ID field. . .

 

[bhammer]

I found it--the stinkin' Tab property! I had it set to No because I wanted
the focus to go the next column on open. Well, who knew that with Tab set to
No it also disables the ability to Sort by that column.

You cannot sort on a field whose Tab property is set to No.

Go figure. . .
-bhammer

 

[GBA]

wow - I had no idea....thanks for posting your solution as I'll remember next
time...

 

[Dirk Goldgar]

I found it--the stinkin' Tab property! I had it set to No because I wanted
the focus to go the next column on open. Well, who knew that with Tab set
to
No it also disables the ability to Sort by that column.

You cannot sort on a field whose Tab property is set to No.

I am not able to reproduce this. When you say "Tab property", do you mean
"Tab Stop property"? There is no "Tab" property. But I took a datasheet
form, changed the Tab Stop property of the text box bound to the table's
autonumber primary key, and was able to sort on that field with no problem.
Maybe I am misunderstanding you, or maybe there's some additional
contributing factor. Is there anything else unusual about your form at all?

 

[bhammer]

Dirk,

Nothing unusual about the table or form that I can see.

I was able to reproduce it using a different table with only two fields; I
used the Form Wizard to make a form based on the table; I changed the Tab
Stop property of the textbox bound to the autonumber ID field to 'No' (all
other properties are defaults); then changed the view to Datasheet; right
clicked on the ID textbox to get the default context menu--and BAM--the two
Sort commands are greyed-out. In Form View they are NOT greyed out.

Apparently this only has to do with the Form Datasheet view. Also maybe
specific to Access 2003 with mdb's in Access 2000 format. That's I'm using.
WinXP.

Strange one!

-Brad

 

[Dirk Goldgar]

Dirk,

Nothing unusual about the table or form that I can see.

I was able to reproduce it using a different table with only two fields; I
used the Form Wizard to make a form based on the table; I changed the Tab
Stop property of the textbox bound to the autonumber ID field to 'No' (all
other properties are defaults); then changed the view to Datasheet; right
clicked on the ID textbox to get the default context menu--and BAM--the
two
Sort commands are greyed-out. In Form View they are NOT greyed out.

Apparently this only has to do with the Form Datasheet view. Also maybe
specific to Access 2003 with mdb's in Access 2000 format. That's I'm
using.
WinXP.

Strange one!

Interesting. I still can't reproduce it, even following your steps as best
I can. I'm using Access 2003 SP3, and the database I'm testing with is in
Access 2000 format. Do you have all service packs applied? Im running
under Vista, not XP, but I don't think that's likely to be the reason for
the difference.

If you'd like to send me a sample database to examine, you can get my real
e-mail address from my website, which is listed in my sig below.

 

[GBA]

plz post your results.

I have a form in Access02/XP where the sort is greyed out. I haven't been
able to figure out why.

 

[bhammer]

Dirk, GBA,

I sent a test mdb to Dirk's email per your request.

I started from scratch with new mdb in Access 2003 SP3 (11.8204.8221) under
WindowsXP SP3 Build 2600 version 5.1.2600. I made one table in design view
with two fields: ID (Autonumber), and (Field Text). Then using the Make Form
Wizard, selected the table and all fields. Then the only thing I changed in
the form design was the Tab Stop property on the Other tab. Changed it from
default Yes, to No. All others props are default. In Form View the Sort
commands are enabled on the ID field's context menu, but with the form in
Datasheet view, the sort commands are disabled (grayed-out). Same as before.

 

[Dirk Goldgar]

Dirk, GBA,

I sent a test mdb to Dirk's email per your request.

I started from scratch with new mdb in Access 2003 SP3 (11.8204.8221)
under
WindowsXP SP3 Build 2600 version 5.1.2600. I made one table in design view
with two fields: ID (Autonumber), and (Field Text). Then using the Make
Form
Wizard, selected the table and all fields. Then the only thing I changed
in
the form design was the Tab Stop property on the Other tab. Changed it
from
default Yes, to No. All others props are default. In Form View the Sort
commands are enabled on the ID field's context menu, but with the form in
Datasheet view, the sort commands are disabled (grayed-out). Same as
before.

Brad -

This is interesting. I received your database and tested it. I have no
problem sorting by the ID field in datasheet view. The sort commands are
enabled in the ID field's context menu, and they work fine -- for me.

I, too, am running Access 2003 SP3 (11.8204.8221). However, I am running
Vista Ultimate SP2, not XP. I guess that could be the cause of the
difference, but if so, it's an odd one. I do have the post-SP3 hotfix
applied: http://support.microsoft.com/kb/945674 . Do you have that one?

 

[GBA]

In my case it is Access02/xp. Continuous form. When I right click inside a
text box in order to sort a column everything is greyed inactive except: Cut,
copy, properties

 

[Dirk Goldgar]

In my case it is Access02/xp. Continuous form. When I right click inside
a
text box in order to sort a column everything is greyed inactive except:
Cut,
copy, properties

Is this in an MDB file, or in an MDE? Is the form updatable; that is, can
you edit records in it? What are the values of the form's Allow Filters,
Allow Edits, and Recordset Type properties?

 

[GBA]

* mdb
* form is updateable/editable
* Allow Filters was set to 'No' at last message; reset to 'Yes' - activates
Filter options but A-Z sorts are still greyed out
* Allow Edits is 'Yes'
* Recordset Type is 'Dynaset'
b.t.w. if I use 'Records' from the tool bar at top ; the A-Z sort is greyed
out there as well, same as right click when in a text box

In my case I have the table's key field in the query/record source and on
the form. It was set to TabStop 'Yes' but was not Visible. Change to
visible - and no change to sort issue. So I have not been able to resolve as
did bhammer.

I will add that this form could sort at one point. Somewhere/sometime in
design mods of either the form or the underlying record source - it changed
to not sortable.

What is odd to me, in the theoretical sense - is that any record set - even
if not updateable nor editable nor filterable - should be sort-able. So that
it seems like a very explicit not-sort property would need to be in affect
for the form alone - - as I am able to sort the underlying query record
source when opened stand alone....

 

[bhammer]

Dirk: Yes I have post-SP3 Hotfix applied some time ago.

I thought I might try converting from a 2000 format mdb, to the 2000-2003
format mdb, but no difference.

GBA: When I view in Continuous Forms view the sort IS enabled--you said this
is Not enabled for you (on the context menu). This is a difference between
your issue and mine. You are in Access2000 (I have Access2003 installed as
part of Office2003Pro on a Vista-Capable PC, but bought it with XP installed).

Dirk: My form's properties are all default except for Tab Stop. The Data tab
says, Allow Filters: Yes; Allow Edits: Yes; Allow Deletions: Yes; Data Entry:
No.

Anther test I did was to try it on an mdb I did NOT create. I used a
Microsoft sample database (Issue Tracking, in 2000 file format). I opened the
Categories form, changed the Tab Stop property to No. The form has only one
field, Categories, which is not AutoNumber, but Text, and is also the primary
key field. Same deal. In Datasheet View, the context menu shows greyed-out
sort commands.

Also (and I never checked this previously) the Sort commands on the standard
Access Menu and Form View Toolbar are grey with the column selected. AHA! But
not with the cursor in one of the fields in the column! Same for context menu.

HERE'S THE ANSWER: We may have been doing this differently between us. I
have always sorted by right-clicking on the column header. Try that, Dirk. If
that field happens to have the Tab Stop property set to No, then by selecting
the entire column by clicking the header disables the Sort commands. You can
nevertheless sort by clicking in one of the fields, rather than on the column
header. This is still and odd behavior.

 

[Dirk Goldgar]

Also (and I never checked this previously) the Sort commands on the
standard
Access Menu and Form View Toolbar are grey with the column selected. AHA!
But
not with the cursor in one of the fields in the column! Same for context
menu.

HERE'S THE ANSWER: We may have been doing this differently between us. I
have always sorted by right-clicking on the column header. Try that, Dirk.
If
that field happens to have the Tab Stop property set to No, then by
selecting
the entire column by clicking the header disables the Sort commands. You
can
nevertheless sort by clicking in one of the fields, rather than on the
column
header.

Yes, you are right. Although I seldom use datasheet view at all, when I do
sort a datasheet, it's my habit to click on one of the fields, not the
column header. When I do what you now describe, and click on the column
header, I see what you see: the sort commands are disabled if the field
isn't a tab stop.

This is still and odd behavior.

Agreed. I don't see the same behavior in Access 2007. I no longer have any
earlier version of Access installed, so I can't check it in 97 - 2002.

 

[Dirk Goldgar]

* mdb
* form is updateable/editable
* Allow Filters was set to 'No' at last message; reset to 'Yes' -
activates
Filter options but A-Z sorts are still greyed out
* Allow Edits is 'Yes'
* Recordset Type is 'Dynaset'
b.t.w. if I use 'Records' from the tool bar at top ; the A-Z sort is
greyed
out there as well, same as right click when in a text box

In my case I have the table's key field in the query/record source and on
the form. It was set to TabStop 'Yes' but was not Visible. Change to
visible - and no change to sort issue. So I have not been able to resolve
as
did bhammer.

I will add that this form could sort at one point. Somewhere/sometime in
design mods of either the form or the underlying record source - it
changed
to not sortable.

What is odd to me, in the theoretical sense - is that any record set -
even
if not updateable nor editable nor filterable - should be sort-able. So
that
it seems like a very explicit not-sort property would need to be in affect
for the form alone - - as I am able to sort the underlying query record
source when opened stand alone....

Is it safe to assume that the text boxes you're trying to sort on are, in
fact, bound fields in the form's recordsource?

I don't think you have the same issue that bhammer was having, but if you'd
like to send me your database so I can poke at it, you can get my e-mail
address from the website listed in my sig, below. Please compact it and zip
it to minimize the download time. Also, remember not to post my real e-mail
address in the newsgroup.

 

[bhammer]

I don't see my post from this morning, so briefly again . . .

After more testing with the same results, I stumbled onto something. All
along I've been clicking on the column header, as opposed to clicking in one
of the fields in the column. That's it! I can view the Sort commands if I
click in the field--I cannot view the Sort commands if I select the column
(i.e. click the header). Simple.

This must be a bug for a field with Tab Stop set to No and in Datasheet
View. I see no helpful reason to have this restriction on purpose.

GBA, Dirk, try this on yours and see if you get the same behavior.

 

[Dirk Goldgar]

I don't see my post from this morning, so briefly again . . .

I replied to that post, to confirm that I'm getting the same behavior. I
wasn't clicking on the column header before.