Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Form: Record Order by decending order.

    I want my form to open up showing me the latest record (last record) which was created.

    I have a filtered form.

    On open the form will present all the records relating to the filter.

    In the properties of the form I have:
    Record source: cJSDetails3Placement
    Filter: [MO_ID] = 190 'this is dependant on what MO_ID record was chosen
    Order by: cJSDetails3Placement.PlacementRequestID DESC


    I have also tried having in the On Open Property of the form an event Procedure where I have tried both of the following codes to try to achieve what I want:

    'Move to last record
    DoCmd.GoToRecord , "", acLast

    'Sort form by PlacementRequestID in decending order
    Me.OrderBy = "cJSDetails3Placement.PlacementRequestID DESC"

    Unfortunately neither works.

    I cant seem to get it to open showing the latest record created.

    Can anyone suggest what may be the problem and how to correct this?

    Thanks heaps.

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    since it is not working, and it should be - I would recommend that you remove your filter, remove your orderby properties - everywhere they occur and get back to baseline...

    you need a sanity check; try one or both of these:

    a. with the form open, put your cursor in any textbox of the column you want sorted; right click and select the Sort Descending Z-A... which should put it in reverse order...

    work?

    b. is the form sourced on a query? set up the sort using the query...

  3. #3
    Join Date
    May 2002
    Posts
    157
    Thanks NTC for replying. I really appreciate it.

    I had already tried your suggestion to remove your filter, remove your orderby properties - every where they occurred and to get back to baseline and then (a.) to no avail.

    My Form source was directly connected to the table.

    So as per your suggestion (b.) I have created a query and sorted it in decending order, then made the query the source of the Form.

    And it now works.

    I have no idea why it wont work the other way.

    Thank you once again.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I find the Filter to be problematic in some way or another (whether it be going to the last record in a filtered recordset or even just filtering and ordering correctly), especially when it comes to filtering on a field which has data that may have non-alpha type (ie. ', " $ %....) characters in it. This may or may not be your case but it's one reason I don't like using the filter (I personally haven't used it in many, many years.) A bad date value is especially problematic (when a date get's somehow entered/imported as 12/09 versus 12/23/09 and you try to filter on that date field.)

    The other reason is because it tends to be slower for large recordsets. If you have subforms within your form (or even expressions/equations or other events on your form), this tends to even be more slower. I imagine that changing the order on a filtered recordset, would probably also be much slower.

    I see a lot of posts on this forum where using a Filtered recordset is the problem in some way or another.

    NTC's advice to use the ordering in your actual select statement for the recordsource is my preferred method since it is the fastest and typically non-problematic.

    As far as going to the last record, once the recordsource is set to open with the correct field being sorted by within the query, I then utilize the docmd.gotorecord,,aclast or docmd.gotorecord,,acfirst. If this tends to cause problems, I might look at any code which fires in the OnCurrent event of the form (or subforms)

    But I also usually open a form based on criteria so it only returns 1 record versus many records. This is extremely fast when working with large recordsets and causes the least problems, especially with subforms, expressions, and other events firing on that form. I think if you open a form using criteria to open only 1 record versus many, you'll notice a very fast difference (just my recommendation.) The same is true for any subforms. Subforms should have criteria in them so that it the recordID or primary key field =Forms!MyMainForm!RecordID. Otherwise you're also loading all the records within the subform and MSAccess has to parse which record(s) it's supposed to be on to match the main form.

    Also, just an FYI, using the Filter tends to be problematic when upsizing to a new version of MSAccess. I think MSAccess keeps trying to fix something or another with the filter and upgrading to a newer version always seems to cause problems. For example, I have a few 2003 apps that I'm trying to upgrade to 2007. When the 2007 users try to utilize the filter command (these apps were designed by my predeccessor), it crashes the app (although it could be due to other reasons but it's happened on quite a few apps now when the Filter command is applied.)

    Here are a few 'General' tips I put in the code bank if you're interested:
    http://www.dbforums.com/6356210-post76.html
    http://www.dbforums.com/6366815-post77.html
    Last edited by pkstormy; 11-28-09 at 14:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    May 2002
    Posts
    157
    Thanks pkstormy you have certainly put a lot of work into this post.

    Unfortunately for me, it has made me think about how I have written my database.

    Keep happy and safe.

    Karen Day

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sorry I can't help you more Karen Day. I'm doing my best to keep up with the posts.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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