Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005

    Question Unanswered: Looping through values to send a unique report

    The final outcome that I need is to be able to send a vendor specific report from a common report template.
    Let's say that I have a table that looks like this:

    Vendor | Order # | Amt Due | Email Address
    A | 51 | 10 |
    A | 62 | 10 |
    A | 32 | 25 |
    A | 95 | 25 |
    A | 48 | 30 |
    B | 97 | 50 |
    B | 65 | 20 |
    C | 26 | 10 |

    What I want to do is have a report template that would would populate with the records for just vendor A then send out that report to the email address in the table. Clear the data for vendor A and move down the remainder of the table in the same fashion.

    I am trying to use a for statement but I am struggling and I am not even sure that is correct. I would also asume that I have to use an unbound report where the vendor value acts as a filter. I have never done that before either but baby steps.

    Last edited by DennisG; 10-29-08 at 12:47.

  2. #2
    Join Date
    May 2008
    Raleigh, NC

    On the right path....

    I think you are on the right path. I would think the following would be a good starting point.

    > Have some type of recordset that supplies the vendor information you need
    > Loop thru the recordset running the report with the 'vendor filter' from your recordset
    > Have the report saved off somewhere in the format you want to use (spreadsheet, snapshot)
    > At the end of each loop have code email the saved report

    These psuedo steps are easier to write then implement but the concept and coding is not really that hard. Just do it in incremental steps and you should be able to do it just fine.

    Good luck, Stu
    --If its free, take it for what its worth!

  3. #3
    Join Date
    Feb 2004


    How many vendors do you have, and how often do they change?
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  4. #4
    Join Date
    Sep 2005
    There are 221 unique vendors with 966 records that are assigned to those various vendors.
    I have created a seprate table with all of the vendors so there is just one instance for each vendor i hopes to make it easier but I don't know if I did or didn't. I am completely turning myself around but I moved away from the for to creating a temp table that the report would be bound to.

    I basiclly don't know the most efficent way to do this but trying to stumble across something that works.

    Thanks for trying to help!!!!!!!!!!!

    Here is what I wrote so far but it doesn't work:
    --Tbl_Invoices has the 966 individual order records
    --tbl_Vendors has the 221 available vendors

    Sub Invoice_Vendor2()
    Dim db As Database
    Dim rsi, rsv As Recordset
    Dim strsql As String

    Set db = CurrentDb
    Set rsi = db.OpenRecordset("tbl_Invoice", dbOpenDynaset)
    Set rsv = db.OpenRecordset("tbl_Vendors", dbOpenDynaset)

    While Not rsv.EOF
    Vendor = rsv!office
    'strsql = "SELECT * FROM rsi WHERE rsi!Office = Vendor"
    DoCmd.RunSQL "INSERT INTO tbl_billing ( Office) " & _
    " SELECT Office" & _
    " FROM tbl_Invoice" & _
    " WHERE rsv!Vendor = [tbl_Invoice]![Office] "


    Set rsv = Nothing
    Set rsi = Nothing
    Set db = Nothing
    End Sub

  5. #5
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6

Posting Permissions

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