Updating field value for all records in query using LOOP

[Mmmel]

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:

Private Sub Command23_Click()
Dim strEMail As String
Dim oOutlook As Object
Dim oMail As Object
Dim strAddr As String
Dim strBody As String
Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset
  
Set oOutlook = CreateObject("Outlook.Application")
Set oMail = oOutlook.CreateItem(0)

'Retrieve all E-Mail Addressess in qryCurrent1stContact
Set MyDB = CurrentDb
Set rstEMail = MyDB.OpenRecordset("Select * From [qryCurrent1stContact]", dbOpenSnapshot, dbOpenForwardOnly)

With rstEMail
  Do While Not .EOF
    'Build the Recipients String
    strEMail = strEMail & ![E-mail] & ";"
    [First Contact date] = Now() 'this is the line of interest
      .MoveNext
  Loop
End With
'--------------------------------------------------
'Set what will be in the body of the email
 strBody = DLookup("EmailBody", "tblEmailBodies", "[ID] = 1")
 
With oMail
  .To = "email@email.fake"
  .Bcc = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  .Body = strBody
  .Importance = 2
  .Subject = "Important Information From us - Please Read!"
    .Display
End With

Set oMail = Nothing
Set oOutlook = Nothing
  
rstEMail.Close
Set rstEMail = Nothing

End Sub

I'm trying to use the loop I already created to do the date for each record, so I put in that line

    [First Contact date] = Now()

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

[Mmmel]

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!!)

[NeoPa]

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 ;-)

[Mmmel]

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:

.Bcc = Left$(strEMail, Len(strEMail) - 1)

In summary, I added the following code into that above:

With rstEMail
  Do While Not .EOF
    'Build the Recipients String
    strEMail = strEMail & ![E-mail] & ";"
     .Edit
      Me![First Contact date] = Now()
     .Update
     .MoveNext
  Loop
End With

Please, what am I doing wrong?

[NeoPa]

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).

[Mmmel]

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!

[NeoPa]

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.

[Mmmel]

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!!!!

[NeoPa]

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 ;-)

[Mmmel]

Here's where I read about dbforward: http://msdn.microsoft.com/en-us/libr...ffice.10).aspx I might be misunderstandin g, 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! : )