Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    26

    Unanswered: Help with nested loop

    I am trying to send e-mails to a list of people and filter the results they see in the e-mail based on a query. I tried with a nested loop, but am just getting multiple e-mails to the same person, with all the information (not filtered). I realize my query in the docmd.send object line is wrong. What would be the syntax to send the filtered query which is in the second strSQL line? And why is the outer loop not moving to the next e-mail address? I'm not a programmer so please bear with me?


    Private Sub cmdEmail_Click()
    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim strSQL As String
    Dim email As String


    Dim eSub, eText As Variant

    strSQL = "SELECT [NMC: email test].[NMC Email Address] FROM [NMC: email test] GROUP BY [NMC: email test].[NMC Email Address]"
    Set rs2 = CurrentDb.OpenRecordset(strSQL)
    rs2.MoveFirst

    Do While Not rs2.EOF

    email = rs2![nmc email address]




    eSub = Me!msgSubject
    eText = Me!msgMessage
    Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveFirst
    Do While Not rs.EOF
    DoCmd.SendObject acSendQuery, rs, acFormatHTML, rs![nmc email address], , , eSub, eText, False
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing

    Loop
    rs2.Close
    Set rs2 = Nothing


    End Sub

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lots wrong there Betty I am afraid (mostly because there is no need for nested loops and you weren't moving rs2 to the next record at any point). How about:
    Code:
    Private Sub cmdEmail_Click()
    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim email As String
    
    Dim eSub, eText As Variant
    
    strSQL = "SELECT [NMC: email test].[NMC Email Address] FROM [NMC: email test] GROUP BY [NMC: email test].[NMC Email Address]"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveFirst
    
    Do While Not rs.EOF
    
    eSub = Me!msgSubject
    eText = Me!msgMessage
    
    DoCmd.SendObject acSendQuery, rs, acFormatHTML, rs![nmc email address], , , eSub, eText, False
    rs.MoveNext
    
    Loop
    
    rs.Close
    Set rs = Nothing
    
    End Sub
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2006
    Posts
    26
    it didn't like the rs in:

    DoCmd.SendObject acSendQuery, rs, acFormatHTML, rs![nmc email address], , , eSub, eText, False

    should rs be categorized as something else?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good point. Why is it there? What do you want to send?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ignore this reply - discussion continued here:
    http://www.dbforums.com/showthread.p...=1#post6234275
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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