Hello everyone,

I am current upgrading some of the functions on an inventory system.
Current system accepts manual data input from the user and stores them in a SQL database.
One of the inputs is control number which is a primary key in one of our table so it needs to be unique. Since it is manually entered into the system, we have to keep track of all of control numbers so there is no redundant control number which is difficult since we have hundreds of items with unique control number that are entered into the system everyday.
Yes, it is waste of time.

So I want our newly upgraded system to have a function where the control number is automatically generated so the control numbers are controled by the system. I know there something similar in MS Access 2007 but I cant seem to find it on Visual Studio.

Format (2 Characters followed by 8 numeric numbers)
Example: AA12345678, AA12345679, AA12345680

I would appreciate if anyone can tell me how to go about it, better if i can see some codes.
Thanks guys

5 Years
Discussion Span
Last Post by bluehangook629

ok.. let's assume that you have a table "tblInvNo" which stores your invoice numbers.. (just for demo)

there is only one column in this table , and that is invno (of type nvarchar)

Let's say the content of Table "tblInvNo" are


As you can see the maximum invoice number here is "BB12345680" .

you can get the max invoice number by following sql Query:

SELECT MAX(invno) FROM dbo.tblInvNo

After you get this max invoice number use the following function to calculate next invoice number:

Public Function IncrementInvoice(ByVal strInvoiceNumber As String) As String

        If strInvoiceNumber.Length <> 10 Then
            Return "Error"
        End If

        Dim strAlphaPart(1) As Char
        strAlphaPart(0) = strInvoiceNumber(0)
        strAlphaPart(1) = strInvoiceNumber(1)

        Dim IntPart As Int64
        IntPart = strInvoiceNumber.Substring(2, 8)

        If IntPart = 99999999 Then
            If strAlphaPart(1) = "Z" Then
                strAlphaPart(0) = Chr(Asc(strAlphaPart(0)) + 1)
                strAlphaPart(1) = "A"

                IntPart = 1

                Return strAlphaPart(0) & strAlphaPart(1) & IntPart.ToString.PadLeft(8, "0")
                strAlphaPart(1) = Chr(Asc(strAlphaPart(1)) + 1)
            End If

            IntPart += 1
            Return strAlphaPart(0) & strAlphaPart(1) & IntPart.ToString.PadLeft(8, "0")
        End If

    End Function
'outputs example:
        strTemp = IncrementInvoice("AA99999998") 'Output will be: "AA99999999"
        strTemp = IncrementInvoice("AA00000005") 'Output will be: "AA00000006"
        strTemp = IncrementInvoice("AZ00000007") 'Output will be: "AZ00000008"
        strTemp = IncrementInvoice("AZ99999999") 'Output will be: "BA00000001"

i hope this helps

Attachments invoice.PNG 9.28 KB

Great! I'll try it when I get in the office later this afternoon.
I see that you've attached a thumbnail but I am unable to open it.
Is it possible if you can email me a copy of it.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.