CurrentDb.Execute not returning RecordsAffected

i cant get the following function to return a value other than 0.

Public Function DaoExecuteSql(SqlStr As String) As Long
On Error GoTo ErrorHandler

   CurrentDb.Execute (SqlStr), dbFailOnError
   DaoExecuteSql = CurrentDb.RecordsAffected

Exit Function
ErrorHandler:
   MsgBox Err.Description, vbCritical
   Exit Function
End Function
tricks801Asked:
Who is Participating?
 
flavoCommented:
I belive its because using currentdb each time creates a new instance of the db object

so use

Public Function DaoExecuteSql(SqlStr As String) As Long
On Error GoTo ErrorHandler

Dim db as dao.database
Set db = currentdb
   db.Execute (SqlStr), dbFailOnError
   DaoExecuteSql = db.RecordsAffected

Exit Function
ErrorHandler:
   MsgBox Err.Description, vbCritical
   Exit Function
End Function

Dave
0
 
flavoCommented:
I belive its because using currentdb each time,which creates a new instance of the db object
0
 
tricks801Author Commented:
thanks
0
 
flavoCommented:
Cheers mate!

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.