SQL Problems!
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6

Thread: SQL Problems!

  1. #1
    Join Date
    Dec 2008
    Posts
    3

    SQL Problems!

    I have a database with over 5000 contacts and I wisht to add a checkbox field which employees can check and uncheck as they please to determine who will be the recipients of a single, mass email. Sounds simple enough and one would assume the sql qould go something along the lines of:

    Set r = CurrentDb.OpenRecordset("SELECT Email FROM FranksFinanceBrokers WHERE SendEmail = True")

    And yet this doesn't seem to be working!

    Furthermore, I tried a code supplied by someone else except "Error 3265: item not found in this collection" keeps popping up, highlighting the If Not IsNull line and the following two lines. Here is my code thus far:

    Private Sub Send_to_Selected_Click()

    Dim r As Recordset
    Dim Email As String
    Set r = CurrentDb.OpenRecordset("SELECT Email FROM FranksFinanceBrokers WHERE SendEmail = True")

    Do While Not r.EOF
    If Not IsNull(r("Email")) _
    And r("Email") <> "" _
    And r("SendEmail") <> True Then
    Email = Email & r("Email") & ";"
    End If
    r.MoveNext
    Loop


    End Sub

    Where am I going wrong??

  2. #2
    Join Date
    Dec 2008
    Posts
    3

    Re: SQL Problems!

    Never mind ... I'm a total idiot ... forgot my DoCmd.SendObject line

    Work's now (also with a few slight adjustments on above code)

  3. #3
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,945

    Re: SQL Problems!

    Post what you came up with. I see a few errors, plus a few hints

    (and take a look at the link in my signature)
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  4. #4
    Join Date
    Dec 2008
    Posts
    3

    Re: SQL Problems!

    Dim r As Recordset
    Dim Email As String
    Set r = CurrentDb.OpenRecordset("SELECT Email FROM FranksFinanceBrokers WHERE SendEmail = True")

    Do While Not r.EOF
    Email = Email & r("Email") & ";"
    r.MoveNext
    Loop
    r.Close

    DoCmd.SendObject acSendNoObject, Null, Null, "", "", Email, "", "", True, Null

    End Sub


    Out of curiousity, how can I make it so each email is personalised with "Hi (FirstName)!" ... I Managed to make each personalised, but it's personalised with the FirstName of the first contact only ... So each said Hi Kate! even tho it was being sent to John, Jack and Jill ... It's not a necessity but I thought it would be nice so if you have any tips, you know where I am!

  5. #5
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,945

    Re: SQL Problems!

    It changes them, but you only see the LAST value.

    Output the name INSIDE of the FOR/LOOP rather than at the end
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  6. #6
    Join Date
    Dec 2008
    Posts
    1

    Re: SQL Problems!

    well done. i think that it is going to work now.

Tags for this Thread

Posting Permissions

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


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center