Results 1 to 5 of 5
  1. #1
    canela123 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Nov 2017
    Posts
    3

    Nested Loop Not working correctly in Subform

    Hi, I need some help working with VBA using Nested loop referring to nested subform. I know probably not the best way to do things but only way I know. The outer loop works fine but the inner loop is stuck and showing the current record multiple times and its placing them in seperate lists rather than listing the subform (rs) record and the nested subform (rs1) record under it then repeating the loop for the next record.


    Dim db As DAO.Database
    Dim CustName As String
    Dim varTo As Variant
    Dim stText As String
    Dim DelDate As Variant
    Dim stSubject As String


    Dim stOrderID As String
    Dim detailQry As String

    Dim dQuery As String
    Dim dQuery1 As String
    Dim rs As DAO.Recordset
    Dim rs1 As DAO.Recordset


    Set rs = Forms![copy of 28_day_contact_info]![28_DAY_CHART_DATE_SUBF_Qtest].Form.Recordset.Clone
    Set rs1 = Forms![copy of 28_day_contact_info]![28_DAY_CHART_DATE_SUBF_Qtest]![28_day_chart_date_subf_q subform].Form.Recordset.Clone
    Set db = CurrentDb





    rs.MoveFirst


    Do Until (rs.EOF)
    'apt info
    dQuery = dQuery & "New Chart Date: " & rs![Chart_date] & ", " & _
    rs![apt_id] & ", " & _
    rs![State] & "; " & _
    rs![Reason] & Chr$(13) & Chr$(13)


    rs1.MoveFirst
    Do Until (rs1.EOF)
    If rs1.EOF Then GoTo NextRSRecord
    'procedure info
    dQuery1 = dQuery1 & rs1![task description] & _
    rs1![task type description] & Chr$(13)

    rs1.MoveNext


    Loop
    'Exit Do
    NextRSRecord:
    rs.MoveNext


    Loop


    rs.Close
    rs1.Close


    Set rs = Nothing
    Set rs1 = Nothing
    Set db = Nothing





    Set objOLApp = CreateObject("Outlook.Application")
    Set outItem = objOLApp.CreateItem(0) 'olMailItem=0
    With outItem






    .Recipients.Add [FPT_A] & "; " & IIf(([28day] = True), [email], "")
    .cc = [IFPCT]


    .Subject = [Area] & ": List of PTS projects moved "

    .body = Me.[NAMES] & Chr$(13) & Chr$(13) & _
    "The following projects have been moved in PTS." & Chr$(13) & Chr$(13) & _
    dQuery & dQuery1 & Chr$(13) & _
    IIf(([28day] = True), [name1] & ", " & Chr$(13), "") & _
    IIf(([28day] = True), "Please change the dates for the following projects: " & Chr$(13) & Chr$(13), "") & _
    "Please advise of any issues/concerns." & Chr$(13) & Chr$(13) & _

    'Write the e-mail content for sending to assignee
    .Display
    'DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, True
    'Err_COMMAND23_Click:
    'MsgBox Err.Description






    Set outItem = Nothing
    Set objOLApp = Nothing




    End With



    'Set rs = Nothing
    'Set db = Nothing
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Please post lengthy code between CODE tags to retain indentation and readability.

    You should get a compile error because of:

    "Please advise of any issues/concerns." & Chr$(13) & Chr$(13) & _

    Have not declared Outlook object variables objOLApp and outItem.

    After dQuery1 is built, concatenate to dQuery, reset dQuery1 to empty string, repeat the outer loop. Then just reference dQuery in the email construction.
    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.

  3. #3
    canela123 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Nov 2017
    Posts
    3
    Thank you for your response!!! So I have removed the dQuery1 reference in the body of the message and also removed & _, as far as the loops, here is what I have so far but I cant figure out how to link the 2nd subform to the first. The first record doesnt run through the nested loop and then it gives me the same current record from the 2nd subform for each record in the subform. see below, (the bold is the records from the 2nd subform that keeps repeating)

    New Chart Date: 5/24/2018,KCVB, TX; 11/17/2017: procedure currently NA 11/7/2017: RUNWAY NUMBER CHANGEFROM 15/33 TO 16/34, UNASSIGNED
    New Chart Date: 3/1/2018,K3T5, TX; AGREED, MOVED CHART DATE TO MATCH OTHER PROC AT APT.11/21/2017: INDIVIDUAL STATUS REQ SENT, NOT ASSIGNED
    PENDING IFP LINKFIX
    FIX GALVESTON NDB 0 TX USFIX

    New Chart Date: 5/24/2018,TX, TX; 11/9/2017: GALVESTON (GLS) NDB DECOMMISSIONED, NOT ASSIGNED
    PENDING IFP LINKFIX
    FIX GALVESTON NDB 0 TX USFIX

    Code:
    Set rs = Forms![copy of 28_day_contact_info]![28_DAY_CHART_DATE_SUBF_Qtest].Form.Recordset.CloneSet rs1 = Forms![copy of 28_day_contact_info]![28_DAY_CHART_DATE_SUBF_Qtest]![28_day_chart_date_subf_q subform].Form.Recordset.Clone
    Set db = CurrentDb
      
    rs.MoveFirst
    
    
    Do Until (rs.EOF)
    'apt info
         dQuery = dQuery & "New Chart Date: " & rs![Chart_date] & ", " & _
        rs![apt_id] & ", " & _
        rs![State] & "; " & _
         rs![Reason] & Chr$(13) & _
    dQuery1 & Chr$(13)
    dQuery1 = ""
    
    
    rs1.MoveFirst
     
    Do Until (rs.EOF)
    If rs1.EOF Then GoTo NextRSRecord
    'procedure info
             dQuery1 = dQuery1 & rs1![task description] & _
             rs1![task type description] & Chr$(13)
             
             rs1.MoveNext
    
    
    Loop
    'Exit Do
    
    
    NextRSRecord:
    rs.MoveNext
    
    
    Loop
    
    
    rs.Close
    rs1.Close
    
    
    Set rs = Nothing
    Set rs1 = Nothing
    Set db = Nothing

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    After dQuery1 string is built, concatenate to dQuery, reset dQuery1 to empty string, repeat the outer loop.

    NextRSRecord:
    rs.MoveNext
    dQuery = dQuery & dQuery1
    dQuery1 = ""

    However, I didn't look closely at how you are setting the recordset objects. rs1 is not filtered by the parent record ID so of course you get the same set in every iteration of the outer loop. Conventional and workable approach is like:
    Code:
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM mainTable WHERE some criteria ORDER BY something") 'referencing the main form recordsetclone as you are doing might be ok
    Do Until rs.EOF
        Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM dependentTable WHERE field = " & rs!ID & " ORDER BY something")
        ...
        Do Until rs1.EOF
            If rs1.EOF Then GoTo NextRSRecord
            ...
            rs1.MoveNext
        Loop
        rs1.Close
    NextRSRecord:
        rs.MoveNext
        dQuery = dQuery & dQuery1
        dQuery1 = ""
    Loop
    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
    canela123 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Nov 2017
    Posts
    3
    OMG You are AMAZING!!! Thanks so much! One thing I ended up changing was Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM dependentTable WHERE field = '" & rs!ID & "'") then it worked flawlessly! thanks again.

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

Similar Threads

  1. Nested loop
    By scoe in forum Modules
    Replies: 5
    Last Post: 09-30-2015, 08:13 AM
  2. Replies: 9
    Last Post: 09-03-2015, 10:04 AM
  3. Replies: 1
    Last Post: 10-29-2012, 11:24 PM
  4. Replies: 5
    Last Post: 03-17-2011, 06:21 AM
  5. Replies: 0
    Last Post: 01-27-2011, 09:58 AM

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