arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 15:04
- Joined
- May 7, 2009
- Messages
- 19,241
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
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:
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
Code:
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 Functionsupposed 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:
Code:
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
