Create running sequence number

[arnelgp]

here is a Sequence number generator.
the problem with this one is that
you cannot use it in Standard Query.
you must use code (VBA) to Insert the
Sequence to your table.
also, you must add another field to
your sequence table (eg.OldSequence).
the purpose is to save the old barcode
to this field, so when you are inserting
new sequence, you must first check the OldSequence
if it is in the table, therefore the sequence
have already been generated, and you
can ignore this record.

the syntax of the function is:
fnSequence(0) = to get the generated Sequence (whole)
fnSequence(1) = to get the generated Prefix
fnSequence(2) = to get the generated Seq
fnSequence(3) = to get the generated Suffix

Public Function fnSequence(Optional ByVal seqPortion As Integer = 0) As String
    Dim var As Variant
    Dim arr As Variant
    Const TABLE_NAME As String = "table1"
    fnSequence = "AA-000001-0001"
    var = DMax("Prefix & '-' & Seq & '-' & Suffix", TABLE_NAME)
    If IsNull(var) Then
        arr = Split(fnSequence, "-")
    Else
        'split the var to array
        arr = Split(var, "-")
        'convert to numbers
        arr(2) = Val(arr(2))
        arr(1) = Val(arr(1))
        'add 1 to the sequence
        arr(2) = arr(2) + 1
        If arr(2) > 9999 Then
            arr(2) = 1
            arr(1) = arr(1) + 1
        End If
        If arr(1) > 999999 Then
            arr(1) = 0
            If arr(0) <> "ZZ" Then
                If Right(arr(0), 1) <> "Z" Then
                    arr(0) = Left(arr(0), 1) + Chr(Asc(Right(arr(0), 1)) + 1)
                Else
                    arr(0) = Chr(Asc(Left(arr(0), 1)) + 1) & "A"
                End If
            End If
        End If
        arr(1) = Format(arr(1), "000000")
        arr(2) = Format(arr(2), "0000")
    End If
    fnSequence = Switch(seqPortion = 1, arr(0), seqPortion = 2, arr(1), seqPortion = 3, arr(2), True, Join(arr, "-"))
End Function

example:

supposed that your Sequence Number table is called Table1.
Table1 now has the new field OldSequence.
your imported raw data is Table2.
and supposed the barcode column is Barcode:

Private Sub test()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Table2") 'table2 is the imported/raw data
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            ' BarCode is the field in you raw data
	    ' check if we already added similar Barcode before
            If DCount("*", "Table1", "OldSequence = '" & !BarCode & "'") = 0 Then
                CurrentDb.Execute "Insert Into Table1 (Prefix,Seq,Suffix,OldSequence) select " & _
                    "fnsequence(1) as ex1,fnsequence(2) as ex2,fnsequence(3) as ex3, '" & !BarCode & "';"
            End If
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
End Sub

 

[lookforsmt]

HI! Thank you each and everyone for your help.

Dear arnelgp,

i have created two tables, table1 & table2
table1 has two fields: OldSequence & NewSequence
table2 has three fields: Prefix; Seq & Suffix

and have savee the function fnSequence in the module.

i have another table lets say table3 which receives the data (approx 500 to 600) rows. I need to understand how will table1 & table2 know that it has to create 500 to 600 barcodes.

Where do i put the second code, is it on the form.
Pls let me know i am trying to understand the method.
I once again want to thank you gurus.

 

[arnelgp]

here is a sample and
hopefully explains what
you need to do.

run Form1.

 

[lookforsmt]

Thanks arnelgp for the code. It will take me sometime to understand the complex but very useful code but i appreciate your help.

Just wanted to know in table3 i will have to create barcodes 00-000001-0501 and so on till the required barcodes daily. If this is correct how do i create the barcodes in table3.

Do I need to import through excel in table3 and then use it.

Also, wanted to known if the barcodes goes above 00-000001-9999 will the next new sequence change to AA-000002-00001

is there a code to check in the immediate window for the next code.

Sorry for being so dumb.

 

[arnelgp]

1. what do you mean?
do you need to put the new
sequence (barcode) to table3 also.
if so add new field to table3 (eg. NewBarCode, text).

after generating it on the form,
create an update query to insert
the NewSequence to the NewBarCode field:

UPDATE Table3 INNER JOIN Table1 ON Table3.BarCode = Table1.OldSequence SET Table3.NewBarCode = [Table1].[NewSequence];


2. yes, it will generate until:

ZZ-999999-9999

 

[lookforsmt]

HI! arnelgp

Sorry if i am not clear.

1. what do you mean?
do you need to put the new
sequence (barcode) to table3 also.
if so add new field to table3 (eg. NewBarCode, text).

No, i dont want to add the new sequence to table3.
I just wanted to know if my DB is receiving data into one of the table on daily basis approx 500 to 600 rows. How do i generate barcodes for these rows.

I dont want to generate the entire barcodes in advance, i want to generate barcodes just for the data received.

2. yes, it will generate until:
ZZ-999999-9999

This is what i wanted, thanks for this.

Just if you can explain me on point 1. pls

 

[lookforsmt]

Thanks arnelgp for your help and the sequence number generator. For the time being i will manually create the codes in Table3 by using excel and then click the generate btn for the barcodes.

Thank you all for your support and help. I will mark this as solved for now.