change the order of items on an invoice

Discussion in 'Microsoft Access' started by Christopher Glaeser, Jun 19, 2007.

  1. If an invoice has grociery items, then the order of the items on the invoice
    is not important. For example:

    apples - $1.29
    oranges - $1.15
    pears - $1.17

    However, some proposals/invoices have items that must be ordered, such as
    construction invoices with tasks. For example:

    - acquire permit
    - remove old roof
    - apply underlayment
    - apply shingles
    - final building inspection

    Now, suppose the customer reviews the proposal and decides they want gutters
    added to the proposal. The revised proposal is now:

    - acquire permit
    - remove old roof
    - remove gutters
    - apply underlayment
    - apply shingles
    - install seamless aluminum gutters and downspouts
    - final building inspection

    Most examples on creating invoices in Access do not allow items to be
    inserted and rearranged. Can you point me in the right direction, perhaps
    with an example or suggestions on the table design.

    Best,
    Christopher
     
    Christopher Glaeser, Jun 19, 2007
    #1
    1. Advertisements

  2. Christopher Glaeser

    Guest Guest

    Hi Chistropher

    Is there a table where all the possible proccesses are stored.
    So you may have
    acquire permit
    remove old roof
    remove gutters
    apply underlayment
    apply shingles
    install seamless aluminum gutters and downspouts
    final building inspection

    plus (maybe)
    return to inspect work after 4 weeks
    or
    pre-site survey
    etc
    etc

    Just a thought


    --
    Wayne
    Manchester, England.



    "Christopher Glaeser" wrote:

    > If an invoice has grociery items, then the order of the items on the invoice
    > is not important. For example:
    >
    > apples - $1.29
    > oranges - $1.15
    > pears - $1.17
    >
    > However, some proposals/invoices have items that must be ordered, such as
    > construction invoices with tasks. For example:
    >
    > - acquire permit
    > - remove old roof
    > - apply underlayment
    > - apply shingles
    > - final building inspection
    >
    > Now, suppose the customer reviews the proposal and decides they want gutters
    > added to the proposal. The revised proposal is now:
    >
    > - acquire permit
    > - remove old roof
    > - remove gutters
    > - apply underlayment
    > - apply shingles
    > - install seamless aluminum gutters and downspouts
    > - final building inspection
    >
    > Most examples on creating invoices in Access do not allow items to be
    > inserted and rearranged. Can you point me in the right direction, perhaps
    > with an example or suggestions on the table design.
    >
    > Best,
    > Christopher
    >
    >
    >
    >
     
    Guest, Jun 19, 2007
    #2
    1. Advertisements

  3. Christopher Glaeser

    Allen Browne Guest

    Presumably you have one table for the invoice header (shown on the main
    form), and a related table for the invoice line items (shown in the
    subform.) The related table will have fields like this:
    InvoiceDetailID AutoNumber primary key
    InvoiceID Number which invoice this line belongs
    to.
    Quantity Number
    Item probably relates to Product.ProductID
    PriceEach Currency

    If you want to define the order of the items, you need to add another field
    to the InvoiceDetail table, e.g.:
    SortOrder Number
    The user can then enter whatever number they want, and you sort the items on
    your invoice report by this field.

    You don't have to make the SortOrder field uniquely indexed. Allowing
    duplicates makes it easy to swap values. For example, if you want to swap
    row 2 and 3, and they are uniquely indexed, you would need to change to 2 to
    (say) 999, so you can change the 3 to 2 and then the 999 to 3. Similarly,
    the numbers don't have to be sequential: they just sort in order.

    The final piece of the puzzle is to automatically offer the next available
    number at the point when the user starts entering a new row on the invoice.
    Use the subform's BeforeInsert event procedure. Something like this (aircode
    that needs changing to match your table and field names):

    Private Sub Form_BeforeInsert(Cancel As Integer)
    With Me.Parent
    If .NewRecord Then
    Cancel = True
    MsgBox "Enter the invoice header in the main form first."
    Else
    Me.SortOrder = Nz(DMax("SortOrder", "InvoiceDetail", _
    "InvoiceID = " & !InvoiceID), 0) + 1
    End If
    End With
    End Sub

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Christopher Glaeser" <> wrote in message
    news:...
    > If an invoice has grociery items, then the order of the items on the
    > invoice is not important. For example:
    >
    > apples - $1.29
    > oranges - $1.15
    > pears - $1.17
    >
    > However, some proposals/invoices have items that must be ordered, such as
    > construction invoices with tasks. For example:
    >
    > - acquire permit
    > - remove old roof
    > - apply underlayment
    > - apply shingles
    > - final building inspection
    >
    > Now, suppose the customer reviews the proposal and decides they want
    > gutters added to the proposal. The revised proposal is now:
    >
    > - acquire permit
    > - remove old roof
    > - remove gutters
    > - apply underlayment
    > - apply shingles
    > - install seamless aluminum gutters and downspouts
    > - final building inspection
    >
    > Most examples on creating invoices in Access do not allow items to be
    > inserted and rearranged. Can you point me in the right direction, perhaps
    > with an example or suggestions on the table design.
    >
    > Best,
    > Christopher
     
    Allen Browne, Jun 19, 2007
    #3
  4. I would make one enhancement to Allen's suggestion. Instead of incrementing
    by 1, increment by 10 or some other number to allow room for inserts without
    having to renumber.

    "Allen Browne" <> wrote in message
    news:...
    > Presumably you have one table for the invoice header (shown on the main
    > form), and a related table for the invoice line items (shown in the
    > subform.) The related table will have fields like this:
    > InvoiceDetailID AutoNumber primary key
    > InvoiceID Number which invoice this line belongs
    > to.
    > Quantity Number
    > Item probably relates to
    > Product.ProductID
    > PriceEach Currency
    >
    > If you want to define the order of the items, you need to add another
    > field to the InvoiceDetail table, e.g.:
    > SortOrder Number
    > The user can then enter whatever number they want, and you sort the items
    > on your invoice report by this field.
    >
    > You don't have to make the SortOrder field uniquely indexed. Allowing
    > duplicates makes it easy to swap values. For example, if you want to swap
    > row 2 and 3, and they are uniquely indexed, you would need to change to 2
    > to (say) 999, so you can change the 3 to 2 and then the 999 to 3.
    > Similarly, the numbers don't have to be sequential: they just sort in
    > order.
    >
    > The final piece of the puzzle is to automatically offer the next available
    > number at the point when the user starts entering a new row on the
    > invoice. Use the subform's BeforeInsert event procedure. Something like
    > this (aircode that needs changing to match your table and field names):
    >
    > Private Sub Form_BeforeInsert(Cancel As Integer)
    > With Me.Parent
    > If .NewRecord Then
    > Cancel = True
    > MsgBox "Enter the invoice header in the main form first."
    > Else
    > Me.SortOrder = Nz(DMax("SortOrder", "InvoiceDetail", _
    > "InvoiceID = " & !InvoiceID), 0) + 1
    > End If
    > End With
    > End Sub
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Christopher Glaeser" <> wrote in message
    > news:...
    >> If an invoice has grociery items, then the order of the items on the
    >> invoice is not important. For example:
    >>
    >> apples - $1.29
    >> oranges - $1.15
    >> pears - $1.17
    >>
    >> However, some proposals/invoices have items that must be ordered, such as
    >> construction invoices with tasks. For example:
    >>
    >> - acquire permit
    >> - remove old roof
    >> - apply underlayment
    >> - apply shingles
    >> - final building inspection
    >>
    >> Now, suppose the customer reviews the proposal and decides they want
    >> gutters added to the proposal. The revised proposal is now:
    >>
    >> - acquire permit
    >> - remove old roof
    >> - remove gutters
    >> - apply underlayment
    >> - apply shingles
    >> - install seamless aluminum gutters and downspouts
    >> - final building inspection
    >>
    >> Most examples on creating invoices in Access do not allow items to be
    >> inserted and rearranged. Can you point me in the right direction,
    >> perhaps with an example or suggestions on the table design.
    >>
    >> Best,
    >> Christopher

    >
     
    Pat Hartman \(MVP\), Jun 19, 2007
    #4
  5. > If you want to define the order of the items, you need to add another
    > field to the InvoiceDetail table, e.g.:
    > SortOrder Number
    > The user can then enter whatever number they want, and you sort the items
    > on your invoice report by this field.


    Thanks, this is very helpful.

    One more quick question. I was thinking of adding Up/Down icons next to
    each invoice item. So, to insert a new item, the user would add an item,
    and then click/click/click to move it into position. The number of items is
    usually between 1 and 8, so the number of moves would be modest. The code
    would resort after each click. Mostly, I want to make sure I'm not
    reinventing a feature or paradigm that is already available in Access. I'm
    guessing drag-and-drop, although user freindly, is more work if doable.

    Best,
    Christopher
     
    Christopher Glaeser, Jun 19, 2007
    #5
  6. Christopher Glaeser

    Allen Browne Guest

    You can code that if you loop through the RecordsetClone of the records in
    the form to renumber them, or exeute update query statements on the table.

    I don't think there's any built-in functionality to do this for you, so it's
    not trivial.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Christopher Glaeser" <> wrote in message
    news:...
    >> If you want to define the order of the items, you need to add another
    >> field to the InvoiceDetail table, e.g.:
    >> SortOrder Number
    >> The user can then enter whatever number they want, and you sort the items
    >> on your invoice report by this field.

    >
    > Thanks, this is very helpful.
    >
    > One more quick question. I was thinking of adding Up/Down icons next to
    > each invoice item. So, to insert a new item, the user would add an item,
    > and then click/click/click to move it into position. The number of items
    > is usually between 1 and 8, so the number of moves would be modest. The
    > code would resort after each click. Mostly, I want to make sure I'm not
    > reinventing a feature or paradigm that is already available in Access.
    > I'm guessing drag-and-drop, although user freindly, is more work if
    > doable.
    >
    > Best,
    > Christopher
     
    Allen Browne, Jun 20, 2007
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    1
    Views:
    747
    Brendan Reynolds
    Jan 19, 2006
  2. Guest

    Custom invoice date = invoice date in SBA

    Guest, Feb 14, 2006, in forum: Microsoft Access
    Replies:
    3
    Views:
    187
    Guest
    Feb 14, 2006
  3. Guest
    Replies:
    0
    Views:
    280
    Guest
    May 3, 2006
  4. Replies:
    1
    Views:
    243
    michael goodall
    Feb 6, 2007
  5. richard
    Replies:
    4
    Views:
    703
    Keven Denen
    Jul 26, 2009
Loading...

Share This Page