nest loops

[slimjen]

I made the corrects and suggestions. I can't see if it works because I get the type mismatch error? Custid and state_abbr are both text datatype. I can't seem to find what the heck is wrong. Please help

[orange]

Do you have a list or description of exactly what you are trying to do? It seems to me you are jumping at things.
Do you know how to step debug? see this link
Can you show us which line is returning the Datatype mismatch?

[slimjen]

This is the a more detail snippet of my code. Trust that all variables are declared along with the Option explicit. It debugs with no errors. But when I run the code; datatype mismatch error. I just want to get this resolved please

' Loop through each Cust in the "tblCust" table - determining which Cust
                    ' need to receive e-mails.  For each eligible Cust, create the necessary
                    ' report file(s), and attach those files to a new e-mail for that Cust.
                    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 qry_StateEmail where Cust = " & 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 Cust ID (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
                                ' Create the e-mail subject.
                                strSubject = strSubjectPrefix & !Bus_Name
                                    ' Get a new copy of the template.
                                varHTMLBody = varHTMLBody_StaticTemplate
   
                                ' Replace all Cust-specific placeholder codes in varHTMLBody with values associated with this Cust.
                                ' <There are none at this time.>
                               
                                ' Build the list of attachments.
'                                varSemicolonSeparatedListOfAttachments = strPathAndFilename_Report1 & "; " & strPathAndFilename_Report2
                               
                                varSemicolonSeparatedListOfAttachmentsNEW = strPathAndFilename_Report1
                              
                                varSemicolonSeparatedListOfAttachmentsTRUEUP = strPathAndFilename_Report2
                         
                                ' Create the e-mail in Outlook.
                                If strFldValue = "NEW" Then
                                blnEmailCreated = g_blnSendEmail("SAVE", strDraftsSubfolderName, varEmail_from, !strEmail_to, IIf(IsNull(!strEmail_cc), Null, !strEmail_cc & "; ") & "JDS@email.com", !strEmail_bcc, strSubject, Null, Null, varHTMLBody, "HIGH", varSemicolonSeparatedListOfAttachmentsN)
                                Else
                                blnEmailCreated = g_blnSendEmail("SAVE", strDraftsSubfolderName, varEmail_from, !strEmail_to, IIf(IsNull(!strEmail_cc), Null, !strEmail_cc & "; ") & "JDS@email.com", !strEmail_bcc, strSubject, Null, Null, varHTMLBody, "HIGH", varSemicolonSeparatedListOfAttachmentsT)
                                End If
                               
                                If blnEmailCreated Then
                                    ' Bump the count of e-mails created.
                                    intNumEmailsCreated = intNumEmailsCreated + 1
 
                                    ' Update the status message.
                                    UpdateStatusMsg "NO CHANGE", Trim(Str(intNumEmailsCreated)) & " of " & Trim(Str(intNumEmailsToCreate))
                                End If
                            rstState.MoveNext
                            End If
                            Loop
                         rstState.Close        
                            rstCust.MoveNext
                        Loop
                        ' Close the recordset.
                    End With
                    End If
                End If
                ' Display a message reporting the results of the process.
                strMsgTrailer = vbCrLf & vbCrLf & ". . ." & vbCrLf & vbCrLf & "The report file(s) attached to these e-mails were created" & vbCrLf & "in the following folder:" & vbCrLf & vbCrLf & """" & Left(strPathToStatementFiles, Len(strPathToStatementFiles) - 1) & """"
                If (intNumEmailsCreated = intNumEmailsToCreate) And (intNumEmailsToCreate > 0) Then
                    ' Tell the user where to find the new e-mails.
                    MsgBox "The " & IIf(intNumEmailsCreated = 1, "", CStr(intNumEmailsCreated) & " ") & "requested e-mail" & IIf(intNumEmailsCreated = 1, " was", "s were") & " created in the following" & vbCrLf & "Outlook mail folder:" & vbCrLf & vbCrLf & """...Drafts\" & strDraftsSubfolderName & """" & strMsgTrailer, vbOKOnly + vbInformation, "E-mail Creation Complete"
                ElseIf intNumEmailsCreated = 0 Then
                    ' Notify the user that that no e-mails were created.
                    MsgBox "No e-mails were created.", vbOKOnly + vbCritical, "No E-mails Created"
                ElseIf intNumEmailsCreated < intNumEmailsToCreate Then
                    ' Notify the user that only a subset of the e-mails were created.
                    MsgBox "Only " & CStr(intNumEmailsCreated) & " of the " & CStr(intNumEmailsToCreate) & " requested e-mails " & IIf(intNumEmailsCreated = 1, "was", "were") & " created.  The" & vbCrLf & "created e-mail" & IIf(intNumEmailsCreated = 1, "", "s") & " can be found in the following Outlook" & vbCrLf & "mail folder:" & vbCrLf & vbCrLf & """...Drafts\" & strDraftsSubfolderName & """" & strMsgTrailer, vbOKOnly + vbExclamation, "Unable to Create All E-mails"
                End If
            End If
 
            ' Close the status message form.
            CloseStatusMsgForm
 
        End If
    End If
   
Exit_cmdOK_Click:
    On Error Resume Next
    'clean up
    rstCust.Close
    rstState.Close
    Set rstCust = Nothing
    Set rstState = Nothing
    Exit Sub
Err_cmdOK_Click:
    Application.Echo True
    g_TrappedErrorMsg "cmdOK_Click", Err.Description, Err.Number
    Resume Exit_cmdOK_Click
End Sub

[orange]

Do you have a copy of MZTools for VBA? It is a free utility that has many features. I think it may be helpful to you.

[ssanfu]

OK, make a copy of the dB, delete the data , compact and repair, Zip it and post it.

Trying to troubleshoot it in snippets is frustrating.

In the last snippet you posted, I count 5 "IF" statements and 10 "End IF" statements.

[slimjen]

Ok. I will after my Monday morning tasks. Thanks