C
cjgav
I would like to add an invoice number to report which is generated only when
the invoice is printed can anyone explain how to do this please
the invoice is printed can anyone explain how to do this please
C
cjgav
Hi
Thanks for your help i;ve done that ut the problem is the number starts at 1
and I would like to tell it were to start
Thanks for your help i;ve done that ut the problem is the number starts at 1
and I would like to tell it were to start
P
Piet Linden
remove the autonumber (make the field type long integer). Add a
record. Set the value of invoiceNumber to be one less than the one
you want. Change the field type back to autonumber.
Compact the database. And there you go.
D
Douglas J. Steele
Don't think so, Piet.
I don't believe it's possible to change the datatype of a field to
Autonumber if the table already has data.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
remove the autonumber (make the field type long integer). Add a
record. Set the value of invoiceNumber to be one less than the one
you want. Change the field type back to autonumber.
Compact the database. And there you go.
I don't believe it's possible to change the datatype of a field to
Autonumber if the table already has data.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
remove the autonumber (make the field type long integer). Add a
record. Set the value of invoiceNumber to be one less than the one
you want. Change the field type back to autonumber.
Compact the database. And there you go.
C
cjgav
Hi
I've tried that but it seems this is not possible
I've tried that but it seems this is not possible
Piet Linden said:
N
Noëlla Gabriël
Hi,
as invoice numbers have to follow without holes, never take an autonumber.
You'll always run into trouble later. Create a separate table which holds the
invoice numbers. Each time you need a new invoice number, you look up the
last used, add 1 to it and then update the number in the new table.
I always have three fields there: last invoice nr. last year, last invoice
nr. this year, and last invoice nr. next year. This helps around the end of
December, start of January.
as invoice numbers have to follow without holes, never take an autonumber.
You'll always run into trouble later. Create a separate table which holds the
invoice numbers. Each time you need a new invoice number, you look up the
last used, add 1 to it and then update the number in the new table.
I always have three fields there: last invoice nr. last year, last invoice
nr. this year, and last invoice nr. next year. This helps around the end of
December, start of January.
C
cjgav
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?
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?
C
cjgav
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 .
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 .
N
Noëlla Gabriël
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
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
C
cjgav
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
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
N
Noëlla Gabriël
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
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
F
Fred
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.
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.
C
cjgav
Hi Fred
That’s exactly what I am trying to do but I cannot generate an invoice number
That’s exactly what I am trying to do but I cannot generate an invoice number
C
cjgav
Hi
I've tried to do this but there is no ADODB LIBARY listed !
I've tried to do this but there is no ADODB LIBARY listed !
N
Noëlla Gabriël
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.
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.
F
Fred
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.
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.
C
cjgav
Hi
My problem is I donot know how to generate a number without using autonum
which is unsuitable .
My problem is I donot know how to generate a number without using autonum
which is unsuitable .
C
cjgav
Hi Steve
Thanks for that not sure were to put the Dmax formula so it updates the
invoice number when I print also this number would need to be stored in the
invoice table and not change .
Thanks for that not sure were to put the Dmax formula so it updates the
invoice number when I print also this number would need to be stored in the
invoice table and not change .
C
cjgav
Hi,
That reference was already checked .
I've checked the table name and all the field names and I still receive a
syntax error in this line:
rst.Open "select inrLastUsed from tblInvoiceNumbers where inrType = """ & _
strType & """ and inrYear = " & Year(Date), cnn, adOpenKeyset,
Regards
That reference was already checked .
I've checked the table name and all the field names and I still receive a
syntax error in this line:
rst.Open "select inrLastUsed from tblInvoiceNumbers where inrType = """ & _
strType & """ and inrYear = " & Year(Date), cnn, adOpenKeyset,
Regards
C
cjgav
Hi Steve
I think i'm been a bit stupid here
To test this I've setup a table (invoices) with 1 field (Invoicenumber)
I've entered the formula DMax("[InvoiceNumber]","TblInvoice") + 1 in an
event procedure on a form when I run this event I get a syntax error.
What am I doing wrong?
regards
I think i'm been a bit stupid here
To test this I've setup a table (invoices) with 1 field (Invoicenumber)
I've entered the formula DMax("[InvoiceNumber]","TblInvoice") + 1 in an
event procedure on a form when I run this event I get a syntax error.
What am I doing wrong?
regards