Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    38

    Unanswered: If Recordset is not empty 'Do Something

    I'm trying to get an automated email to send only if the recordset is not empty.

    Here's a snippet:

    Code:
    If rs_Data.RecordCount > 0 Then
          .Send
          End If
    This sends a blank email, instead of no email if the recordset is empty. I also tried:

    Code:
    If Not rs_Data.EOF And Not rs_Data.BOF Then....
    but it did the same thing. I feel like either of these approaches should work. What am I missing?

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I dislike trying to troubleshoot code when I can only see the snip of code that the poster thinks is relevant and without even knowing where it's being executed from! Can you post the entire procedure you're using?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2012
    Posts
    38
    Sorry about that.

    This is the situation: I only want to execute the If/Then/Else with the SendMessage and Parse_Workflow routines if my recordset has no Fields of the EmailSent attribute that are checked (I have another script that sends the email and successfully checks the EmailSent attribute).
    You can see the lines I commented out, those are what I'm having trouble with. I tried changing them a bit since I made the OP but that's where I had the 'rs_Data.RecordCount > 0' attemt etc.

    Code:
    Private Sub ChkForNullEmails()
    'Dim rs_Data As ADODB.Recordset
    Dim rst As ADODB.Recordset
    
    'Set rs_Data = New ADODB.Recordset
    Set rst = New ADODB.Recordset
    'rs_Data.Open "Select * From q_AuthToRoute Where [E-Mail] = '" & rs.Fields("E-Mail") & "' And EmailSent = False", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    rst.Open "Select [E-Mail] from q_AuthToRoute Where [E-Mail] is null", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    'If (rs_Data.Fields(12).Value) = False Then
    If (rst.EOF And rst.BOF) = False Then
    SendMessage
    parse_Workflow
    Else
    parse_Workflow
    End If
    'End If
    DoCmd.SetWarnings False
    DoCmd****nSQL "Update t_Routing Set EmailSent = Yes Where [E-Mail] is not null"
    DoCmd.SetWarnings True
    End Sub
    EDIT: field 12 is the EmailSent attribute.

  4. #4
    Join Date
    Jun 2012
    Posts
    38
    As Missinglinq hinted at, the problem was elsewhere in the code. Thanks.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad you got it fixed!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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