1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access form ignoring "Order by"

Discussion in 'Business Applications' started by DKTaber, May 16, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    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?
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    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?
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    In what way do the Users "do to screw things up"?
    Does a Sorted Query work, as I do not base Forms on Tables?
     
  4. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    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.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    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?
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    What affect does a Compact & repair have?
     
  7. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    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?
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    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.
     
  9. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    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.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/997222

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice