How can I get a value from the update query prompt in Access VBA?

When we run a update query we get prompt saying that 'these many recordr are going to be updated. do you want to continue' is it possible to capture the value in the prompt message to a variable i.e the number of records going to be updated.

From stackoverflow

• If you run the query from code, you can use the records affected property:

  Dim db As Database
  Set db=CurrentDB
  db.Execute "Some SQL here"
  db.RecordsAffected
  

  If you use a transaction, you can rollback.

  tksy : THanks that did the job

• Yes, you can get the number of records updated via the RecordsAffected property:

  Function RowsChanged(updateQuery As String) As Long
      Dim qry As QueryDef
  
      Set qry = CurrentDb.QueryDefs(updateQuery)
      qry.Execute
  
      RowsChanged = qry.RecordsAffected
  End Function
  

  You can call this function with the name of your update query to get the number of rows updated:

  Dim numRows as long
  numRows = RowsChanged("UpdateQuery")
  

• Patrick Cuff proposed this function:

    Function RowsChanged(updateQuery As String) As Long
      Dim qry As QueryDef
  
      Set qry = CurrentDb.QueryDefs(updateQuery)
      qry.Execute
  
      RowsChanged = qry.RecordsAffected
    End Function
  

  I don't understand why one would go to the trouble of assigning a QueryDef variable to execute a query when it can be done directly CurrentDB.Execute without initializing (or cleaning up) any object variables.

  Obviously, a parameter query is going need to use the QueryDef approach, since you have to assign the values to the parameters before executing it. But without parameters, there's no reason to make it more complicated than necessary. With a generic function like this that isn't set up to handle parameter queries, it seems wrongly designed.

  And, of course, it ought also to use dbFailOnError, so that you don't get unexpected results (dbFailOnError works with QueryDef.Execute, just as it does with CurrentDB.Execute). In that case, there really needs to be an error handler.

  Rather than write an error handler every time you execute SQL, you can do this, instead. The following function returns the RecordsAffected and will recover properly from errors:

    Public Function SQLRun(strSQL As String) As Long
    On Error GoTo errHandler
      Static db As DAO.Database
  
      If db Is Nothing Then 
         Set db = CurrentDB
      End If
      db.Execute strSQL, dbFailOnError
      SQLRun = db.RecordsAffected
  
    exitRoutine:
      Exit Function
  
    errHandler:
      MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in SQLRun()"
      Resume exitRoutine
    End Function
  

  It can also be used to replace DoCmd.RunSQL (you just call it and ignore the return value). In fact, this function was entirely designed for use as a global replacement for DoCmd.RunSQL.

  Patrick Cuff : I copied/pasted your code above into a new module in Access 2003. When I run it, I get "Error in SQLRun(), 424: Object required." for SQLRun = dbLocal.RecordsAffected.

  Patrick Cuff : When I run Remou's solution, I get a result that 0 rows where affected, but the table had 1 row updated. When I run my solution I get the table updated and the correct number of rows updated returned.

  David-W-Fenton : Sorry about that -- I copied out of an app where I use a cached database variable accessed via function called dbLocal that returns a database object. I've fixed the code -- just use CurrentDB for both the .Execute and the .RecordsAffected. Please undo the down vote now that I've fixed the error. ;)

  David-W-Fenton : Just to be clear -- you can't use CurrentDB and get the RecordsAffected, since each call to CurrentDB returns a different pointer to the currently-opened database. I altered the code in January to fix that problem.