invoice number

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

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

--
Message posted via http://www.accessmonster.com

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

"bhicks11 via AccessMonster.com" wrote:

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

--
Message posted via http://www.accessmonster.com

On Jun 7, 3:07*pm, cjgav wrote:
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

"bhicks11 via AccessMonster.com" wrote:
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 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

--
Message posted viahttp://www.accessmonster.com

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.

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
http://I.Am/DougSteele
(no private e-mails, please)


"Piet Linden" wrote in message
...
On Jun 7, 3:07 pm, cjgav wrote:
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

"bhicks11 via AccessMonster.com" wrote:
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 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.

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

"Piet Linden" wrote:

On Jun 7, 3:07 pm, cjgav wrote:
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

"bhicks11 via AccessMonster.com" wrote:
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 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

--
Message posted viahttp://www.accessmonster.com

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.

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 the 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.
--
Kind regards
Noëlla


"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

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?

"Noëlla Gabriël" wrote:

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 the 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.
--
Kind regards
Noëlla


"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

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?

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?