If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
invoice number
Noella explained how to do it. You make another table with 1 field. In the
field you put the first invoice number. When you print the invoice, set the invoice number to the value of that table.field. Then you run an update query to add one to the value in the field so that the next invoice will have the next number. Bonnie http://www.dataplus-svc.com cjgav wrote: hi I want to keep the same number . I enter the info in to the database to create a estimate for customer when the estimate is excepted and the job is completed I want a table with invoice date in which I have used an append query to update but I cannot create an invoice number without using autonum which will not produce the numbers I want . And what happens when you print the same invoice again later? Do you want a different invoice number to be created? [quoted text clipped - 8 lines] when the invoice is printed can anyone explain how to do this please -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200906/1 |
#12
|
|||
|
|||
invoice number
Hi,
The invoice numbers table can be designed as (example for INV = invoice and CN = credit note): tblInvoiceNumbers ----------------------------- inrID inrType inrYear inrLastUsed ------ ---------- ---------- ------------------ 1 INV 2008 256 2 CN 2008 36 3 INV 2009 106 4 CN 2009 12 The following function gets the invoice nr. and augments the field with 1 (please add your proper error handling) Public Function GetNextInvoiceNr(strType As String) As Long Dim rst As New ADODB.Recordset Dim cnn As ADODB.Connection Dim lngNr As Long Set cnn = CurrentProject.Connection rst.Open "select inrLastUsed from tblInvoiceNumbers where inrType = """ & _ strType & """ and inrYear = " & Year(Date), cnn, adOpenKeyset, adLockPessimistic With rst If Not (.BOF And .EOF) Then .MoveFirst lngNr = !inrLastUsed !inrLastUsed = !inrLastUsed + 1 .Update End If .Close End With GetNextInvoiceNr = lngNr + 1 End Function -- Kind regards Noëlla "cjgav" wrote: Hi Thank you for your help . I'm not sure about this if I 'm not using autonum to generate the numbers how do I do it? |
#14
|
|||
|
|||
invoice number
Hi Thank you for your help this looks very much like it. I do not understand vb very well at all do i need to edit function as it stands there seems to be a compile error on line rst.open "Noëlla Gabriël" wrote: Hi, The invoice numbers table can be designed as (example for INV = invoice and CN = credit note): tblInvoiceNumbers ----------------------------- inrID inrType inrYear inrLastUsed ------ ---------- ---------- ------------------ 1 INV 2008 256 2 CN 2008 36 3 INV 2009 106 4 CN 2009 12 The following function gets the invoice nr. and augments the field with 1 (please add your proper error handling) Public Function GetNextInvoiceNr(strType As String) As Long Dim rst As New ADODB.Recordset Dim cnn As ADODB.Connection Dim lngNr As Long Set cnn = CurrentProject.Connection rst.Open "select inrLastUsed from tblInvoiceNumbers where inrType = """ & _ strType & """ and inrYear = " & Year(Date), cnn, adOpenKeyset, adLockPessimistic With rst If Not (.BOF And .EOF) Then .MoveFirst lngNr = !inrLastUsed !inrLastUsed = !inrLastUsed + 1 .Update End If .Close End With GetNextInvoiceNr = lngNr + 1 End Function -- Kind regards Noëlla "cjgav" wrote: Hi Thank you for your help . I'm not sure about this if I 'm not using autonum to generate the numbers how do I do it? |
#15
|
|||
|
|||
invoice number
Hi,
You need to have a reference to the ADODB library in your project. Open a module (any module) , click tools - references and check the ADODB reference lib. I don't have my own computer now, so can't have a quick look now, but you'll find it :-) -- Kind regards Noëlla "cjgav" wrote: Hi Thank you for your help this looks very much like it. I do not understand vb very well at all do i need to edit function as it stands there seems to be a compile error on line rst.open "Noëlla Gabriël" wrote: Hi, The invoice numbers table can be designed as (example for INV = invoice and CN = credit note): tblInvoiceNumbers ----------------------------- inrID inrType inrYear inrLastUsed ------ ---------- ---------- ------------------ 1 INV 2008 256 2 CN 2008 36 3 INV 2009 106 4 CN 2009 12 The following function gets the invoice nr. and augments the field with 1 (please add your proper error handling) Public Function GetNextInvoiceNr(strType As String) As Long Dim rst As New ADODB.Recordset Dim cnn As ADODB.Connection Dim lngNr As Long Set cnn = CurrentProject.Connection rst.Open "select inrLastUsed from tblInvoiceNumbers where inrType = """ & _ strType & """ and inrYear = " & Year(Date), cnn, adOpenKeyset, adLockPessimistic With rst If Not (.BOF And .EOF) Then .MoveFirst lngNr = !inrLastUsed !inrLastUsed = !inrLastUsed + 1 .Update End If .Close End With GetNextInvoiceNr = lngNr + 1 End Function -- Kind regards Noëlla "cjgav" wrote: Hi Thank you for your help . I'm not sure about this if I 'm not using autonum to generate the numbers how do I do it? |
#16
|
|||
|
|||
invoice number
In my humble opinion, invoicing is a (one time) event (which typically
occurs the first time that it is printed) = a data entity which should be handled / stored as a data entity. IMHO you are running into a lot of challenges which are arising because your structure is not treating it as such. |
#17
|
|||
|
|||
invoice number
Hi Fred
That’s exactly what I am trying to do but I cannot generate an invoice number "Fred" wrote: In my humble opinion, invoicing is a (one time) event (which typically occurs the first time that it is printed) = a data entity which should be handled / stored as a data entity. IMHO you are running into a lot of challenges which are arising because your structure is not treating it as such. |
#18
|
|||
|
|||
invoice number
Hi
I've tried to do this but there is no ADODB LIBARY listed ! "Noëlla Gabriël" wrote: Hi, You need to have a reference to the ADODB library in your project. Open a module (any module) , click tools - references and check the ADODB reference lib. I don't have my own computer now, so can't have a quick look now, but you'll find it :-) -- Kind regards Noëlla "cjgav" wrote: Hi Thank you for your help this looks very much like it. I do not understand vb very well at all do i need to edit function as it stands there seems to be a compile error on line rst.open "Noëlla Gabriël" wrote: Hi, The invoice numbers table can be designed as (example for INV = invoice and CN = credit note): tblInvoiceNumbers ----------------------------- inrID inrType inrYear inrLastUsed ------ ---------- ---------- ------------------ 1 INV 2008 256 2 CN 2008 36 3 INV 2009 106 4 CN 2009 12 The following function gets the invoice nr. and augments the field with 1 (please add your proper error handling) Public Function GetNextInvoiceNr(strType As String) As Long Dim rst As New ADODB.Recordset Dim cnn As ADODB.Connection Dim lngNr As Long Set cnn = CurrentProject.Connection rst.Open "select inrLastUsed from tblInvoiceNumbers where inrType = """ & _ strType & """ and inrYear = " & Year(Date), cnn, adOpenKeyset, adLockPessimistic With rst If Not (.BOF And .EOF) Then .MoveFirst lngNr = !inrLastUsed !inrLastUsed = !inrLastUsed + 1 .Update End If .Close End With GetNextInvoiceNr = lngNr + 1 End Function -- Kind regards Noëlla "cjgav" wrote: Hi Thank you for your help . I'm not sure about this if I 'm not using autonum to generate the numbers how do I do it? |
#19
|
|||
|
|||
invoice number
Hi,
the reference is called: Microsoft ActiveX Data Objects x.x library if that's installed and you still get errors, check the tablenames and fieldnames in the expression. They should match the existing table in your database. -- Kind regards Noëlla "cjgav" wrote: Hi I've tried to do this but there is no ADODB LIBARY listed ! "Noëlla Gabriël" wrote: Hi, You need to have a reference to the ADODB library in your project. Open a module (any module) , click tools - references and check the ADODB reference lib. I don't have my own computer now, so can't have a quick look now, but you'll find it :-) -- Kind regards Noëlla "cjgav" wrote: Hi Thank you for your help this looks very much like it. I do not understand vb very well at all do i need to edit function as it stands there seems to be a compile error on line rst.open |
#20
|
|||
|
|||
invoice number
Under my described thought process, you would create the record in the
invoice table and create an invoice number at the time that the invoice event occurred. The first printing would happen a few seconds AFTER that process. And later you could print extra copies of that invoice if needed. |
Thread Tools | |
Display Modes | |
|
|