invoice number

[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

 

[bhicks11 via AccessMonster.com]

One solution might be to base InvoiceNo field on an autonum field. Run code
to add the autonum (or some variation of it) to the InvoiceNo field
before/when printing the invoice.

Bonnie
http://www.dataplus-svc.com

 

[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

 

[Piet Linden]

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

One solution might be to base InvoiceNo field on an autonum field.  Run code
to add the autonum (or some variation of it) to the InvoiceNo field
before/when printing the invoice.  

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.

 

[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)

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

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.

 

[cjgav]

Hi
I've tried that but it seems this is not possible

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

One solution might be to base InvoiceNo field on an autonum field. Run code
to add the autonum (or some variation of it) to the InvoiceNo field
before/when printing the invoice.

cjgav wrote:
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

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.

 

[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.

 

[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?

 

[Steve]

And what happens when you print the same invoice again later? Do you want a
different invoice number to be created?

Why don't you create the invoice number when you enter the invoice data in
your invoice form?

Steve
(e-mail address removed)

 

[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 .

 

[bhicks11 via AccessMonster.com]

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

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]

 

[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

 

[Steve]

TblEstimate
EstimateID
CustomerID
EstimateDate
etc

TblInvoice
InvoiceID
EstimateID
EstmateAcceptedDate
EstimateCompletedDate
InvoiceNumber
InvoiceDate
InvoiceAmount

Use a form/subform to enter the data in these two tables. At the time you
enter InvoiceDate and InvoiceAmount, you can automatically generate
InvoiceNumber with:
DMax("[InvoiceNumber]","TblInvoice") + 1

Steve
(e-mail address removed)

 

[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

 

[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

 

[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.

 

[cjgav]

Hi Fred
That’s exactly what I am trying to do but I cannot generate an invoice number

 

[cjgav]

Hi
I've tried to do this but there is no ADODB LIBARY listed !

 

[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.

 

[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.