If Then Else statement problem (1 Viewer)

[slimjen1]

All, using access 2010. Attached is a snippet of code from an “Email Module”. Everything works but I have two reports that attaches to the email and in a folder. As of now; both reports are attached to a corresponding email. But I want to use the reports (either rpt1 or rpt2) to attach to the email based on criteria from a query. I tried below an “IF Then Else” Statement. When I run the code; only the second rpt(rpt2) attaches even though both criteria are available the qry used. (Bolded below) I can’t find why. Help please.

 ' Loop through each broker in the "tblBroker" table - determining which brokers
                    ' need to receive e-mails.  For each eligible broker, create the necessary
                    ' report file(s), and attach those files to a new e-mail for that broker.
                    Set rstBrokers = CurrentDb().OpenRecordset("EA01_qryBroker_email1")
                    With rstBrokers
                        ' Get the total e-mail count.
                        .MoveLast
                        intNumEmailsToCreate = Nz(.RecordCount, 0)
                        .MoveFirst

                        ' Loop through the brokers - creating an e-mail (with attached reports) for each broker.
                        Do While Not .EOF
                            ' Create the e-mail.
                            If (Not g_blnTestMode) Or (intNumEmailsCreated <= 2) Then
                                ' Make broker name filename-ready.
                                strScrubbedBrokerName = Replace(!Bus_Name, ".", "")
                                strState = Replace(!state_abbr, ".", "")
    
                                ' Set current broker ID (used by the reports' underlying queries).
                                g_varCurrentBrokerID = !client_id
                            [B]   If ("[EA01_qryBroker_email1]).[letter_code]" = "NEW") Then
                   
                                ' Create the report #1.
                                strPathAndFilename_Report1 = strPathToStatementFiles & strReportFilenamePrefix & strScrubbedBrokerName & " " & strState & " " & varCheckIssueDate & ".pdf"
                                DoCmd.OutputTo acOutputReport, "rpt1_email", acFormatPDF, strPathAndFilename_Report1, False
                                DoEvents     ' Allow this operation to be fully completed before proceeding.
                               Else
        
                                ' Create the report #2.
                                strPathAndFilename_Report2 = strPathToStatementFiles & strReportFilenamePrefix & strScrubbedBrokerName & " " & strState & " " & varCheckIssueDate & ".pdf"
                                DoCmd.OutputTo acOutputReport, "rpt2_email", acFormatPDF, strPathAndFilename_Report2, False
                                DoEvents     ' Allow this operation to be fully completed before proceeding.
                               End If[/B]
                              ' Create the e-mail subject.
                                strSubject = strSubjectPrefix & !Bus_Name
                               ' Get a new copy of the template.
                                varHTMLBody = varHTMLBody_StaticTemplate
                                .MoveNext
                        Loop
             ' Close the recordset.
                        .Close
                    End With
                    Set rstBrokers = Nothing
                End If

 

[JHB]

Check the value of [EA01_qryBroker_email1]).[letter_code] when your code runs, use a msgbox or put in a breakpoint.

 

[lespritdelescalier]

Hi there!

At first glance, it looks like you are comparing two constant strings together in your IF statement:

If ("[EA01_qryBroker_email1]).[letter_code]" = "NEW") Then

By putting the double quotes around the table and field name, you're basically comparing the string "[EA01_qryBroker_email1]).[letter_code]" to the string "NEW"

I'd suggest removing the double quotes around the table and field, and see if that works for you.

If it doesn't, I'd go about debugging this by doing one of the following:

1. Just before the IF statement, insert a msgbox to display the current value of the field. You'll be able to see what you're comparing to the string "NEW"

2. Type the following just before the IF statement:

Debug.Assert False

When you run your code, you'll jump into debug mode, and then tap F8 to step through your code one line at a time. Once you step past the IF statement, hover your mouse cursor over the value you want to see. You may have to assign the field value to a variable first (because sometimes you can't hover over the field to see the value).

Let me know if any of this helps!

 

[slimjen1]

Thanks for assisting. I'll try these suggestions and post back.

 

[slimjen1]

When I put the debug.assist false, I hover over

If ([EA01_qryBroker_email1]).[letter_code] = "NEW") Then

and get nothing! I removed the quotes. It doesn't look like it's even evaluating this line. What am I doing wrong please

 

[lespritdelescalier]

When I put the debug.assist false, I hover over

If ([EA01_qryBroker_email1]).[letter_code] = "NEW") Then

and get nothing! I removed the quotes. It doesn't look like it's even evaluating this line. What am I doing wrong please

Hovering over a field in a table may not bring up anything in the debugger. The way around this is to put the field into a variable so you can see the value. You can get rid of the variable later.

For instance, you can do this:

Dim strFldValue as string

strFldValue = ([EA01_qryBroker_email1]).[letter_code]

Then once you step past that line (you have to be past it, or it won't work) you can hover over strFldValue and find out what string is.

Alternatively, you can add a third line of code to the above to pop a Msgbox:

Msgbox = strFldValue

 

[CJ_London]

looks like

([EA01_qryBroker_email1]).[letter_code] = "NEW")

has 1 left bracket and two right brackets - none are required

 

[slimjen1]

Ok. I added this code as suggested:

 Debug.Assert False
                                
                                strFldValue = [EA01_qryBroker_email].[letter_code]
                                
                                If strFldValue = "NEW" Then
' Create the report #1.
                                strPathAndFilename_Report1 = strPathToStatementFiles & strReportFilenamePrefix & strScrubbedBrokerName & " " & strState & " " & varCheckIssueDate & ".pdf"
                                DoCmd.OutputTo acOutputReport, "rpt1_email", acFormatPDF, strPathAndFilename_Report1, False
                                DoEvents     ' Allow this operation to be fully completed before proceeding.
                               Else
        
                                ' Create the report #2.
                                strPathAndFilename_Report2 = strPathToStatementFiles & strReportFilenamePrefix & strScrubbedBrokerName & " " & strState & " " & varCheckIssueDate & ".pdf"
                                DoCmd.OutputTo acOutputReport, "rpt2_email", acFormatPDF, strPathAndFilename_Report2, False
                                DoEvents     ' Allow this operation to be fully completed before proceeding.
                               End If

I get an error: the expression you entered refers to an object that is closed or doesn't exist.
I hover over the strFldValue and get "". The records in EA01_qryBroker_email has either "New" or "Old". So why the error?

 

[CJ_London]

you say

The records in EA01_qryBroker_email has either "New" or "Old".

So where is EA01_qryBroker_email in relation to your code? i.e. is your code in a form and EA01_qryBroker_email is the recordsource to your form? or perhaps EA01_qryBroker_email is a recordset that has been opened somewhere?

The error message is telling you it doesn't exist - which can be interpreted as not existing in the way you are referring to it - e.g. if EA01_qryBroker_email is the recordsource to your form then you would refer to it as me.letter_code

 

[slimjen1]

GM. Thanks for assisting. Referring to my post in #1; EA01_qryBroker_email is used to loop thru the records. So are you saying because it's already open; I need refer to the qry as

strFldValue = me.[EA01_qryBroker_email].[letter_code]

?
thanks

 

[CJ_London]

sorry, for some reason didn't see that.

You have opened a recordset called rstBrokers based on EA01_qryBroker_email1

and you are looping through that recordset so try

 If rstBrokers.letter_code = "NEW" Then
 ...

or

 If !letter_code = "NEW" Then
 ....

 

[slimjen1]

Your suggestion worked! Thanks so much for all your assistance.