Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort records on an unbound form (2000/2007)

    I have a database in 2000 format being run with the backend on the server and the frontend on the desktop. They have recently upgraded to 2007 but the database remains in 2000 format.

    One of the forms is very complicated and accesses a large recordset and in a previous post we came up with making the form unbound and setting recordsources on load. The item in question uses the following:
    Me!frmDep.Form!frmISAPSubMeetings.Form.RecordSourc e = "tblISAPMeeting"

    Last week, I got a call that records for meetings were no longer sorting chronologically. The order showed no logic I could grasp. With one record with 50+ meetings dating back 3 years, the first meeting displaying was the most recent (late June) then meeting #2 was the very first meeting in 2004 and and they proceeded in chronological order from there. There was an orderby in the table properties which I removed while the backend was still on the server to no avail. I eventually discovered that if I took the backend off the server, fixed it on a desktop and then put it back, it was OK.

    2 Questions:
    1) is there a way to set the order in VBA when the recordsource is set so that even if the table is flaky, the records will be in chronological order? I suppose I could use a query, but I'd rather avoid that if I can.
    2) Any speculation as to why I could fix it on a desktop but not over the network? Has anyone else encountered this little quirk before and is it 2000 or 2007 related?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort records on an unbound form (2000/2007)

    Are the dates unique? If so, you could set the date field to be the primary key of the table. Records should then be sorted by date automatically.
    Or you can create a query that sorts the records by date and set that as record source (I don't understand why you want to avoid that).
    Or use

    Me!frmDep.Form!frmISAPSubMeetings.Form.RecordSourc e = "SELECT * FROM tblISAPMeeting ORDER BY [DateField]"

    where DateField is the name of the date field.

    (I don't have Access 2007 myself)

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort records on an unbound form (2000/2007)

    Thanks Hans. That gives me the info I need.

    I don't want to deal with a query because there is so much code all over in this database I have concerns about changing one thing and having something else break. Not too mention there are already reams of queries and the interface for 2007 is a big old pain in navigating through them.

    I can't change the primary key and date wouldn't work anyways. About 600 new meetings are recorded by about 12 workers every month. Primary key is an autonumber. There are about 30,000 meetings currently in the database.

    The simple solution is the SELECT statement. Why this cropped up all of a sudden when it has been working fine for 2 years and fine (mostly...) in 2007 since January is beyond me.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort records on an unbound form (2000/2007)

    In a relational database, the records in a table aren't stored in any particular order, so you cannot rely on the records being in the desired order. It may work correctly for a while, but it's not reliable. That's why you need a query or SELECT statement with an ORDER BY clause.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort records on an unbound form (2000/2007)

    That is a given and I understand that, but in the absence of any OrderBy statements in the properties of a table doesn't a table sort itself by its primary key if it is an autonumber? I have certainly never seen this behaviour before and why would it crop up all of a sudden?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort records on an unbound form (2000/2007)

    Normally, the records would be returned sorted by the primary key if you don't specify another sort order explicitly. But I never rely on that, I always specify the sort order, just to prevent the kind of behavior you experienced. (I don't have an explanation why it suddenly changed)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •