How does RecordsAffected work?

[Laurel]

I have code which updates the database in two ways. Both times, if I put
?Currentdb.RecordsAffected in the immediate window just after the statement
is completed, the value is 0. If I look at the tables being updated, both
statements worked. In one a row is deleted, and in the other a row is added
in. I use two methods:

CurrentDb.Execute "CurrentDb.Execute "DELETE * FROM tblClassSummaryTemp
WHERE student_id <> -1;"

With irstClassSummary
.AddNew
!Student_ID = 27
!Total = txtTotal
!Bonus_Days = txtBonus_Days
!Level_Fines = txtLevelFines
!Safety = txtSafety
!Homework = txtHomework
.Update
End With

If RecordsAffected isn't the right approach, how can I test that my updates
to the database are successful?

 

[Douglas J. Steele]

You can't use CurrentDb: You need to instantiate a Database object, and use
that same database object consistently.

Dim dbCurr As DAO.Database
Dim strSQL As String

strSQL = "DELETE * FROM tblClassSummaryTemp " & _
"WHERE student_id <> -1"
Set dbCurr = CurrentDb()
With dbCurr
.Execute strSQL, dbFailOnError
MsgBox "You just deleted " & .RecordsAffected & " records."
End With

 

[Laurel]

Thanks!

You can't use CurrentDb: You need to instantiate a Database object, and use
that same database object consistently.

Dim dbCurr As DAO.Database
Dim strSQL As String

strSQL = "DELETE * FROM tblClassSummaryTemp " & _
"WHERE student_id <> -1"
Set dbCurr = CurrentDb()
With dbCurr
.Execute strSQL, dbFailOnError
MsgBox "You just deleted " & .RecordsAffected & " records."
End With

 

[Laurel]

When I do this, I get an error at the .execute statement, "Object variable
or With block variable not set."
I've had problems in the past trying to Dim the database. I have referenced
DA0 3.6. I have not referenced any ADO libraries. (Although I've played
around with that, referencing both.. in different orders. But not for this
problem here). Finally I just gave up and used "CurrentDB." But apparently
I can't get away with that and still use RecordsAffected


Here is my code, cut and pasted (ls_SQL is legit... defined elsewhere)

Dim dbCurr As DAO.Database
With dbCurr
.Execute "Select * from tlbScores"
li_safety = .RecordsAffected
End With