Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Missouri, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting up invoice (Access 2000)

    Hello,

    I'm trying to figure out the best way to approach this but nothing I've thought of so far seems like it will work quite "right."

    I need to setup a system for the user to create an invoice where they select one or more "packages" but the individual components of the package(s) are posted to the invoice. For example, the user invoices a customer for an "oil change" so the invoice should list out "oil change", "oil filter" and "motor oil" on the invoice. The user needs to be able to change the quanities for some of the components (may use a variable number of qts of motor oil.)

    If I didn't have to work with "packages" I'd just use a form/subform approach to add individual lines to an invoice. (Data stored in an INVOICE table and an INVOICE_LINE table.) But I'm not sure how to go about this with packages.

    As far as storing the data I was planning on using the following table structure (table names in caps, field names in lower case, pk = primary key):
    INVOICE (invoice_number (pk), date, customer_name, etc)
    INVOICE_LINE (invoice_number (pk), line_number (pk), item_sold (references item_number from INVENTORY table), qty_sold, price, etc)
    PACKAGE (package_number (pk), description, etc)
    PKG_INV (package_number (pk), item_number (pk)) -- this is what determines what items are components of each package
    INVENTORY (item_number (pk), description, price, etc)

    Can anyone point me in the right direction? I've done a bit of work with VB and some VBA in Word but I've not tried VBA in Access -- tho I'm ready to learn if need be. TIA

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up invoice (Access 2000)

    I think your problem is that you are thinking of a package as a data item, where in fact it

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Setting up invoice (Access 2000)

    You still need to think in terms of a form/subform, because that is how your data is related. A "package" is nothing more than a predetermined series of lines that will show up in the subform. For example, you might have a listbox on left side of your form which contains all your packages by title. Selecting a package from the listbox then populates the invoice subform, allowing user to make changes.

    In addition to the tables you probably already have (Invoices and InvoiceLines), you also need these tables: Packages and PackageLines.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Missouri, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up invoice (Access 2000)

    Sorry for the late reply. I took your suggestions and they did the job. Thanks for your help!

Posting Permissions

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