Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Automatic resorting data (XP)

    I'm designing a DB to track motor vehicle mileage, fuel consumption, maintenance, etc. I plan to use the vehicle's mileage as the primary key (the mileage number will always be different and therefore unique). I'll be entering data as time allows, and sometimes I'll have to enter a new record that is older than the previous record. I'll want to use a form to do the data entry, naturally, and I'd like this system to chronologically resort the data automatically as soon as I finish populating the fields for the new record. I've seen this sort of thing at work in Microsoft Money--I enter a check that predates the current date, and as soon as I press Enter, Money resorts the data to display checks in the proper order. I'll bet there's a snippet of code I can use to make this work--can someone suggest where to look for such code?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Automatic resorting data (XP)

    Assuming that you are using a continuous form, all you need to do is a Requery on the form, and it will resort the data in the manner specified in the data source for the form.
    Wendell

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

    Re: Automatic resorting data (XP)

    Although this is possible in Access, I don't like the effect. Having the records in a form sorting themselves as they are being entered makes the form seem "jumpy", and is disconcerting to the user. I would recommend not sorting the records on the fly. The records will be in the correct order the next time the form is opened, and if the user wants to sort them after entering a few records, that is possible of course.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic resorting data (XP)

    One other thought... Consider not using the mileage as the Primary Key. There have been frequent posts by the lounge-meisters that highly recommend the primary key not be "real" data. Instead, have a separate field that is defined as an AutoNumber data type as your Primary Key. I don't know exactly what will be going into your database, but I could envision an oil change and fuel fill-up happening at the same time (same mileage), but you'd want separate records for each -- hence the mileage wouldn't work as a primary key. Also, if down the road (so to speak), you add linked tables to your database (linked through the mileage) and you later find out that one of your mileages is wrong and needs correcting, this will reek havoc with your table links.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Automatic resorting data (XP)

    Thanks, Wendell. Truth is, I hadn't considered using a continuous form, although that makes sense. Just monkeying around with the idea, I built a basic table and then inserted an AutoForm for data entry. I switched it to Continuous Form, and I'll keep playing with it.

    I've seen many references to "requerying" but I'm a bit unclear on how to do this. Let's say I'm using a continuous form and I've entered several new records. How do I "do a Requery on the form"? Just press F5 or something? Close and reopen the form? (That seems a bit clunky.) Maybe add a command button with code to execute the requery?

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Automatic resorting data (XP)

    Very good point! Thanks for that tip! Mileage seemed like a great idea for a primary key, but it could lead to problems, as you point out.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Automatic resorting data (XP)

    Hm-m-m...that's a valid point, Hans. I suspect that I'll be the only user for a long while, and I don't think I'll mind a little jumpiness. On the other hand, you're quite right: All that stuff will get resorted when I reopen the form next time. I suppose I can best set the sorting order in the table itself, yes?

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

    Re: Automatic resorting data (XP)

    Create a query based on the table, and set the sort order there. Use the query as Record Source of the form.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Automatic resorting data (XP)

    Thanks again, Hans!

Posting Permissions

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