# Access form ignoring "Order by"



## DKTaber (Oct 26, 2001)

I put a similar post on TSG in July 2008, but never received an answer specific to my question.

Situation:

Using Access 2000
Input form that uses a table (not a query) as the data source
"Order By" property set to sort by name -- last, first, middle, suffix
Some computer-illiterate people tried to add records to the database
Form subsequently displayed records in *ID* order, not by name
Deleted "Order By" entries and saved form
Reopened form and inserted Order By using last, first, middle, suffix
Form still displays records in ID order
Opened form and switched to Datasheet View
Highlighted LN, FN, MI and SUFX columns and clicked the "AZ" icon to order ascending
Form now displays records in name order
I have run into this problem numerous times and have had to solve it each time by switching to datasheet view and doing an ascending sort. The Order By property does not appear to work, at least not in this version of Office. Seems to me that the Order by property should control the order regardless of what the idiot inputters do to screw things up. Why doesn't it?


----------



## Ziggy1 (Jun 18, 2002)

I have 2000 installed on this PC and the order by works for me, I might have to see a copy of what you have....if you can upload? Just make sure no personal data.

I tried to do everything as you said it but it still sorts. Is your database set to compact on close?


----------



## OBP (Mar 8, 2005)

In what way do the Users "do to screw things up"?
Does a Sorted Query work, as I do not base Forms on Tables?


----------



## DKTaber (Oct 26, 2001)

OBP said:


> In what way do the Users "do to screw things up"?
> Does a Sorted Query work, as I do not base Forms on Tables?


Tony: I don't know exactly what they did because I wasn't in the office when they did it. However, I was recently sent a copy of the latest tables and found that the one in question contained 3 blank records and a 4th that was almost blank. Thru some detective work, I discovered that the 4th almost-blank record belonged to a legitimate person. Someone had deleted his title; first, last, middle names; address; city; state; zip code (why?. . .haven't the foggiest). I was able to find out who it was only because he had made a contribution to the organization in August 2009, and restored his record.

That the inputters were able to do this is my fault. This is a brand new database. The original data came from a collage of old spreadsheets going back to 2005. They were inconsistent and generally a mess, and some records were missing first or last names. For that reason, I could not initially make first and last name REQUIRED. As of a couple of months ago, we had tracked down and entered all the first and last names, but I did not IMMEDIATELY change those fields to *required *(shame on me). Last week, I deleted the 3 blank records and made the first and last names required, so it is now impossible to enter no-name records or remove the first or last names from any record!

As for the *Order By* not working. . . I still don't know what's wrong with that property. I didn't mention this in the initial post, but one of the things I tried was sorting the table by last/first/middle/suffix and saving it. It did not change the order in the form (remained by ID). I'm aware that the property you set for a field in a form takes precedence over the properties it has in the table. It would be logical to assume that setting the form's sort order would also take precedence. . . but it obviously doesn't.

Now that I have vented my frustration with Access, I will create a query that consists of every field in the table, sort it by the name fields, and make that the form's data source.


----------



## OBP (Mar 8, 2005)

Don, assuming it wasn't a malicous event it sounds like they thought they were creating new records by overwriting the old ones instead of moving to a new record.
Does the main menu AZ sort icon sort the form?


----------



## OBP (Mar 8, 2005)

What affect does a Compact & repair have?


----------



## DKTaber (Oct 26, 2001)

OBP said:


> What affect does a Compact & repair have?


I tried C & R before doing anything else. Didn't do anything.

Didn't try the AZ, but it would probably have worked. Problem with that function is you can only do one field at a time, so I never use it.

The issue is not whether other methods of sorting the records in the form work or not (they do), but what causes *Order By* -- which is hard-coded in the form's properties -- to be ignored?


----------



## OBP (Mar 8, 2005)

That is what I am trying to establish, could you send me a copy of the original table & form that has the problem?
Normally a C & R rewrites the Table's Indexes and should fix the problem, but without the problem I am not sure what could cause it or fix it.


----------



## DKTaber (Oct 26, 2001)

OBP said:


> That is what I am trying to establish, could you send me a copy of the original table & form that has the problem?
> Normally a C & R rewrites the Table's Indexes and should fix the problem, but without the problem I am not sure what could cause it or fix it.


I would send you the table and form if the problem still existed. It doesn't. When you switch to datasheet view and sort the records, it automatically enters that sort into the Order By property (something I became aware of only yesterday). So now the form is sorted the way it should be and the Order By property reflects that sort.

Now that the form is "fixed", I observe that if the table is the data source, the form sorts according to what I put into the Order By property (makes sense; form format takes precedence over table format). I can change from "DMMemVol.LN" to "DMMemVol.LN DESC" and the form sorts by the last name in descending order. Remove the DESC and it immediately reverts back to LN in ascending order. But if a query is the data source and it's sorted in ascending order by LN/FN/MI/SUFX, the Order By is ignored. I.e., if I change it to LN DESC, it is still sorted by LN ascending. So if a query is the data source, it takes precedence; if a table is the source, Order By trumps the table.


----------

