Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Limiting # of records

    Access 97 - I have clients and client transactions. Frequently I need a statement (report) for only one client. I have a button in the transaction form that previews the statement report for the single person. However, when that person has made many transactions, I want the report limited to the most recent 10 transactions.

    I tried sorting by transaction date and limiting to the top 10 (using the topvalues control and editing SQL), but the result limits the most recent 10 transactions for all clients.

    I'm stumped and my head is getting sore from batting it against the wall. Help??
    Thanks

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting # of records

    Restrict the report to run from a query that selects only the client you are looking for, not the whole table and then the top values should work.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Limiting # of records

    I tried that but I don't know how to convince Access to select one client BEFORE it finds top 5. Right now the button on the statement runs code that limits to the single client. The top-5 is in the Report-source query. That seems as though the client limitation SHOULD come before the record limitation - but that's not how it works.

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

    Re: Limiting # of records

    What is in the code behind your button? Something like this should work:

    <pre> Dim stDocName As String

    stDocName = "rptTest"
    DoCmd.OpenReport stDocName, acPreview, , _
    "[TestID]=" & Me![txtTestID]</pre>


    In this example, TestID is the name of the field you want to use in the where condition, and Me!txtTestID represents the control on the current form that contains the value you want to use. Passing the where condition to the report automatically limits the recordset behind the report to only the records that match.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Limiting # of records

    Yes, my code is much as you wrote and it does limit the report to the one client. My question was, when there are multiple transactions for that client, how do I limit the details (transactions) to the 10 most recent ones? I attempted to do so with TopValues, but could never get TopValues to apply to the transactions of the current client. Instead, it applied to top transactions of ALL clients.

    Thanks

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

    Re: Limiting # of records

    In that case, you might be better off using automation to open the report and then setting its recordsource to the SQL statement that would return the recordset for that client including the TopValue argument.
    Charlotte

Posting Permissions

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