Trying to generate changing reports with recordset keeps making the same report

[muddy1015]

I am trying to go through a recordset and create changing reports to send out via email. The email function works but keeps sending out the same report to the entire list of emails. Still a big time Access n00b so any help would be appreciated. The report is supposed to pull from 'Customer Table' and has a subreport in it to pull from 'Customer Product Table' which both have a Customer Code in common

Dim rs As New ADODB.Recordset
Dim sSQL As String
Dim x As Long
Dim i, sec As Long
Dim varItm As Variant
Dim varCustEmail As Variant
Dim varConditionString As Variant
Const FAX_Number = 2
Const Stored_Fax_Number = 3On Error GoTo ROUTINE_ERROR
Select Case fraContactOptions.Value
    Case 1
        'SENDS TO REGULAR REPORT
        sSQL = "SELECT [Customer Table].[Company Email], [Customer Table].[Customer Code], [Customer Table].[Stored Fax Number], [Customer Table].[Company Name] FROM [Customer Table] WHERE [Customer Table].[Fax Terminal Report]=0 AND [Customer Table].[Send Customer Email]=True ORDER BY [Customer Table].[FAX Number];"
        rs.Open sSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
        MsgBox "WARNING! Open Email Program Before Hitting OK"
        i = 1
        While Not rs.EOF
            varConditionString = "[Customer Table].[Customer Code] = " & rs.Fields("Customer Code").Value & " "
            varCustEmail = rs.Fields("Company Email").Value
            DoCmd.OpenReport "Daily EMAIL Price List Report", acViewPreview, , varConditionString
            DoEvents
            Wait (2)
            DoCmd.SendObject acReport, "Daily EMAIL Price List Report", acFormatRTF, varCustEmail, , , rs.Fields("Company Name").Value & " - Daily Pricing Report", "Please find attached your daily pricing report.", False
            DoCmd.Close acReport, "Daily EMAIL Price List Report", acSaveNo
            Wait (2)
            DoEvents
            i = i + 1
            rs.MoveNext
        Wend
        rs.Close
        Set rs = Nothing

[June7]

Code looks good to me. Don't see need for the i variable.

Have you step debugged? Refer to link at bottom of my post for guidelines.

[muddy1015]

Hm ok, I have debugged and it all runs through fine, guess it must be the way I have my reports set up then.

[June7]

Is [Customer Code] a number field?

[muddy1015]

Is [Customer Code] a number field?

Yes, it is

Does fact that it keeps giving me the same values in my report mean that my vba is working correctly and I just don't have something linked correctly between reports & tables, or not necessarily?

[June7]

The recordset should have a unique record for each customer. If the code is showing same data in each iteration, that would indicate not advancing through the recordset. Which doesn't make sense because rs.MoveNext should do that and if it did not advance to EOF would be an endless loop.

The code uses only 2 fields from the recordset, really don't need to retrieve all those other fields, unless you use them later in the module.

Afraid would have to analyse your db. If you want to provide, follow instructions at bottom of my post.

[muddy1015]

Ok I've attached it, the initial issue came up with this trying to port it from Access 2003 to Access 2013 just FYI. The price information in there is old and non-confidential/critical.

The problem can be reached through 'Email Price Sheets' and then just clicking 'email' on the next screen, the VBA is in Form_formAutoEmail then in cmdEmail_click()

Thanks a bunch for any time and insight

[June7]

There is code behind the reports to set filter. The criteria is empty and this appears to be negating the criteria passed by the email procedure. I removed it and the report then opened properly filtered for each iteration.

The option group buttons have values of 1 and 2.

Code appears to be missing Case 2 line.

Code has Case 3 but there is no option 3.

[muddy1015]

There is code behind the reports to set filter. The criteria is empty and this appears to be negating the criteria passed by the email procedure. I removed it and the report then opened properly filtered for each iteration.

The option group buttons have values of 1 and 2.

Code appears to be missing Case 2 line.

Code has Case 3 but there is no option 3.

Yes! Ok the case stuff I've just been messing around with, but deleting that criteria in the open function solved it. I was under impression it was necessary to have that code there for some reason.

Thank you very much