Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2008
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting first 10 records (AccessXP)

    Hello. I have some code that selects and copies the results of a query as follows:
    RunCommand acCmdSelectAllRecords
    RunCommand acCmdCopy
    Is a simple way to just copy the first 10 lines from the query?

    I need to do this programmatically since I have hundred's of queries to run through.

    Thanks!

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

    Re: Selecting first 10 records (AccessXP)

    Welcome to Woody's Lounge!

    I don't see an easy way to do that for queries.
    You can create a top 10 query by setting the Top Values property of the query to 10 (and making sure that the query is sorted the way you want).

    In a form based on a query, you can set the SelTop property to 1 and the SelHeight property to 10. This selects the first 10 records in the form.

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting first 10 records (AccessXP)

    Hi Twinky!

    Of course Hans is correct. He's one of the most knowledgeable and helpful people around. But since you're new to the forum, I wonder if you perhaps haven't phrased your question correctly or supplied sufficient detail. You haven't, for example, told us what you want to do with the data once you've selected it.

    As an exercise, I've assumed you might want to move the data elsewhere; perhaps even into a new table. Here's some simple code that runs through all the queries in the database and for each query, creates a new table holding the top ten records.
    I've deliberately left out docmd.setwarnings false/true for debugging purposes and to allow you some control. The code would also need better error trapping in case the table already existed. Is this any help?

    JulesG

    Private Sub cmdCopyQueries_Click()
    On Error GoTo aEscape

    Dim dbCurrent As DAO.Database
    Dim strQueryName As String
    Dim strSQL As String
    Dim qdf As QueryDef

    Set dbCurrent = CurrentDb

    For Each qdf In dbCurrent.QueryDefs
    strQueryName = qdf.Name
    strSQL = "SELECT TOP 10 " & strQueryName & ".* INTO tbl" & strQueryName & " FROM " & strQueryName & ";"

    DoCmd.RunSQL strSQL

    Next qdf


    Exit Sub
    aEscape:
    MsgBox Err.Description
    End Sub

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

    Re: Selecting first 10 records (AccessXP)

    Just in case there are queries whose name contains spaces or other "unusual" characters, you should put square brackets around the query name and table name:
    <code>
    strSQL = "SELECT TOP 10 [" & strQueryName & "].* INTO [tbl" & strQueryName & "] FROM [" & strQueryName & "]"</code>

  5. #5
    New Lounger
    Join Date
    Jan 2008
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting first 10 records (AccessXP)

    Thanks Hans and Jules. That worked brilliantly. <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>

Posting Permissions

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