Results 1 to 8 of 8
  1. #1
    catluvr is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87

    Question Open two recordsets at the same time

    On to my next programming issue ... also involving sending e-mails with tables.



    Overall, what I want to do is to open query that already exists and pull the name, email and troop number from the first record. Then, using the troop number, run an sql statement that pulls all the records for the leader's troop and builds a table with the troop roster.

    So, two recordsets, the recordset for the troop leaders and the recordset for the roster.

    When I step though and watch the locals window, I see that the leader recordset opens (it has a little + to the left of it), but the roster recordset does not (no little +).

    When I set up the DIM statements, I see that there is a Recordset variable type and a Recordset2 variable type.

    What is the difference?

    In the locals window, the leader recordset has "Recordset/Recordset2" as the type while the roster recordset says "Recordset" for the type. Does it matter? I tried changing the roster recordset to "Recordset2," but the code still didn't run correctly.

    I have two debug.print statements. The leader count statement would be the count of leaders. When it appears in the immediate window, it is correct. The troop members count should be the number of members in the leader's troop. In this case, the number should be 6, but it comes up 0.

    Also, I have used EOF and BOF successfully in other code, but for some reason unknown to me, it doesn't seem to work with this code. That's why I'm counting the records and using that to get out of the loop (made THAT mistake only once!).

    Code:
    Sub RosterEmails()
    
    Dim objOutlook As Object
    Dim objEmailItem As Outlook.MailItem
    Dim dbs As DAO.Database
    Dim tlTroop As Integer
    Dim FirstName As String
    Dim EMail As String
    Dim rstTL As DAO.Recordset
    Dim rstRoster As DAO.Recordset
    Dim strQry As String
    Dim strTable As String
    Dim tblHeader As String
    Dim OutlookAcct As Variant
    Dim OutlookAccounts As Variant
    Dim OutlookAcctTemp As Variant
    Dim strFrom As String
    Dim rstCountTL As Integer
    Dim rstStopTL As Integer
    Dim qryTL As String
    Dim rstCountRos As Integer
    Dim rstStopRos As Integer
    Dim qryRoster As String
    Dim FullName As String
    
    
    
    
    'prevent 429 error if Outlook is not open
    On Error Resume Next
        Err.Clear
        'See if Outlook is open
        Set objOutlook = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then
        'Should open Outlook
        Set objOutlook = CreateObject("Outlook.Application")
    End If
    
    
    
    
    Set OutlookAcctTemp = objOutlook.Account
    
    
            strFrom = "an_email@gmail.com"
            Set OutlookAccounts = objOutlook.Application.Session.accounts
                 For Each OutlookAcctTemp In OutlookAccounts
                    If (OutlookAcctTemp.SmtpAddress = strFrom) Then
                        Set OutlookAcct = OutlookAcctTemp
                        Exit For
                    End If
                Next OutlookAcctTemp
    
    
    Set dbs = CurrentDb
    
    
    qryTL = "TroopRosterOnlyTL"
    
    
    Set rstTL = dbs.OpenRecordset(qryTL)
    
    
    rstTL.MoveLast
    rstStopTL = rstTL.RecordCount
    Debug.Print "Number of Troop Leader records: " & rstStopTL
    
    
    rstCountTL = 0
    rstTL.MoveFirst
    
    
        
        Do Until rstCountTL = rstStopTL         'rstQuery.EOF       'Do While Not rstQuery.EOF
        
        rstCount = rstCount + 1
        
            FirstName = rstTL!First
            tlTroop = rstTL!Troop
                    
               'Pull Troop Roster based on Troop leader's troop number
                qryRoster = "SELECT TroopRoster.Troop, TroopRoster.TrpPos, TroopRoster.First, TroopRoster.Last, TroopRoster.Email, TroopRoster.Type, TroopRoster.PosSortOrder, TroopRoster.School, TroopRoster.Grade, [first] & "" "" & [last] AS FullName From TroopRoster WHERE (((TroopRoster.Troop)=tlTroop) AND ((TroopRoster.TrpPos) Not Like ""*_*"")) ORDER BY TroopRoster.Troop, TroopRoster.Type DESC , TroopRoster.PosSortOrder, TroopRoster.Last, TroopRoster.First;"
    
    
                 Set rstRoster = dbs.OpenRecordset(qryRoster, dbOpenDynaset, dbReadOnly)
                    
                    'Create table header
                    tblHeader = "<HTML><br><br>Troop" & tlTroop & " Roster:&nbsp;&nbsp;&nbsp;(please note: information is accurate as of 10/19/2021;" & _
                                " changes may take up to 24 hours to appear)<br><BR><BR><br>" & _
                                "<table border='1' style='font-family:calibri;font-size:12pt;border-collapse:collapse;padding:1px 10px 1px 10px'>" & _
                                "<tr><th align='left'>Position</th><th align='left'>Name</th><th align='left'>Email</th>" & _
                                "<th align='left'>School</th><th align='left'>Grade</th>"
                    
                    
                    'Create Table Rows
                    
                    rstRoster.MoveLast
                    rstStopRos = rstRoster.RecordCount
                    Debug.Print "Number of troop members: " & rstStopRos
    
    
                    rstCountRos = 0
                    rstRoster.MoveFirst
    
    
                    Do Until rstCountRos = rstStopRos         'rstQuery.EOF       'Do While Not rstQuery.EOF
        
                    rstCountRos = rstCountRos + 1
                        strTable = strTable & "<tr><td>" & rstRoster!TrpPos & "</td><td>" & rstRoster!FullName & _
                                   "</td><td>" & rstRoster!EMail & "</td><td>" & rstRoster!School & _
                                   "</td><td>" & rstRoster!Grade
                        Debug.Print rstRoster!FullName
                        
                        rstRoster.MoveNext
                    Loop
                   
                    
                    
                    'Build E-mail
                    
                    Set objEmailItem = objOutlook.CreateItem(0)
            
                        With objEmailItem
            
                            .SendUsingAccount = OutlookAcct
                            'Who the e-mail is to
                            .To = rstTL!EMail
                            'What the subject line is
                            .Subject = "GS Troop Roster information, please respond!"
            
                            'using HTML so the font and size can be changed. <br> is a carriage return
                            .HTMLBody = "<BODY style=font-size:12pt;font-family:Calibri>" & rstQuery!FirstName & ",<br><br>" & _
                                "Hello Again! I need you to verify several things. And again, please respond, even" & _
                                "if it's to say that everything is correct.<br><br>Below is the roster for your troop. " & _
                                "<br><br>Here are specific things to check on:<BR><BR>" & _
                                "Adults - is the position correct? When I get the information from the council, if an adult " & _
                                "is listed as both a troop leader and a troop helper, I generally code them as a troop leader. " & _
                                "I only keep track of leaders, helpers, cookie managers, and fall product sales managers. " & _
                                "If an adult is listed as, say, a camping volunteer at the council level and that's all, I code " & _
                                "them as a troop helper." & _
                                "<BR><BR>Adults - are all the adults listed? Are there adults who should not be listed?" & _
                                "<BR><BR>Girls - are all the girls listed? Are there girls who should not be listed?" & _
                                "<BR><BR>E-mail addresses - is the e-mail address for each person correct? If it's blank, " & _
                                "there is not a valid e-mail address. Some high school girls have both a parent's e-mail and " & _
                                "their own e-mail on file, however, for this listing, I only included the parent e-mail address." & _
                                "<BR>BR>School - is the school listed for each girl correct?<BR>BR><BR>BR>" & _
                                tblHeader & strTable & "</table><br>" & _
                                "<br><br><br>Thank you very much for checking this over and for taking time to respond, " & _
                                "even if it's just to say everything is correct!" & _
                                "<br><br><br>Susie Gilson<br>Registrar<br>SU 678<br>Girl Scouts<br></BODY>"
            
            
                                    Display the e-mail rather than sending it
                                    .DISPLAY
    
    
                        End With
                
                    'have the e-mail item be visible
                    objOutlook.visble = True
            
                    'Reset variables
                    Set objEmailItem = Nothing
                    rstRoster.Close
                    Set rstRoster = Nothing
                    strTable = ""
    
    
        'Move to next item in the list
        rstTL.MoveNext
        
    Loop
    
    
    rstTL.Close
    
    
    Set rstTL = Nothing
    Set dbs = Nothing
    
    
    
    
    End Sub

    Any insight would be helpful!


    Susie
    Kansas

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I see that there is a Recordset variable type and a Recordset2 variable type.

    What is the difference?
    this information is easy to find with a simple google search. But the answer is recordset2 is a subrecord used for multivalue and attachment fields, whereas recordset relates to the actual query.

    beyond that, not quite sure what you are asking/expecting.

    Note that first, last and type are reserved words and should not be used as field names because they have other meanings. Doing so can result in misleading errors. If you have to use them, enclosed with square brackets - that will resolve the issue on most occasions, but not always.

  3. #3
    catluvr is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    Quote Originally Posted by Ajax View Post
    this information is easy to find with a simple google search. But the answer is recordset2 is a subrecord used for multivalue and attachment fields, whereas recordset relates to the actual query.

    I did do a google search, but I wasn't sure if what I was doing was a subset or not. I will edit the code to not include those key words. I had forgotten that "first" and "last" were reserved.

    What I'm asking is if there is an obvious reason the code skips over opening the second recordset ... perhaps two recordsets can't be opened at the same time or something. I"ve worked with recordsets quite a lot, but never two at the same time.

    Thank you.

    Susie
    Kansas

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Open as many recordsets as you want or CPU memory will allow.

    rstRoster has no records because the tlTroop variable is within quote marks in the SQL statement construction. Concatenate variables.

    Debug.Print qryRoster to see the constructed SQL statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If you put 'option Explicit at the top of your module and compile, you should find the problem - you appear to have variables that are either not declared or not assigned values - for example

    Code:
    Do Until rstCountTL = rstStopTL         'rstQuery.EOF       'Do While Not rstQuery.EOF
        
        rstCount = rstCount + 1
    looks to me like an infinite loop since I don't see rstCountTL incrementing

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Use the EOF of each recordset. That is why it is there, rather than muck around with counts.

    Then it is two simple loops.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    catluvr is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    THANK YOU June7 and Ajax!

    I can stare at it all day long and not see the typos!

    Welshgasman, I'll try the EOF again.




    Thank Thank Thank you ALL!



    Susie
    "wait, what did I agree to?"
    Kansas

  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might want to change your code...

    You have
    Code:
    <snip>
            'Display the e-mail rather than sending it
                .DISPLAY
            End With
    
            'have the e-mail item be visible
            objOutlook.visble = True
    
            'Reset variables
            Set objEmailItem = Nothing
            rstRoster.Close
            Set rstRoster = Nothing
            strTable = ""
    
            'Move to next item in the list
            rstTL.MoveNext
        Loop
    
        rstTL.Close
    
        Set rstTL = Nothing
        Set dbs = Nothing
    
    End Sub
    The 2 lines in RED destroys the 2 variables within a loop!!
    But you could still need/be using the variables until the loop ends.

    I never destroy declarations until the end of the routine (sub or function).



    I would have have something like
    Code:
    <snip>
                'Display the e-mail rather than sending it
                .DISPLAY
            End With
    
            'have the e-mail item be visible
            objOutlook.visble = True
    
            'Reset variables
            'Set objEmailItem = Nothing    '<<-- Delete this line
            objEmailItem.Close
    
            rstRoster.Close
            'Set rstRoster = Nothing   '<<-- Delete this line
            strTable = ""
    
            'Move to next item in the list
            rstTL.MoveNext
        Loop
    
    
        rstTL.Close
        objEmailItem.Close
        rstRoster.Close
    
        Set rstTL = Nothing
        Set objEmailItem = Nothing  
        Set rstRoster = Nothing
    
        Set dbs = Nothing
    
    End Sub

    And I would suggest that the top 2 lines of the module (every module) should be (if you do not have them already):
    Code:
    Option Compare Database
    Option Explicit

    Good luck with your project...

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

Similar Threads

  1. Recursion and recordsets (too many databases open)
    By mcucino in forum Programming
    Replies: 11
    Last Post: 10-01-2019, 06:13 PM
  2. About recordsets
    By Lou_Reed in forum Access
    Replies: 4
    Last Post: 11-10-2016, 11:20 AM
  3. DAO.Recordsets - When to close and open
    By jgelpi16 in forum Programming
    Replies: 11
    Last Post: 02-08-2011, 09:16 AM
  4. Replies: 1
    Last Post: 04-11-2010, 04:05 AM
  5. Recordsets, add new, DAO and ADO
    By Arca in forum Programming
    Replies: 1
    Last Post: 04-07-2008, 03:57 PM

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
  •  
Other Forums: Microsoft Office Forums