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?