Generating a Unique "Identification Code" for each record

[charly.csh]

Hi everyone,


Today I write with this question.

I have created a form to keep some info for my customer complains and I am creating a "unique code" for each complaint. I have been coded that based on the product, date and "autonumber" (...bad thing)

Just I want instead of using the autonumber ID a new "consecutive number". I mean... read the "product code + date" and if this doesn't exist creat ID equals to "001", otherwise "002" or "3"

Example....

NC-PR1-20201404-001
NC-PR1-20201404-002

Or
NC-PR2-20201404-001


This is my current code, what do you suggest to change?

'************************************************* ***********************
Sub Product_AfterUpdate()

Dim Clave As String
Dim Zero As String
Dim Year1 As String
Dim Mes As String
Dim Mes1 As String
Dim Dia As String
Dim Dia1 As String
Dim Identificacion As String
Dim BU As String

'Values assigment:

Zero = "0"
Year1 = Year(Date) - 2000
Mes = Month(Date)
Dia = Day(Date)
BU = DLookup("[Codigo]", "[Linea_Produccion]", "[LineaProduccion]= '" & Me.Linea_Produccion & "'")

Identificacion = Me.ID 'This is the line should be changed to create a new consecutive based on reading "Product Code" + "Year", maybe a recordSet?

'Conditions for the code

If Mes < 10 Then
Mes1 = Zero + Mes

Else

Mes1 = Mes

End If



If Dia < 10 Then
Dia1 = Zero + Dia

Else

Dia1 = Dia

End If

If Identificacion < 10 Then

'NC = No conformidad

Clave = "NC-" + BU + "-" + Year1 + Mes1 + Dia1 + "-" + Zero + Zero + Identificacion

ElseIf Identificacion >= 10 And Identificacion <= 100 Then

Clave = "NC-" + BU + "-" + Year1 + Mes1 + Dia1 + "-" + Zero + Identificacion

Else

Clave = "NC-" + BU + "-" + Year1 + Mes1 + Dia1 + "-" + Identificacion

'El cuadro de texto de Codigo(ID) toma el valor de la variable clave'

End If

Me.Code = Clave

End Sub

[orange]

I'd vote against that approach. Suggest an autonumber PK and keep with the 1 fact 1 field in a relational table.
You can index on other fields as needed. You can use cascading combos and/or Find as you type contructs to help search/find records.
I have seen many "concocted identifiers" run into trouble when some underlying business facts change.

Now, if you proceed as you are going, you would not be the first to do so. Others on this and other forums often offer suggestions to assist your approach. Good luck with your project.