By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

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

P: 14
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.
Feb 12 '09 #1
Share this Question
Share on Google+
12 Replies

Expert 100+
P: 903
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.


Feb 12 '09 #2

P: 14
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.
Attached Files
File Type: pdf Relationships for Invoice.pdf (8.1 KB, 304 views)
Feb 12 '09 #3

Expert 100+
P: 903
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.

Feb 12 '09 #4

Expert 100+
P: 489
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.
Feb 12 '09 #5

Expert Mod 15k+
P: 31,419
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!
Feb 13 '09 #6

Expert 100+
P: 903
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.


Feb 13 '09 #7

Expert Mod 15k+
P: 31,419
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.
Feb 13 '09 #8

P: 14
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
Attached Files
File Type: pdf Invoice structure.pdf (7.5 KB, 322 views)
Feb 14 '09 #9

Expert Mod 15k+
P: 31,419
Does this not answer your question?
Feb 15 '09 #10

Expert 100+
P: 903
What you are asking is a pretty open ended question.

The basic formula of


comes to mind.

If you could be a little more specific of where you are having trouble we can be of more help.

Have you got your recordset filtered to a specific invoice, do you use subforms to display line items, do you need to know how to retrieve the highest value from the line field, how do you take into account deleting a line item and then rearrange the line numbers, etc etc..

Show us the code you currently have and where the trouble is.

Feb 16 '09 #11

P: 10
Is this an Autonumber field? If not, just set it to 0 and increment as you add items. Is the Line Number field bound?

You could create a temp table and copy the data to that table. Dropping and Creating the table should reset it to 0.
Feb 17 '09 #12

Expert 2.5K+
P: 2,653
Hi, Coni.

Using method described here (particularly Part 1) you could build an interface where invoice items subform shows several (quantity is adjustable) numerated rows to put info to.

Feb 20 '09 #13

Post your reply

Sign in to post your reply or Sign up for a free account.