Getting text data out of DAO Query into a variable

[Monterey_Manzer]

Hi All,



So I'm trying to write some code for mass email distributions based on certain criteria and I'm running into trouble getting the email addresses I want to drop into the BCC field out of the DAO query I use to select them. My code is below:

Dim db As DAO.Database, rst As DAO.Recordset, qdf As DAO.QueryDef
    Dim C As Integer
    Dim strSQL As String, strWHERE As String, CA As String, qryName As String
    
' Retrieving the CA value from the Open Form event
    CA = Forms!frmBulkEmails.strCA
    Debug.Print CA


    strSQL = "SELECT [Email Address] FROM PartInfo INNER JOIN GandHTracker ON PartInfo.[SMART Id] = GandHTracker.[SMART ID]"
    strWHERE = " WHERE PartInfo.CA Like '" & CA & "';"
    Set db = CurrentDb
    qryName = "GH Emails"
    Set qdf = db.CreateQueryDef(qryName)
    qdf.SQL = strSQL & strWHERE
    Set rst = db.OpenRecordset(qryName)
        For C = 1 To C = 999
             Emails = Emails & "; " & rst.GetRows(C)
             If rst.EOF Then
                GoTo MyNext
             End If
        Next
    Debug.Print Emails

MyNExt:
      db.QueryDefs.Delete ("GH Emails") ' deleting the query to avoid the "query already exists" error
End Sub

I have tried a few different methods to assign the emails to a string variable, in an iterative fashion, but to no avail. If anyone has an idea of how I could put this together it would really help me out.

Thanks!
Ryan

[pbaldy]

For starters, I'd get rid of all the QueryDef stuff and do this:


Set rst = db.OpenRecordset(strSQL & strWHERE)

The code to assign would look like:

Emails = Emails & "; " & rst![Email Address]

By the way, you never declare Emails.

[pbaldy]

Oh, and a better loop:

Do While Not rst.EOF
   Emails = Emails & "; " & rst![Email Address]
   rst.MoveNext
Loop

[Monterey_Manzer]

Thanks! That worked great! Now I can get all the necessary emails into the BCC field just fine. I do have one stubborn problem though and perhaps someone can help with that as well. As you can see in the code above, I filter the emails by the administrator (CA) assigned to each participant. I retrieve the value for CA by using an InputBox with the On Open event which asks the user to enter the CA before proceeding. This variable is declared as Public and is included in the General Declarations for this form.

My problem occurs when the Send Object event is canceled (usually I'm testing and I simply close the email). After I cancel the sending of the email, my CA variable no longer contains the user input from the opening of the form. I would like my users to only have to identify themselves once (upon opening the Bulk Emails Form) but it seems like the easy way out is to have that part of the subroutine that runs when they press the bulk email button. Does anyone have any more creative solutions?

Thanks!

[pbaldy]

Do you have error trapping in the procedure? I think with error trapping, the variable won't be reset. Isn't in a brief test I just did.

http://www.baldyweb.com/ErrorTrap.htm

[Monterey_Manzer]

That is some nice, common sense error handling. I realize that it might help if I showed the larger code sequence I have. The original was just the subroutine for getting the email addresses. I separated that out to troubleshoot it and now that it's working I simply added it to a larger subroutine.

First here is the code that gets the CA value:

Private Sub Form_Open(Cancel As Integer)
    strCA = InputBox("Enter the CA for the paricipants you wish to email" & vbCr & vbCr & _
            Chr(9) & "VP, LM, KR, SH, or JS" & vbCr & vbCr & "Or press * to email all participants", "Email All/Filter")

End Sub

Since the strCA is declared as Public for the entire form, I figured I should be okay using a Private subroutine to get the data. Alright, now the big ugly stuff for the "Send Email" button.

Private Sub btnGandH_Click()
    Dim EmailType As String, strSQL As String, strWHERE As String, strSEASON As String
    Dim db As DAO.Database, rst As DAO.Recordset
    Dim ThisYear As Integer
    Dim SpringStart As Date, SummerStart As Date, FallStart As Date, FallEnd As Date
    On Error GoTo ErrHandler
    
' Ensuring all the necessary selections are made before this code runs
    If Me.cboAwType = "" Then
        MsgBox "You must select and Award Type.", vbInformation, "Award Type Needed"
        Exit Sub
    End If

'Selecting the text of the email to be sent based on award type
    If Me.cboAwType.Value = "RC" Then
        EmailType = GHRCText
    ElseIf Me.cboAwType.Value = "RT" Then
        EmailType = GHRTText
    End If

'Ensuring the user has selected Season
     If Me.cboSeason.Value = "" Then
        MsgBox "Please Select a graduating Season from the menu on the left.", vbInformation, "Season Needed"
        Exit Sub
    End If
    
    ThisYear = Year(Date)
    'Setting dates for the "season" variable
     SpringStart = DateSerial(ThisYear, 1, 1)
     SummerStart = DateAdd("m", 6, SpringStart)
     FallStart = DateAdd("m", 2, SummerStart)
     FallEnd = DateAdd("yyyy", 1, SpringStart)

' selecting the SEASON SQL statement based on the Season variable
    If Forms!frmBulkEmails.Season = "Spring" Then
        strSEASON = "AND PartInfo.[Degree Grad Date] < '" & SummerStart & "';"
    ElseIf Forms!frmBulkEmails.Season = "Summer" Then
        strSEASON = "AND PartInfo.[Degree Grad Date] >= '" & SummerStart & "' AND PartInfo.[Degree Grad Date] < '" & FallStart & "';"
    ElseIf Forms!frmBulkEmails.Season = "Fall" Then
        strSEASON = "AND PartInfo.[Degree Grad Date] > '" & FallStart & "';"
    End If
    Debug.Print strSEASON

    
    If Me.cboSeason.Value = "" Then
        MsgBox "Please Select a graduating Season from the menu on the left.", vbInformation, "Season Needed"
        Exit Sub
    End If
    
' Selecting the emails by CA and Awardee Type.  Note that by using the INNER JOIN properties this SQL statement restricts
' the email collection to those participants who are on the Grad and Hire tracker as well as CA and Awardee Type.

' Retrieving the CA value from the Open Form event
    Debug.Print Forms!frmBulkEmails.strCA


    strSQL = "SELECT [Email Address] FROM PartInfo INNER JOIN GandHTracker ON PartInfo.[SMART Id] = GandHTracker.[SMART ID]"
    strWHERE = " WHERE PartInfo.CA Like '" & strCA & "' AND PartInfo.[Awardee Type] Like '" & Me.cboAwType & "' "
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL & strWHERE & strSEASON)
    Debug.Print strSQL & strWHERE & strSEASON
        Do While Not rst.EOF
            EmailList = EmailList & "; " & rst![Email Address]
            rst.MoveNext
        Loop
    Debug.Print EmailList
    EmailList = Right(EmailList, Len(EmailList) - 1)
    Debug.Print EmailList
    Debug.Print rst.RecordCount
    Set rst = Nothing
    Set db = Nothing

    
  ' Sending the actual email with the email addresses in the BCC section
    DoCmd.SendObject acSendNoObject, , , , , "'" & EmailList & "'", "Graduation and Hiring Information", EmailType
    Exit Sub
    
ErrHandler:
    If Err.Number = 2501 Then
        MsgBox "This email was not sent.", , "No Emails for You!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
End Sub

As you can see, I have some basic error handling at the bottom there but I'm still experiencing the loss of the CA data.

[pbaldy]

I'm not sure. Here's my brief test:

Option Compare Database
Option Explicit

Dim lngTest As Long

Private Sub Command3_Click()

  On Error GoTo ErrorHandler

  Debug.Print lngTest & " before"
  lngTest = 777
  DoCmd.SendObject acSendReport, "tblNumbers", acFormatPDF
  Debug.Print lngTest & " after"

ExitHandler:
  Exit Sub

ErrorHandler:
  Select Case Err
    Case 2501
      MsgBox "No data to display"
      DoCmd.Hourglass False
      Resume ExitHandler
    Case Else
      MsgBox Err.Description & " in Command3_Click "
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub

Without the error handler, the value was lost on the next run. With it, the value was still there.

[Monterey_Manzer]

Your error trapping code works like a charm! I worked out the last couple syntax errors myself (e.g. needed to surround the date variable with # rather than ' in SQL statement). It is now working beautifully and I will be applying this method to several tasks. Thanks a million!

[pbaldy]

Excellent! Glad it helped.