 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / November 2005Help:creating a new invoice?
Hi there i have a little problem with my invoice. Here it is: i have a main menu with buttons, one of my button is "Create new invoice", when click on it a form pop up so i can enter my information and one of the field (the user cannot change the info in it.) is invoice #. Right now, everythime i click on "Create new invoice", the invoice # add 1. But my problem is sometime the employee dont fill it (for x reason) and shut it down, the nest time they will click on "Create new invoice" the invoice # will be 1 more then the last one. There will be a blank invoice between them. Is there a way to get rid of this?? thx a lot. > Hi there i have a little problem with my invoice. > [quoted text clipped - 12 lines] > > thx a lot. Have the employee supply required information before creating a new invoice number. how do i do that? any hint? have the button that creates the invoice check that all required fields are filled in first. have the button that creates the invoice check that all required fields are filled in first - before allowing an invoice to be created at all. > how do i do that? any hint? Have your Create Invoice button display an unbound dialog box showing only the required fields with an OK and CANCEL button. If the user clicks OK, verify the required fields, append the data and next invoice number to the table and open the Invoice form to the new record. You could also add a user ID field to your invoice table to identify who created each invoice. Yes i'm using the auto number....and not +1.. >Yes i'm using the auto number....and not +1.. That's risky in this situation. Auditors will ask questions if they find gaps in the invoice number sequence. It's far better to create the invoice numbers yourself so that you have complete control over them.  SignatureBernard Peek London, UK. DBA, Manager, Trainer & Author. ok, but i dont want them to enter it manually, to risquy i think, and they wont remember what was the last invoice number? If it's a numeric column in the table for invoice number you can use DMax() to get the highest existing value then add 1 to it. SignatureNick Coe (UK) Available - Will work for money :-) http://www.alphacos.co.uk/ AccHelp + pAnimal http://www.pjandcoe.co.uk/ Online Store In news:1131029792.984473.23260@g14g2000cwa.googlegroups.com, NomoreSpam4Me@hotmail.com typed: > ok, but i dont want them to enter it manually, to risquy i > think, and they wont remember what was the last invoice > number? and on my form i have a Save button and a Back tu main menu button. even if i dont save it the autonumber goes up by 1 each time. >and on my form i have a Save button and a Back tu main menu button. > >even if i dont save it the autonumber goes up by 1 each time. One way to handle this is to search the invoice table for the largest invoice number, add one to it and then use this as the next invoice number. If it is a large table this could be slow. Another approach is to use a table that has one row and one field. When you create an invoice you: 1. read the number stored there 2. add one to it 3. store the new number, overwriting the old one 4. create the invoice record using the new number The order that you do things can be important. If the process is interrupted or fails part way it is better to have a gap in the sequence than to have two invoices with the same number. SignatureBernard Peek London, UK. DBA, Manager, Trainer & Author. > >and on my form i have a Save button and a Back tu main menu button. > > [quoted text clipped - 15 lines] > interrupted or fails part way it is better to have a gap in the sequence > than to have two invoices with the same number. dMAX will not take long if the Invoice number is indexed. You can't have a duplicate invoice if the invoice number is the primary key. You will need to check that your append query succeeded before opening the invoice form, because a 2nd user may grab the next invoice number a split second before the 1st > >and on my form i have a Save button and a Back tu main menu button. > > [quoted text clipped - 15 lines] > interrupted or fails part way it is better to have a gap in the sequence > than to have two invoices with the same number. Dmax would search a large table quickly if Invoice number is indexed. Invoice number could not be duplicated if Invoice number is the primary key. You would need to check that the append query succeeded before opening to the new invoice. A 2nd user may grab the next number just before the 1st, causing a key violation in the append query. >Hi there i have a little problem with my invoice. > [quoted text clipped - 10 lines] > >Is there a way to get rid of this?? It depends on how the new invoice numbers are created. What you need is a system that creates a new invoice number in the before_insert trigger of the form. Are you using autonumber? SignatureBernard Peek London, UK. DBA, Manager, Trainer & Author. > Hi there i have a little problem with my invoice. > [quoted text clipped - 12 lines] > > thx a lot. Is the Invoice number an autonumber field? If so, this is to be expected. An autonumber is not a good method to use for sequential numbers. An autonumber is GREAT as a key field to link to other tables. If you enter anything into the form, the autonumber increments. Even if you undo your changes, the next time the invoice form is opened, a new number will be one more than the last time an autonumber was incremented, whether or not the record was saved. So you can expect holes in the sequence. The better thing to do is make the invoice number field in your table a LongInt number. Simply remove the Autonumber type from the table and change it to Number, type Long. And make it the primary key. Now you need to determine when you want the record to create new invoice number. If ONLY ONE person is updating/creating invoices, you can do something like If Me.NewRecord then Me.InvoiceNumber = Dmax("InvoiceNumber","InvoiceTable") Endif anytime you want. If you are going to have multiple people entering invoice numbers at the same time, you might want to do that in the BeforeUpdate event of the form...IOW, just prior to saving the record. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.