change the order of items on an invoice

[Christopher Glaeser]

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

 

[Wayne-I-M]

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

 

[Allen Browne]

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

 

[Pat Hartman \(MVP\)]

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.

 

[Christopher Glaeser]

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]

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.