Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    23

    Unanswered: Generating Invoices

    I have one table that contains the following fields:
    Vendor Name (Third Party)
    Vendor Id
    Order Date
    Customer Name
    Customer Address
    Billing Amount
    Vendor Email Address

    Ultimately I would like a report to pull all of the records for one vendor from the table send the invoice via the email address within the table. Then move to the next vendor and repeat that process until all of the vendors on the table have been accounted for.

    I am guessing that I would have to use a For...Next but I am not sure how to pull it all together.

    Thank for any help and suggestions.

  2. #2
    Join Date
    Sep 2008
    Location
    Amherst NY (near Buffalo)
    Posts
    11
    Dennis, what you need is two RECORSET loops to handle this (the outside loop for each vendor, then the inner loop for each invoice). Here's an example. To loop through ONE recordset, let's say your vendors, you would say:

    dim db as database
    dim rs as recordset
    set db = currentdb
    set rs = db.openrecordset("VendorTable")
    while not rs.EOF
    VendorID=rs!VendorID
    ' get other fields here
    ' generate your email here!
    rs.MoveNext
    wend
    rs.close
    db.close
    set rs = nothing
    set db = nothing

    Now you'd need an INNER loop where you would loop through each of the invoices for that vendor (rs2) and create your text or html-based invoice. You would, of course, also need the code to send the email.

    I just finished recording a whole series (about 8 hours) of tutorials on recordset development (starting with Access 320) that will be available on my web site later this week. Plus I have a free tutorial that I'll have up in my Tips & Tricks section that gives a better example of recordsets.

    Hope this gets you started.

  3. #3
    Join Date
    Sep 2005
    Posts
    23
    I am sorry but I beleive my lack of knowledge is throwing into a loop of my own.

    Do I need to create a seperate module with your code suggestions and do everything in there or do I create the report and manipulate the data through the control source or do I am I manipulating a query that becomes the data source for a report?

  4. #4
    Join Date
    Sep 2008
    Location
    Amherst NY (near Buffalo)
    Posts
    11
    Just make an unbound form. Create your recordset loops. In the loop, build text or HTML file that contains your email attachment (or drop all the records into a Temp table), then send the email there. You don't NEED to use an actual Access Report unless you want to. I assume you were just sending text-based emails out via Outlook.

Posting Permissions

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