Auto generate invoice number on report

[Adriano85]

Hi guys,

I have created invoices as reports but now I'm looking for a way to add an invoice number to it. It needs to be an auto increment number for every invoice for every client.

I have 10 different clients and I want them to have their own incremented invoice number. Every client has it own unique number. So for example in week 1 I want to have the following invoice numbers; 01-0001, 02-0001, 03-0001 etc. In week 2 I want them to be; 01-0002, 02-0002, 03-0002. It is important that every client has the invoice numbers without gaps.

What is the best way to realize this?

Many thanks for your time and help!

Adrian

 

[nanscombe]

By adapting a post I made in Question Auto generated number you could try ...

Private Sub Form_BeforeUpdate (Cancel as Integer)
Dim strPrefix as String
  strPrefix = Format(Val(Me.[yourClientId] & vbNullString), "00") & "-"
  if Len(Me.[yourInvoiceId] & vbNullString) = 0 then Me.[yourInvoiceId] = getNextId (strPrefix)
End Sub

Public Function getNextId(byval gniPrefix as String) AS Variant

  getNextId = NZ(DMAX("yourID", "yourTable", "yourID Like '" & gniPrefix & "*'"), gniPrefix & "0")
' gniPrefix & "0" gives you a default value if one is not found in the table

  getNextId = Val(Mid(getNextId, len(gniPrefix) +1) +1 ' get next numerical value

  getNextId = gniPrefix & Format(getNextId, "0000") ' create next string Id

End Function

 

[nanscombe]

After a bit of playing around I've changed the code to make it more flexible and included a demo of how it could be used with the Form_BeforeUpdate() function.

I've renamed it to nextIdString().

Public Function nextIdString(ByVal nisFieldName As String, ByVal nisTableName As String, ByVal nisPrefix As String) As Variant

    nextIdString = Nz(DMax(nisFieldName, nisTableName, "[" & nisFieldName & "] Like '" & nisPrefix & "*'"), nisPrefix & "0")
    ' nisPrefix & "0" gives you a default value if one is not found in the table

    nextIdString = Val(Mid(nextIdString, Len(nisPrefix) + 1)) + 1
    ' Get next numerical value by looking at the highest value number after the prefix and adding 1

    nextIdString = nisPrefix & Format(nextIdString, "0000") ' create next string Id
    ' Create new ID string by concatenating the formated number to the prefix
End Function

It now takes 3 parameters.
1) nisFieldName - This is the field in the table where it is to use for it's value.
2) nisTableName - This is the field in the table where it is to use.
3) nisPrefix - This is the prefix that will be placed at the start of the ID string.

In this example it is handed the current years as two digits (13) followed by a "-" as the prefix.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strPrefix As String
  strPrefix = Format(Now(), "yy") & "-"
  If Len(Me.ID & vbNullString) = 0 Then Me.ID = nextIdString("ID", "tblMyData", strPrefix)
End Sub

Feel free to have a poke around.