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

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
>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>

> 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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>> 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