Count number of records deleted

  • Thread starter Thread starter Bob Quintal
  • Start date Start date
B

Bob Quintal

How can I count the number of records deleted using sql script?
Here is my code:

Dim strSQL As String
strSQL = "Delete * FROM [RMA DETAIL] "
strSQL = strSQL & " WHERE [DETAILID]=" &
Me![lstDetail].Column(14) CurrentDb().Execute strSQL

I tried this but it does not work:
MsgBox "count = " & strSQL.RecordCount

Thanks in advance for all your help!
Click to expand...
Dim countB4 as long
Dim strSQL As String
strSQL = "Delete * FROM [RMA DETAIL] "
strSQL = strSQL & " WHERE [DETAILID]=" & Me![lstDetail].Column(14)
CountB4 = Dcount("*",[RMA_DETAIL])
CurrentDb().Execute strSQL
debug.print Dcount("*",[RMA_DETAIL]) - CountB4
 
How can I count the number of records deleted using sql script?
Here is my code:

Dim strSQL As String
strSQL = "Delete * FROM [RMA DETAIL] "
strSQL = strSQL & " WHERE [DETAILID]=" & Me![lstDetail].Column(14)
CurrentDb().Execute strSQL

I tried this but it does not work:
MsgBox "count = " & strSQL.RecordCount

Thanks in advance for all your help!
 
Hi Samantha,

Try:
MsgBox CurrentDb.RecordsAffected & " records deleted."

HTH,

Rob
 
How can I count the number of records deleted using sql script?
Here is my code:

Dim strSQL As String
strSQL = "Delete * FROM [RMA DETAIL] "
strSQL = strSQL & " WHERE [DETAILID]=" & Me![lstDetail].Column(14)
CurrentDb().Execute strSQL

I tried this but it does not work:
MsgBox "count = " & strSQL.RecordCount

Thanks in advance for all your help!
Click to expand...

Dim strSQL As String
Dim qd As DAO.Querydef
Dim db As DAO.Database
strSQL = "Delete * FROM [RMA DETAIL] "
strSQL = strSQL & " WHERE [DETAILID]=" & Me![lstDetail].Column(14)
Set db = CurrentDb
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute
MsgBox "Count = " & qd.RecordsAffected
Set qd = Nothing

John W. Vinson [MVP]
 
Samantha said:
How can I count the number of records deleted using sql script?
Here is my code:

Dim strSQL As String
strSQL = "Delete * FROM [RMA DETAIL] "
strSQL = strSQL & " WHERE [DETAILID]=" & Me![lstDetail].Column(14)
CurrentDb().Execute strSQL

I tried this but it does not work:
MsgBox "count = " & strSQL.RecordCount
Click to expand...


Dim db AS Database
Dim strSQL As String
strSQL = "Delete * FROM [RMA DETAIL] "
& " WHERE [DETAILID]=" & Me![lstDetail].Column(14)
Set db = CurrentDb()
db.Execute strSQL
MsgBox "count = " & db.RecordsAffected
Set db = Nothing
 
Rob said:
Try:
MsgBox CurrentDb.RecordsAffected & " records deleted."
Click to expand...


Rob,

That won't work for the same reason that the expression:
CurrentDb Is CurrentDb
is never True.

I.e. Every time you use CurrentDb, Access constructs a new
database object. So the second CurrentDb is not the same
object that ran the query and its RecordsAffected property
was not set.
 
Thanks for the heads-up on that one, Marsh.

I'd just come across the .RecordAffected a little while ago, and my first
effort to use it failed (for a completely different reason, related to
running a saved query from code), so when I solved that I finished up with a
line:
qdf.RecordAffected ...

I'd also done some testing using CurrentDb, but looking back I find that in
that case I had:
Set db = CurrentDb
db.Execute ...
db.RecordAffected ...

Sorry for misleading the OP, and I think I've got it firmly sorted now,

Rob
 
Samantha said:
How can I count the number of records deleted using sql script?
Here is my code:

Dim strSQL As String
strSQL = "Delete * FROM [RMA DETAIL] "
strSQL = strSQL & " WHERE [DETAILID]=" & Me![lstDetail].Column(14)
CurrentDb().Execute strSQL

I tried this but it does not work:
MsgBox "count = " & strSQL.RecordCount

Thanks in advance for all your help!
Click to expand...

With CurrentDB
.Execute "Delete FROM [RMA DETAIL] " & _
"WHERE [DETAILID]=" & Me![lstDetail].Column(14), _
dbFailOnError
MsgBox "Deleted: " & .RecordsAffected
End With
 
Thank you all for your useful advice!

RoyVidar said:
Samantha said:
How can I count the number of records deleted using sql script?
Here is my code:

Dim strSQL As String
strSQL = "Delete * FROM [RMA DETAIL] "
strSQL = strSQL & " WHERE [DETAILID]=" & Me![lstDetail].Column(14)
CurrentDb().Execute strSQL

I tried this but it does not work:
MsgBox "count = " & strSQL.RecordCount

Thanks in advance for all your help!
Click to expand...

With CurrentDB
.Execute "Delete FROM [RMA DETAIL] " & _
"WHERE [DETAILID]=" & Me![lstDetail].Column(14), _
dbFailOnError
MsgBox "Deleted: " & .RecordsAffected
End With
Click to expand...
 
Back
Top