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

Updating field value for all records in query using LOOP

P: 52
I'm baaaack! I've searched for a while but could find no answers. Could you help me?

I have table that contains, among other things, an email address and a date field for the day that I emailed them.

I have built the following vba code to loop through a query (built off the table, containing the email and date fields mentioned above) to send an email. Works perfectly. What I want to do is set the [First Contact Date] to today's date for each record.

Here's the code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command23_Click()
  2. Dim strEMail As String
  3. Dim oOutlook As Object
  4. Dim oMail As Object
  5. Dim strAddr As String
  6. Dim strBody As String
  7. Dim MyDB As DAO.Database
  8. Dim rstEMail As DAO.Recordset
  9.  
  10. Set oOutlook = CreateObject("Outlook.Application")
  11. Set oMail = oOutlook.CreateItem(0)
  12.  
  13. 'Retrieve all E-Mail Addressess in qryCurrent1stContact
  14. Set MyDB = CurrentDb
  15. Set rstEMail = MyDB.OpenRecordset("Select * From [qryCurrent1stContact]", dbOpenSnapshot, dbOpenForwardOnly)
  16.  
  17. With rstEMail
  18.   Do While Not .EOF
  19.     'Build the Recipients String
  20.     strEMail = strEMail & ![E-mail] & ";"
  21.     [First Contact date] = Now() 'this is the line of interest
  22.       .MoveNext
  23.   Loop
  24. End With
  25. '--------------------------------------------------
  26. 'Set what will be in the body of the email
  27.  strBody = DLookup("EmailBody", "tblEmailBodies", "[ID] = 1")
  28.  
  29. With oMail
  30.   .To = "email@email.fake"
  31.   .Bcc = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  32.   .Body = strBody
  33.   .Importance = 2
  34.   .Subject = "Important Information From us - Please Read!"
  35.     .Display
  36. End With
  37.  
  38. Set oMail = Nothing
  39. Set oOutlook = Nothing
  40.  
  41. rstEMail.Close
  42. Set rstEMail = Nothing
  43.  
  44. End Sub
  45.  
  46.  
I'm trying to use the loop I already created to do the date for each record, so I put in that line

Expand|Select|Wrap|Line Numbers
  1.     [First Contact date] = Now()
  2.  
Problem: It only updates the very last record to today's date.

What am I missing? I'm sorry if I did not supply enough information, or if this question has been answered elsewhere. If so, could you please point me in the right direction?

Many thanks!

Melody
Mar 17 '12 #1
Share this Question
Share on Google+
9 Replies


P: 52
I had a thought...do you think this will work?

I thought I'd create an update query based on the query this code uses. Then I could trigger the update query to run from within the code to update the dates. Will this work?

Now I need to figure out how to do it!! Any tips would be appreciated.

(Oh, and thanks for fixing my code tags, Stewart!!)
Mar 17 '12 #2

NeoPa
Expert Mod 15k+
P: 31,411
When updating data in a recordset it is necessary to use the .Edit and .Update methods of the recordset object. You can find the details using Context-Sensitive Help ;-)
Mar 17 '12 #3

P: 52
Thanks, NeoPa. I have attempted to learn and implement this, but I have a new problem. I get the "Run-time Error 3251 - operation not supported for this type of object" error. I read online that this probably has to do with not using dbOpenDynaset (I use dbOpenSnapshot). However, if I change it, it gaks on this line:
Expand|Select|Wrap|Line Numbers
  1. .Bcc = Left$(strEMail, Len(strEMail) - 1)        
In summary, I added the following code into that above:
Expand|Select|Wrap|Line Numbers
  1. With rstEMail
  2.   Do While Not .EOF
  3.     'Build the Recipients String
  4.     strEMail = strEMail & ![E-mail] & ";"
  5.      .Edit
  6.       Me![First Contact date] = Now()
  7.      .Update
  8.      .MoveNext
  9.   Loop
  10. End With
Please, what am I doing wrong?
Mar 18 '12 #4

NeoPa
Expert Mod 15k+
P: 31,411
Mmmel:
However, if I change it, it gaks on this line:
I'm not sure why that would happen. The two points seem totally unrelated to me.

Snapshots are, as they say, snapshots. You can never update records from a recordset which is opened as a snapshot. It's read-only. Dynaset, one the other hand, is a dynamic recordset. It can be updated as long as the recordset is updatable (See Reasons for a Query to be Non-Updatable).
Mar 18 '12 #5

P: 52
Thanks...that helps. I will post the error message from that line - perhaps you will know what is wrong then?

You 're the best! Thank you!
Mar 18 '12 #6

NeoPa
Expert Mod 15k+
P: 31,411
Mmmel:
I will post the error message from that line - perhaps you will know what is wrong then?
Sounds like a plan Melody :-)

I'm pleased the other posts were able to help you get a better understanding of the issues. Improved understanding is always good.
Mar 18 '12 #7

P: 52
Got it!! Turns out that dbforward is incompatible with dynaset. Who knew? It's working now.

It does take time to update the fields - is that normal? Any way to accelerate that?

I even put in an error trap for when the recordset is empty.

Thanks for all of your help!!!!
Mar 18 '12 #8

NeoPa
Expert Mod 15k+
P: 31,411
Mmmel:
It does take time to update the fields - is that normal? Any way to accelerate that?
It is fairly normal yes. It's also quite variable, and can be effected by indices and number of existing records I believe. Not that reducing the delay is always worthwhile, as a small hit here (write-time) can heve very beneficial effects for reading/finding records. You have to determine the best balance by considering your whole situation.
Mmmel:
Turns out that dbForwardOnly is incompatible with dbDynaset. Who knew?
Not I. I did wonder though, but came to the conclusion there wasn't an obvious reason why that should be so. Sometimes reasons aren't obvious ;-)
Mar 18 '12 #9

P: 52
Here's where I read about dbforward: http://msdn.microsoft.com/en-us/libr...ffice.10).aspx I might be misunderstanding, but they say that dbforward can also be called dbforward snapshot or some such. Since I want to update, this wasn't a good choice for me.

I really, really need to take a course someplace about Access/VBA. Everything I know about it I learned from Google or very nice people like you!

Have a wonderful day! It is gorgeous up here in Canada today. I think Spring might be on its way! : )
Mar 18 '12 #10

Post your reply

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