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

Question

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

Answer 1

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

Answer 2

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