How to reset a number field to 0 for a new invoice

[Coni]

Hi All,

I am working in Access to print Invoices. On each invoice there is a line number column which is a field to number each line for a product purchase. I would like to know if there is a way to reset the number field (line-number field) to 0 for each invoice created. This would be incremented by 1 for each product purchase. Example below:

Invoice # line number product price
0123 1 Sugar $10.00
0123 2 Rice $10.00
0123 3 Peas $5.00
0155 1 Banana $2.00
0155 2 Orange $5.00
0159 1 Peas $4.00


Thanks for your help. The above list is not format as I would like. But just picture an invoice with line numbers for each item. But when a new invoice is created the line number is reset. Also is there a way to increment the numbers beside using autonumber.

[mshmyob]

You cannot reset the an autonumber field unless nothing is in that table then you can delete all data and compact and repair.

SHow us your schema so we can see what you are trying to do.

cheers,

@Coni

[Coni]

Hi mshmyob,

By schema are you refering to the relationship. I have attached a copy of the relationship in pdf format. As far as the autonumber, I know that will not work. I wanted to know if there were other means whether by programming or some other expression. Thank you.

[mshmyob]

Depends on you PK in your LINE table.

If your PK is just Inv_No and Prod_Code then that means each product can only appear once per invoice, therefore line number is just programatically entered. Each line number per invoice can just start at 1 and increment by 1 for each item added to invoice. No need to zero out the line numbers.

If you made Inv_No and Prod_Code and Line_Number your PK then that means a product may appear more than once on the same invoice, but again no need to zero out line numbers.

Maybe you could explain why you need to zero them out? I cannot see any reason to zero them out but I may be missing something.

cheers,

[DonRayner]

If you want line numbers to appear on the printed invoice why not just add them at the report level. The database itself doesn't require them.

[NeoPa]

A report control (TextBox) can be set up to sum its values within a grouping. If the value is 1 every time, the effect you will get is numbered lines within the grouping. Define the grouping as the invoice and you should have what you need.

Welcome to Bytes!

[mshmyob]

I will differ with the opinion that the line numbers are not needed in the table.

You would want the line numbers in his bridge table as he has so that when looking back at an existing invoice you can sort on line number so that they always display as the line items were entered originally.

cheers,

@DonRayner

[NeoPa]

I can't argue with that point.

A transaction management system with multiple lines per transaction does generally need a field to indicate the original order of items. Even without the Invoicing issue, this would still be required.

In such a case, this should be determined (and saved) at the time of entry.

[Coni]

Hi Mshmyob,
I think you understand exactly what I am trying to say. I really don't need to zero out the line numbers. I want for each item to start at 1 and increment by 1 for additional items. In the Line table, invoice number and line number are unique and they are the primary keys. I just want to know if there is a way to reset the number back to 1 for a new invoice number. The line numbers on each invoice are also useful for when customers are referring to an item, you can know exactly which item on the invoice they are speaking about. I have attached a better description of the structure of the database using Visio. The attached copy can be viewed as a pdf file. Is there any way to reset the line number to 1 for each new invoice and a way to programatically increment the line number for each addtional items added

[NeoPa]

@NeoPa
Does this not answer your question?