Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No of records to edit (2003)

    When running an Update Query, Access will advise how many records are to be amended. Is there any way to retrieve this number within VBA without having to create and run a Select Query version of the query? Thanks, Andy.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No of records to edit (2003)

    Andy

    Something like this should do :

    Set qdf = CurrentDb.QueryDefs(rst("Queryname"))
    qdf.Execute (dbFailOnError)
    "Total recs amended = " & qdf.RecordsAffected

    Nick

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

    Re: No of records to edit (2003)

    Alternatively, use something like this:

    Dim dbs As DAO.Database
    Dim lngNumRecords
    Set dbs = CurrentDb
    dbs.Execute "MyQuery", dbFailOnError
    lngNumRecords = dbs.RecordsAffected
    Set dbs = Nothing

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No of records to edit (2003)

    Is there an important difference between these two methods? Doesn't 'DoCmd.Execute' run the Action Query? I was hoping to discover how many records 'will be' affected without running the query, so that I could replace the system messages with my own.
    Thanks both, Andy.

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

    Re: No of records to edit (2003)

    DoCmd.Execute doesn't exist, as far as I know.

    If you want to find out how many records will be affected without running the query, you'll have to use DCount or open a recordset on a SELECT statement that corresponds to the action query.

Posting Permissions

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