Results 1 to 5 of 5
  1. #1
    smikkelsen is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    60

    Bulk Email / Loop through recordset


    I am trying to create a command button that will email 1 or 2 dozen people at a time. I have the code for the email more or less figured out, its just the recordset i'm having trouble with. I'm not real knowledgable on the recordset stuff, and I ALWAYS have tons of errors thrown at me when I try to do it. Even when using exsisting code that I found, like in this case.

    I have Access 2007.

    Here is my code so far:

    Code:
     
    Private Sub cmdBulkEmail_Click()
    Dim cn As DAO.Connection
    Dim rs As DAO.Recordset
    Dim strEmails As String
    Set cn = CurrentProject.Connection
    Set rs = New DAO.Recordset
    With rs
        Do While Not .EOF
            If [pu BulkEmail] = -1 Then
                strEmails = strEmails & .Fields("Email") & "; "
                strClientNames = strClientNames & .Fields("Merchant") & " | "
                [pu BulkEmail] = 0
            End If
            .MoveNext
        Loop
        .Close
    End With
    strEmails = Left(strEmails, Len(strEmails) - 2)
    strClientNames = Left(strClientNames, Len(strClientNames) - 3)
     
    MsgBox (strEmails)
    MsgBox (strClientNames)

    This command button is on the form footer of a continuous form. I have checkboxes on the details that can be checked. This button should then loop through each record, skipping records that do not have the check box checked, and it should gather the email addresses and names for later use.

    I keep getting errors like "invalid use of the word new" for the Set rs = New DAO.Recordset line. and set cn = CurrentProject.Connection errors, etc.

    Any idea how I can fix this to work? Im ready to pull my hair out!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You're kind of mixing ADO and DAO there. Here's an example of a loop:

    http://granite.ab.ca/access/email/recordsetloop.htm

    One correction I would make to that old code is to disambiguate the declarations to:

    Dim MyDB As DAO.Database, RS As DAO.Recordset
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    smikkelsen is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    60
    Thank you for your reply.

    I got that part sorted out using the link you gave me. Much appreciated.

    This is my problem though. I have a form open like I mentioned. This form is based off a table, but is filtered in the forms query builder. How can open the recordset for just the records in this form rather than the entire table?

    Here is what I have:
    Code:
    Set rs = db.OpenRecordset("main_tracking")
    But this will obviously open the entire table. Since it is a multi-user enviroment, some of the things I am doing with the recordset will affect others if I cannot limit it to just what the user is looking at.

    I hope this makes sense. Any help is appreciated!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Several ways. You can open the recordset on an SQL statement, which is what I most commonly do:

    Set rs = db.OpenRecordset("SELECT...FROM...WHERE...")

    Or set it to the form's recordset:

    Set rs = Me.Recordset.Clone
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    smikkelsen is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    60
    That was exactly what I wanted (the clone).

    Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA Loop
    By DreamOn in forum Programming
    Replies: 4
    Last Post: 06-25-2010, 03:35 AM
  2. Loop through Records and Make ID
    By rob4465 in forum Programming
    Replies: 3
    Last Post: 01-14-2010, 10:46 AM
  3. Replies: 1
    Last Post: 11-13-2009, 03:03 AM
  4. For Each LOOP statement
    By zambam737 in forum Programming
    Replies: 3
    Last Post: 10-26-2009, 09:59 PM
  5. concatenate string using loop
    By nengster in forum Programming
    Replies: 0
    Last Post: 02-23-2009, 08:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums