Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2003
    Posts
    150

    Unanswered: More than one product on an invoice

    Hi,

    I have 2 tables set up, ( tblOrders, and tblOrderDetails ). tblOrders contains the orderID ( PK ) and the tblOrderDetails contains the products purchased on that specific orderID.

    I have three forms setup. In the first, the user chooses the Customer, Employee, Date, Shipping service, then clicks on a 'Next' button that closes the form, and opens a form for the order details ( i would like this form to open with only the orderID of the record that was open in the Order from ). Obviously, i want to be able to add more than one product to an order, but this means that i have no PK in this table.

    Any ideas as to how to accomplish this? I know it must be pretty simple, but i just can'T figure it out....

    Right now, i have this code attached to the OnClick Event of the next button on my Orders Form:

    Private Sub Command25_Click()
    On Error GoTo Command25_Click_Err

    DoCmd.OpenForm "frmOrderDetails", acNormal, "", "", , acNormal
    DoCmd.GoToRecord acForm, "frmOrderDetails", acNewRec
    DoCmd.Close acForm, "frmOrders"


    Command25_Click_Exit:
    Exit Sub

    Command25_Click_Err:
    MsgBox Error$
    Resume Command25_Click_Exit

    End Sub

    Thanks
    Last edited by MrCrud; 01-21-04 at 11:59.
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your table could look something like

    orderDetailsID 'why can't it have a PK? (tho you probably wont need it)
    orderID 'foreign key on tlbOrders
    itemID 'foreign key on wherever you keep your items for sale
    intQty
    dblUnitPrice
    dblDiscount
    dblExtendedPrice
    blah blah blah

    and your form recordsource is:
    SELECT * FROM tblOrderDetails WHERE orderID= whatever

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2003
    Posts
    150
    Hi,

    Thanks for the reply. I guess i could add an autonumber field to have a PK. But right now, i'm using the orderID and the productID as the key to this table, as you suggested.

    The SQL statement you show makes sense, but i'm not sure howto make the OrderID equal the OrderID of the previously opened form... in this case, frmOrders
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    aaaah.

    somewhere in there you said you closed the form... so you lost orderID at the same time.

    there are so many choices!
    my preferred solution for this sort of stuff is to have the order details as a subform of the order form... orderID stays alive, and it is somehow more human to see the order heading whilst you are editing the line items.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Nov 2003
    Posts
    150
    I totally agree with you. I havent taken rock-solid decisions about the interface my application will have.

    If i put the orderdetails as a subform of orders, how would i go about doing what i want?

    thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ok.

    assuming you have dragged frmOrderDetails into frmOrderHeader so it is now a subform
    ...called (check properties¦other¦name) "frmOrderDetails"
    then

    code in frmOrderDetails can "see"
    forms!frmOrderHeader.SelectedOrderID

    code in frmOrderHeader can "see"
    me.frmOrderDetails.anyControl

    but easiest is probably code in frmOrderHeader:
    me.frmOrderDetails.recordsource = "SELECT * FROM tblOrderDetails WHERE orderID = SelectedOrderID;"

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Nov 2003
    Posts
    150
    Kool, works fine now!

    Merci beaucoup du coup de main!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  8. #8
    Join Date
    Oct 2003
    Posts
    706

    Lightbulb

    I usually use a tabbed-notebook arrangement on a single form to handle lengthy documents like orders and invoices with limited screen-space. At the bottom of each page is a "Next >>>" button; the user is also able to click directly on the appropriate notebook-tab.

    Invoices, Orders and so-forth have a one-to-many relationship between the Invoice or Order itself, and the line-items which appear on that Invoice or Order.

    Usually, in the line-item table, I simply have the OrderID and a sequence-number. The product-ID being ordered is not part of the primary key; it is not even required! This allows me to have things like blank-lines, or the same product being listed twice on the same order. When an item is added to the order, the item's description is copied from the item reference-table to the appropriate line-item record so that it is editable on a per-invoice basis.

    You see, it's easy to make an ordering or invoicing system too rigid. Sometimes a single Purchase Order (for instance) may list an item twice because (and the clerk needs to be able to type this in, possibly adding a blank line or two) they need so-many units from the lot that the supplier says is in his warehouse now, and the balance from another lot to be shipped two weeks later. When the supplier invoices for it, he may choose to list the items twice on his invoice so that everything clearly matches up for the I.R.S. or the Auditors. Your system needs to be flexible enough to do that.

    I suggest that you grab a copy of a successful invoicing system like (ick...) Peachtree Accounting or QuickBooks just to see what kind of flexibility they've designed into their system. Yours needs to be comparable, although hopefully a lot better.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Usually, in the line-item table, I simply have the OrderID and a sequence-number. The product-ID being ordered is not part of the primary key; it is not even required! This allows me to have things like blank-lines, or the same product being listed twice on the same order. When an item is added to the order, the item's description is copied from the item reference-table to the appropriate line-item record so that it is editable on a per-invoice basis.
    ...is a VERY good observation.

    in fact, it is mandatory to do it this way otherwise your old invoices change any time you edit the description of a product.

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Nov 2003
    Posts
    150
    the system i'm building deals only with consumers, so there isnt any need to be able to modify purchase orders. In fact, this causes a major problem right now ( they still work with only paper ). Some employees cause quite a mess when they try to modify orders, instead of adding a new order.

    I understand what you are talking about though, and i can see where it would be useful in a B2B environment, but this isnt the case for me.

    I've thought about using tabs, but in the end, chose to put everything on the same page.

    Thanks for the input though!

    Cheers!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  11. #11
    Join Date
    Nov 2003
    Posts
    150
    Very good point... hadnt thought about that!!

    I'll be sure to add the necessary autonumber field to avoid this problem.
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  12. #12
    Join Date
    Nov 2003
    Posts
    150
    One last thing i forgot about:

    The subform should calculate the extended price ( Price per unit * Qty - discount ) as the user adds items.

    Should i base the subform on a query that performs the calculations, or is there another way to do this?

    thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

Posting Permissions

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