PC Review


Reply
Thread Tools Rate Thread

change the order of items on an invoice

 
 
Christopher Glaeser
Guest
Posts: n/a
 
      19th Jun 2007
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



 
Reply With Quote
 
 
 
 
=?Utf-8?B?V2F5bmUtSS1N?=
Guest
Posts: n/a
 
      19th Jun 2007
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
>
>
>
>

 
Reply With Quote
 
 
 
 
Allen Browne
Guest
Posts: n/a
 
      19th Jun 2007
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


 
Reply With Quote
 
Pat Hartman \(MVP\)
Guest
Posts: n/a
 
      19th Jun 2007
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

>



 
Reply With Quote
 
Christopher Glaeser
Guest
Posts: n/a
 
      19th Jun 2007
> 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


 
Reply With Quote
 
Allen Browne
Guest
Posts: n/a
 
      20th Jun 2007
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


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I change the invoice number assigned in Invoice template... =?Utf-8?B?YWtyZXNz?= Microsoft Excel Misc 1 28th Feb 2005 07:36 PM
Invoice Template with Autoupdate for Invoice Number Barb Reinhardt Microsoft Word Document Management 2 10th Dec 2004 09:13 PM
creating an invoice/invoice details form/subform Mike \(AAA- AnotherAccessAmatuer\) Microsoft Access Forms 2 6th Jul 2004 08:55 PM
That old Invoice toolbar in Excel 97's Invoice Template =?Utf-8?B?V2FycmlvciBPbmU=?= Microsoft Excel Misc 1 9th Apr 2004 04:31 PM
where is invoice under toolbar for excel 2002 invoice templet brian bernt Microsoft Excel Misc 3 28th Dec 2003 10:58 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:34 AM.