Results 1 to 4 of 4

Thread: nested loops

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: nested loops

    All, using access 2010 I have code that creates an email and a pdf attachment based on a query. The table has the fields custid and state along with other fields. When I run the code; it runs fine pulling the data to create the pdf attachment. But when I have a customer with data in more than one state; the pdf attachment shows the two or more reports in one pdf for that custid. I would like separate pdf for the output; one for each state the cust has. So; I was trying a nested loop to pick up the cust and state combination instead of all the rpts. Going to one pdf but I’m getting blank reports or still multiple reports in the same pdf. Attached is a snippet of the code( all my variables are declared but doesn't show in this snippet):

    Code:
    Private Sub cmdOK_Click()
    On Error GoTo Err_cmdOK_Click
    Dim rstCust As DAO.Recordset
    Dim rstState As DAO.Recordset
    Set rstCust = CurrentDb().OpenRecordset("qryCust_email")
                        
                        If rstCust.BOF And rstCust.EOF Then
                            MsgBox "No Records"
                            Else
                        With rstCust
                            ' Get the total e-mail count.
                            .MoveLast
                            intNumEmailsToCreate = Nz(.RecordCount, 0)
                            .MoveFirst
                      ' Loop through the Cust - creating an e-mail (with attached reports) for each Cust.
                            Do While Not rstCust.EOF
    Set rstState = CurrentDb().OpenRecordset("Select  State_abbr  from qryState_Email where  CustID =” & rstCust!CustID")
    
                            Do While Not rstState.EOF
                                ' Create the e-mail.
                                If (Not g_blnTestMode) Or (intNumEmailsCreated <= 2) Then
                                    ' Make Cust name filename-ready.
                                    strScrubbedCustName = Replace(!Bus_Name, ".", "")
                                    strState = Replace(!state_abbr, ".", "")
                                    strCust = Replace(!CustID, ".", "")
                                    
                                    ' Set current CustID (used by the reports' underlying queries).
                                    g_varCurrentCustID = !CustID
                                    strFldValue = !letter_code
                                    If strFldValue = "NEW" Then
        
                                    ' Create the report #1.
                                    strPathAndFilename_Report1 = strPathToStatementFiles & " " & strCust & " " & strScrubbedCustName & " " & strState & " " & varIssueDate & ".pdf"
                                    DoCmd.OutputTo acOutputReport, "rptLetterNEW_email", acFormatPDF, strPathAndFilename_Report1, False
                                    DoEvents     ' Allow this operation to be fully completed before proceeding.
                                   Else
                                           ' Create the report #2.
                                    strPathAndFilename_Report2 = strPathToStatementFiles & " " & strCust & " " & strScrubbedCustName & " " & strState & " " & varIssueDate & ".pdf"
                                    DoCmd.OutputTo acOutputReport, "rptLetter_email", acFormatPDF, strPathAndFilename_Report2, False
                                    DoEvents     ' Allow this operation to be fully completed before proceeding.
                                    End If
                                    ' Build the list of attachments.
    '                                varSemicolonSeparatedListOfAttachments = strPathAndFilename_Report1 & "; " & strPathAndFilename_Report2
                                    
                                    varSemicolonSeparatedListOfAttachmentsN = strPathAndFilename_Report1
                                   
                                    varSemicolonSeparatedListOfAttachmentsT = strPathAndFilename_Report2
                                rstState.MoveNext
                            Loop
                                rstCust.MoveNext
                            Loop
                            ' Close the recordset.
                            rstCust.Close
                            rstState.Close
                        End With
                        
                        Set rstCust = Nothing
                        Set rstState = Nothing
        End If
        End If
       
    Exit_cmdOK_Click:
        Exit Sub
    
    Err_cmdOK_Click:
        Application.Echo True
        g_TrappedErrorMsg "cmdOK_Click", Err.Description, Err.Number
        Resume Exit_cmdOK_Click
    End Sub
    I’m also getting a “Type Mismatch” error which I can’t find why because both state_abbr and CustID is of the same datatype. Maybe I’ve been looking at this too long but I need a resolution as I’ve been at this for a couple of days now. Can someone tell me what’s wrong with my code please?

  2. #2
    Join Date
    Mar 2015
    Posts
    27
    While I haven't looked at your code to find the type mismatch problem yet, I suspect that your other problem comes from the fact that your call to DoEvents is not a method for checking if your output to report really is completed. It gives up control to other processes for a tiny time slice and then just moves right on through your loop and is back to the report generation command a moment later, almost guaranteed to be there will still processing the previous request. DoEvents only gives other things waiting to happen a quick time slice and then moves on, keeping Access from going non-responsive in long processing loops. It will not wait for your previous command to finish outputting that file and that seems to be what you are thinking it does.

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by rodrich1954 View Post

    ...DoEvents only gives other things waiting to happen a quick time slice and then moves on, keeping Access from going non-responsive in long processing loops. It will not wait for your previous command to finish
    Actually, that's pretty much what DoEvents does do, according to Help:

    Quote Originally Posted by Access Help
    DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent.
    and my past 15 years of experience.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Mar 2015
    Posts
    27
    Quote Originally Posted by Missinglinq View Post
    Actually, that's pretty much what DoEvents does do, according to Help:



    and my past 15 years of experience.
    And that would include the completion of a command that is writing a report to disk? I was not under that impression but hey, I will readily admit I may have misunderstood what all that command would do. I will have to look more closely at that in the future since that kind of behavior would be more useful than I thought. Oh well, any other ideas? I haven't had time to study his code in any depth. That was the only thing that immediately caught my eye based on my (mis)understanding of the DoEvents command.

Posting Permissions

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