invoice number

  • Thread starter Thread starter cjgav
  • Start date Start date
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
 
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
 
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 said:
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.  
Click to expand...
Click to expand...

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

(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
Click to expand...

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 said:
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 said:
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
Click to expand...
Click to expand...

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.
Click to expand...
 
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.
 
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
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 .
 
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
 
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
 
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 :-)
 
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.
 
Hi Fred
That’s exactly what I am trying to do but I cannot generate an 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.
 
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.
 
Hi
My problem is I donot know how to generate a number without using autonum
which is unsuitable .
 
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 .
 
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
 
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
 
Back
Top