Access VBA:To generate a report of result of update query

hi,

Is it possibel to get a report of the records that are updated using a update query.I mean without using recordset. suppose sqltext = update table employees set bonus = 0 where salary > 50000 DoCmd.RunSQL sqltext

now after theis query runs is it possible to get the name of th eemployees for whom this update query was performed.

Thanks

From stackoverflow

• I don't see any way at this time to get the information after the update query has run unless you have another distinguishing field (maybe an updated date field). Why not run a select query for it and run a report off of that data, THEN run the update query to change the values for 'bonus'.

  Let me know if this helps! JFV

  tksy : i am not sure about this method as there are chances updates on certain records may fail to haapen but they may be picked up by the select query

  JFV : As long as the data is not highly volatile, the 'Select' and the 'Update' should return the same records. If the 'Update' fails, then you might have some other issues with the data.

  Patrick Cuff : You can open the Access database for exclusive access to run the select and update, just to make sure nobody got in and changed data on you between queries.

• It's never a good idea to use DoCmd.RunSQL as it generates a prompt (which you have to turn off if you don't want it), and it completes the updates even if errors occur, and doesn't report the errors. Much better is to replace it with a function that executes the same SQL:

  Public Function SQLRun(strSQL As String) As Boolean
  On Error GoTo errHandler
  
    CurrentDB.Execute strSQL, dbFailOnError
    SQLRun= True
  
  exitRoutine:
    Exit Function
  
  errHandler:
    MsgBox err.Number & ": " & err.Description, vbExclamation, "Error in SQLRun()"
    Resume exitRoutine
  End Function
  

  Once you've placed this in a public module, you can easily do a global search and replace for "DoCmd.RunSQL" to replace it with "SQLRun".

  EDIT: Another version of this function that returns the number of records affected is here:

  http://stackoverflow.com/questions/343396/acess-vba-to-get-value-from-update-query-prompt/348453#348453