 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / December 2005Creating an auto invoice no!
I am creating an Invoice and I wish to add a invoice no. to every invoice I print. Following condtions should be met. - Click on a button from the order form will create an invoice report - Invoice no. should be unique and bound to the field orders.InvoiceNo - Invoice no. should be automatically generated and incremented for every invoice I print - Invoice no. can be overwriten by the user in the order form. (invoice no. should not be incremented for this Invoice, if the invoice no is already exist) only the InvoiceNr value "0" or null allow an increment and adding a invoice no. Is there any simple way to do it? Thank you regards Nathan Why do you want the user to be able to manually enter an Invoice Number? Is it because you want the user to be able to edit an existing Invoice? TC This is because, in case the the printed invoice containg any wrong data, it should be reprinted with the same invoice no. I do not want it incremented in this case.  SignatureMed venlig hilsen Nathan Thurairajasingham http://www.remozone.dk salg@remozone.dk > Why do you want the user to be able to manually enter an Invoice > Number? > > Is it because you want the user to be able to edit an existing Invoice? > > TC But if you want to edit an Invoice, you should go to a form which lets you recall that Invoice, by Invoice Number (for example), and lets you edit the relevant fields, /but not/ edit the invoice number. You are going the wrong way, IMHO, if you let a user edit an /invoice number/. When they create a new invoice, the system should detemine that number automatically. When they edit an existing invoice, the system should not let them edit that number, at all. HTH, TC What I am trying to do is ofcourse not tring to edit the invoice, but rather the invoice no. in the order form. I do not want to put the Invoice no. manually. I wanted the script to generate the no. and the no. should existing in continues form. so every time I need to find a highest no. and add one to that, so the Invoice no is printed on the invoice. If I find out for any reason the invoice containing a wrong data, I need to go back to the order form, correct the data and print the new invoice. in this case my invoice no. should remain the same. There is another problem as well, since not all orders have invoices(if the coustomer not paid or the cancelled), I am difficult to find a way to generate an invoice no. only when the invoice is printed. if no invoice been printed, the invoice no. should remain blank. Signatureregards Nathan > But if you want to edit an Invoice, you should go to a form which lets > you recall that Invoice, by Invoice Number (for example), and lets you [quoted text clipped - 7 lines] > HTH, > TC You say you need to be able to correct the order data and then print the new invoice; "in this case my invoice no. should remain the same". Sure - I understand that. But you suggested in your original post, that the user had to do this manually; ie. they have some degree of /manual/ control over the value of the invoice number. "Invoice no. can be overwriten by the user in the order form." In my opinion, there is no case where the user should /ever/ have manual control of an Invoice # field, except when they are entering an invoice # to do a query on. In all other cases, the invoice # should be managed automatically, by the software, without any user intervention. If a new invoice # is required, eg. when a new invoice has been generated, the software should do that automatically. But /conversely/, if a new invoice # is /not/ required, eg. when an existing invoice is being reprinted, the software should recognize that case, and just not generate a new number. In other words, it shouldn't be up to the user to decide whether he will or will not overwrite the invoice number. You should neither require, nor allow, him to do that, IMHO. Cheers, TC >I am creating an Invoice and I wish to add a invoice no. to every invoice I >print. [quoted text clipped - 11 lines] > >Is there any simple way to do it? Do all your data entry with a Form, of course. In the Form's BeforeInsert event put code like Private Sub Form_BeforeInsert(Cancel as Integer) Me!txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Orders]")) + 1 End Sub assuming that the form control txtInvoiceNo is bound to the invoice number table field. John W. Vinson[MVP] John, The increment is working fine. But it allow the same InvoiceNo to be assign to multiple records. I need it to be unique. Any help? regards Nathan >>I am creating an Invoice and I wish to add a invoice no. to every invoice >>I [quoted text clipped - 27 lines] > > John W. Vinson[MVP] > John, > The increment is working fine. But it allow the same InvoiceNo to be > assign to multiple records. I need it to be unique. Any help? BeforeInsert is not an appropriate event in a multi-user environment. Use BeforeUpdate instead. SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Hi, I am the only user. so there will be no issues with the multi user problems. regards Nathan >> John, >> The increment is working fine. But it allow the same InvoiceNo to be >> assign to multiple records. I need it to be unique. Any help? > > BeforeInsert is not an appropriate event in a multi-user environment. Use > BeforeUpdate instead. >John, >The increment is working fine. But it allow the same InvoiceNo to be assign >to multiple records. I need it to be unique. Any help? Make InvoiceNo the Primary Key of the invoice table (preferable), or use the Index property of the field to uniquely index it. John W. Vinson[MVP] Hi, There are no invoice table. only the order table the promary key is already assigned to orderID I tried to use the index property, but access not allowing me to do it since there other relation involved. SignatureRegards Nathan >>John, >>The increment is working fine. But it allow the same InvoiceNo to be [quoted text clipped - 5 lines] > > John W. Vinson[MVP] Er - how can you have an invoice in a single table? Typically, an Invoice requires at least /four/ tables: InvoiceHeader, InvoiceDetail, Customer, and Product. What table structure are you using? What is the primary key of each table? HTH, TC >Hi, >There are no invoice table. only the order table the promary key is already >assigned to orderID >I tried to use the index property, but access not allowing me to do it since >there other relation involved. Well, I'm with TC then - what is the structure of your database? If you want to have a unique invoice number, and if you're recording information about invoices (e.g. date issued), I don't see how you can AVOID having an Invoice table, at least not without violating database integrity! John W. Vinson[MVP] My bet is, his (one?) table actually contains an Order, using repeating fields for the line-items. Then, if necessary, he turns it into an invoice, by setting a value into the Invoice # field. Or somesuch! :-) TC |
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.