How to use CurrentDb.RecordsAffected?

[TheSmileyCoder]

I am trying to use the CurrentDB.Recordsaffected property to ensure that the latest CurrentDB.Execute actually affected some records.
I have a function intented to backup a record into a history table:

Public Function backupRecord(strTableName As String, strKeyField As String, lngID As Long) As Boolean
    Dim strSQL As String
    strSQL = "INSERT INTO [hist_" & strTableName & "]" & _
            " SELECT [" & strTableName & "].*" & _
            " FROM [" & strTableName & "]" & _
            " WHERE [" & strKeyField & "]=" & lngID
    Debug.Print strSQL
    CurrentDb.Execute strSQL
    Debug.Print "Records:" & CurrentDb.RecordsAffected
 
 
End Function

My problem is that the CurrentDb.RecordsAffected is always returning 0. Can anyone explain why this is?

I have confirmed that a record is actually inserted into the history table.

[ADezii]

A slight change in Syntax should obtain the desired Results:

Dim strSQL As String
Dim db As DAO.Database
 
Set db = CurrentDb
 
strSQL = "INSERT INTO tblTest ([Field1], [Field2], [Field3]) VALUES ('One','Two','Three')"
 
With db
  .Execute strSQL, dbFailOnError
    MsgBox "Number of Records Affected: [" & .RecordsAffected & "]"
End With

[TheSmileyCoder]

Thank you very much. You have just made my life alot easier!

[ADezii]

You are quite welcome, TheSmileyCoder.

[NeoPa]

You'll notice Smiley, that although the question is quite different from your other one earlier, the solution is the same. Once you get that understanding a whole bunch of similar issues will disappear. BTW the thread Stewart linked to has a whole lot more interesting stuff in it than just that answer - It's pretty darned long though.

[TheSmileyCoder]

Hi Neo

Yes, I do realise that now. Both questions were posted at the same time though, before I got the explanation.

I will just include the link to the post in the other thread, since I find it quite relevant:
Problem using CurrentDB - Post nr 9

[NeoPa]

Smiley:
Yes, I do realise that now. Both questions were posted at the same time though, before I got the explanation.

I'm fully aware of that. My comment was not to imply any criticism, but simply to check that you'd perceived the problem. Clearly you had ;-)