By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

How to use CurrentDb.RecordsAffected?

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function backupRecord(strTableName As String, strKeyField As String, lngID As Long) As Boolean
  2.     Dim strSQL As String
  3.     strSQL = "INSERT INTO [hist_" & strTableName & "]" & _
  4.             " SELECT [" & strTableName & "].*" & _
  5.             " FROM [" & strTableName & "]" & _
  6.             " WHERE [" & strKeyField & "]=" & lngID
  7.     Debug.Print strSQL
  8.     CurrentDb.Execute strSQL
  9.     Debug.Print "Records:" & CurrentDb.RecordsAffected
  10.  
  11.  
  12. 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.
Jan 4 '12 #1

✓ answered by ADezii

A slight change in Syntax should obtain the desired Results:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim db As DAO.Database
  3.  
  4. Set db = CurrentDb
  5.  
  6. strSQL = "INSERT INTO tblTest ([Field1], [Field2], [Field3]) VALUES ('One','Two','Three')"
  7.  
  8. With db
  9.   .Execute strSQL, dbFailOnError
  10.     MsgBox "Number of Records Affected: [" & .RecordsAffected & "]"
  11. End With

Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,597
A slight change in Syntax should obtain the desired Results:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim db As DAO.Database
  3.  
  4. Set db = CurrentDb
  5.  
  6. strSQL = "INSERT INTO tblTest ([Field1], [Field2], [Field3]) VALUES ('One','Two','Three')"
  7.  
  8. With db
  9.   .Execute strSQL, dbFailOnError
  10.     MsgBox "Number of Records Affected: [" & .RecordsAffected & "]"
  11. End With
Jan 4 '12 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
Thank you very much. You have just made my life alot easier!
Jan 4 '12 #3

ADezii
Expert 5K+
P: 8,597
You are quite welcome, TheSmileyCoder.
Jan 5 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 5 '12 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
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
Jan 5 '12 #6

NeoPa
Expert Mod 15k+
P: 31,186
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 ;-)
Jan 6 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.