Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Doesn't remember sort order (Access 2000)

    Hi,
    On a subform in my database, I don't understand why Access doesn't keep the order in which I entered the information. In the attached example, under the "date" field, I used to only enter the invoice date for invoices and a paid date for payments. Since the database didn't keep the order, I decided to enter the paid date in both places and sort by "date". It works for the time I have the database open, but when I close it and reopen it at a later time, the sorting goes back to the way it was. The strange thing is that some of the records sort properly and others don't . I have not been able to figure out why. What am I doing wrong?

    Thanks!
    Louise

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doesn't remember sort order (Access 2000)

    You should use a query as recordsource for the subform and set the sort order you want in that query.
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doesn't remember sort order (Access 2000)

    Hi Francois,

    Thanks! I'll try that!
    Louise

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doesn't remember sort order (Access 2000)

    Hi Francois,
    I changed the recordsource to the query which solved the problem of sorting beautifully but now I can't add any more records in the form. Is this because the recordsource is now a query instead of a table? If so, I need to change it back because it is what I use to enter the data.

    Thanks
    Louise

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Doesn't remember sort order (Access 2000)

    Access doesn't care about the order you enter the data, since record order is meaningless to the database. If you use the menu or toolbars to "sort" the form or subform, that applies only to the current session in that form or subform. If you want the sort order to be permanently set, you must set the sort order of the underllying query. If you post the SQL you're using for the datasource of your subform, one of us can help you get it sorted out.
    Charlotte

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doesn't remember sort order (Access 2000)

    Can you add records directly in the query ?
    If not, you have to rework your query so that you can add records.
    If you have problems with this, like <!profile=Charlotte>Charlotte<!/profile> say, post the sql or attach a stripped version of you db so that somebody can have a look at it.
    For instructions how to get it under the 100k limit see <!profile=HansV>HansV<!/profile>'s <post#=401925>post 401925</post#>
    Francois

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doesn't remember sort order (Access 2000)

    Hi!
    I'm sorry, but I don't know what "SQL" is or else I'd give it to you. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> Let me see what I can find. I'm thinking of making a copy and then deleting that sub-form and make another from scratch using the query. Maybe that will work.
    Louise

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Doesn't remember sort order (Access 2000)

    If you open the query in design view, the View menu item offers you a SQL view. Select that and you will see the query language that produces what you see in the grid. Use your mouse to select the entire SQL statement and paste that into a post here.
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doesn't remember sort order (Access 2000)

    Thank you!

    This is what the SQL View says on the query:

    SELECT Invoice_Payments.PaymentID, Customers.CustomerName, Invoice_Payments.[SalesPO#], Invoice_Payments.InvDate, Invoice_Payments.[Inv#], Invoice_Payments.InvAmt, Invoice_Payments.Paid, Invoice_Payments.Amt, Invoice_Payments.Notes
    FROM Customers INNER JOIN (Orders INNER JOIN Invoice_Payments ON Orders.[SalesPO#] = Invoice_Payments.[SalesPO#]) ON Customers.CustomerID = Orders.CustomerName
    ORDER BY Invoice_Payments.PaymentID;

    Hope that helps!
    Louise

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

    Re: Doesn't remember sort order (Access 2000)

    Do you need the Customers and Orders tables in this query? I would think that the subform only needs Invoice_Payments.

  11. #11
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doesn't remember sort order (Access 2000)

    Hi Hans,
    For this purpose, no.
    As you can see in my earlier post, I originally wasn't using a query at all for my subform. My recordsource was originally:

    SELECT [Invoice_Payments].[SalesPO#], [Invoice_Payments].[InvDate], [Invoice_Payments].[Inv#], [Invoice_Payments].[InvAmt], [Invoice_Payments].[Paid], [Invoice_Payments].[Amt], [Invoice_Payments].[Notes] FROM Invoice_Payments;

    So I can always redo the query since I'm not currently using it for any reports.
    Thanks!
    Louise

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

    Re: Doesn't remember sort order (Access 2000)

    Try this:

    SELECT [Invoice_Payments].[SalesPO#], [Invoice_Payments].[InvDate], [Invoice_Payments].[Inv#], [Invoice_Payments].[InvAmt], [Invoice_Payments].[Paid], [Invoice_Payments].[Amt], [Invoice_Payments].[Notes] FROM Invoice_Payments ORDER BY Invoice_Payments.PaymentID;

    You can use this directly in the Record Source property of the subform, or you can create a query with this SQL string, and set the Record Source to the name of the query.

  13. #13
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doesn't remember sort order (Access 2000)

    Hans,
    As usual you're brilliant! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> Worked perfectly.

    Thanks to all. I learn so much from this group!
    Have a great week.
    Louise

Posting Permissions

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