Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2007
    Posts
    348

    Unanswered: Do something with each record, not update

    I'm sorry to have to come with such a simple one but I'm still learning to deal with some of this stuff programmatically.

    I have table that has a person's name and email. I want to send an email to each of them that is customized to them. What I can't figure out is how do I go to the first row, perform some stuff, go to the second row and so on.

    I was thinking that I should do some kind of "for...each" but that only takes an array, or so it seems. It seems that to put the field in an area, I'd already have to step through each record. Obviously I'm missing something here.

    Thanks very much.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Open a recordset, then a loop:

    Code:
    Do While Not rs.EOF
      'do your thing
      rs.MoveNext
    Loop
    Paul

  3. #3
    Join Date
    Feb 2007
    Posts
    348
    Thanks very much Paul.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    To expand on Paul's posting:

    Code:
    Dim db As Database, rs As Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("TblPeople", dbOpenTable)
    
    Dim strSubject as string
    strSubject  = "Subject Line"
    
    'usually I do a movefirst as well, so...
    With rs
        .MoveFirst
        While Not .EOF
            DoCmd.SendObject acSendNoObject, , , ![FldEmail], , , strSubject, "Hello " & ![FldName]
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
    Or something there abouts.
    Last edited by nckdryr; 06-29-07 at 13:56.
    Me.Geek = True

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    1) db & rs should be disambiguated with DAO
    2) strEmailAddress is not declared
    3) the .MoveFirst is unnecessary and will throw an error if the recordset happens to be empty
    4) why use a DLookup to get a value that's already available in the recordset?
    5) strSubject is neither declared nor set
    Paul

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    Thanks guys for the info. I got it nailed thanks to you all.
    Between trying to use the wrong syntax and the fact that I still stumble with my recordsets and connections a bit, I was getting frustrated. I really appreciate the help.

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by pbaldy
    1) db & rs should be disambiguated with DAO
    2) strEmailAddress is not declared
    3) the .MoveFirst is unnecessary and will throw an error if the recordset happens to be empty
    4) why use a DLookup to get a value that's already available in the recordset?
    5) strSubject is neither declared nor set
    Had fixed 2, 4 and 5 before you posted
    Me.Geek = True

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    if the recordset is empty, won't the .movenext also throw up an error anyways?
    Me.Geek = True

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by nckdryr
    if the recordset is empty, won't the .movenext also throw up an error anyways?
    No, because it's inside the loop. If EOF is true, code inside the loop will never be executed.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •