Cinco formas de generar un valor único para usarlo como campo clave en VBA.

Recientemente tuve una conversación con Arkaitz del blog programadordepalo acerca del uso de los campos auto numéricos, de los cuales soy un gran defensor como se puede vez en el artículo La importancia de los auto numéricos en Access, y dicha conversación me animó a escribir este artículo.

Si bien es cierto que yo los uso en casi todas las tablas donde necesito un campo clave y único y no tengo un valor real para ello, hay ocasiones en que no es posible utilizarlos, es más, recientemente, realizando unas pruebas con una base de datos SQL Azure, de Microsoft, vinculada a nuestro querido Access, he tenido serios problemas con el funcionamiento de un auto numérico, lo que nunca me ha pasado con Microsoft Access. Además, tras contactar con el servicio técnico de Azure me indicaron el siguiente enlace donde expresan tácitamente que no aseguran que un auto numérico respete la numeración correlativa.

Bien, ante esta situación y dado que habrá ocasiones en las que necesitemos un campo clave y no nos valga un DNI, una Matricula, o cualquier valor único real y tengamos que inventárnoslo, te propongo algunos métodos muy comunes de conseguir un valor único para ser usado como campo clave. Ten en cuenta que es una pequeña representación, pues hay tantas como imaginación tenga el programador y si surfeas por la red encontrarás muchas más.

1 – Un auto numérico secuencial

Este sería el método más sencillo de emular el propio auto numérico de Access. Si necesitamos que cada registro tenga un número secuencial simplemente podemos leer el último número guardado, con la función de agregado Dmax y sumarle uno.

Public Function NumId() As Long
    Dim Numero As Long
    Numero = DMax("Campo","Tabla")+1
    NumId = Numero
End Function

Pero esta función, tal cual, tiene el problema de que si aún no hay registros, Dmax, devolverá un error, para ello podemos hacer uso de la función Nz que puede sustituir el Nulo por otro valor. Por ejemplo así:

Public Function NumId() As Long
    Dim Numero As Long
    Numero = Nz(DMax("Campo","Tabla"),0) + 1
    NumId = Numero
End Function

De esta forma, si no hubiera aún ningún registro la función devolvería el valor 1, el cero que devuelve Nz + el 1 que sumamos al final.

Se podría utilizar esta fórmula como parte del evento Current del formulario, analizando si es un nuevo registro y aplicando el valor a nuestro campo clave, más o menos así:

Private Sub Form_Current()
   If Me.NewRecord Then
      Me.CampoClave = Nz(DMax("Campo","Tabla"),0) + 1
   End If
End Sub

2 – A través de un número aleatorio

Podemos hacer uso de la función Rnd para conseguir un número aleatorio entre los valores que le especifiquemos, algo así:

Public Function NumId() As Long
    Dim Numero As Long
    Randomize
    Numero = ((9999 - 1000 + 1) * Rnd + 1000)
    NumId = Numero
End Function

El valor devuelto por esta función es un número comprendido entre el valor máximo, en este caso 9999 y el mínimo de 1000. Sobra decir que ambos valores se pueden cambiar sin problemas.

Y, como no te puedo asegurar que el número aleatorio no se repita de una vez a otra, aunque es sumamente difícil que esto ocurra pero no imposible, si quieres perfeccionar el método, podrías comprobar, antes de asignar el valor a un campo, si ya existe mediante DlookUp, por ejemplo.

3 – Utilizando la fecha y la hora del sistema

Este podría ser otro método para tener una especie de auto numérico combinando los dígitos del año, el mes, el día, la hora, los minutos y los segundos del sistema. Con este método sería complicado obtener dos números iguales, ya sería mucha casualidad que dos personas crearan un registro en el mismo segundo. Lo interesante de este método es que la numeración sigue una secuencia e incluso nos vale como referencia para saber en que momento se ha creado el registro. La función sería mas o menos así:

Public Function NumId() As Double
    Dim Numero As Double
    Numero = CDbl(Format(Now(), "ddmmyyhhmmss"))
    NumId = Numero
End Function

Esta función tiene el inconveniente de que el número que genera es demasiado grande y debemos usar un tipo de datos Double que ocupa el doble de bytes(8) en el sistema que el Entero Largo(4) del ejemplo anterior.

Esta función devolvería un número como este: 160414221136

4 – Secuencial con reinicio anual

Otra necesidad que en muchas ocasiones nos hemos encontrado consiste en tener que contabilizar las facturas y llevar una numeración que precisa, por un lado, añadir al principio los dígitos del año, y por otro, reiniciar la numeración a cada año nuevo. Por ejemplo podríamos estar haciendo facturas del 2013 así:

20130028, 20130029, 20130030, 20130031…….etc

y al llegar al 2014 tener que reiniciar la numeración:

20140001, 20140002, 20140003…..etc.

En este caso el ejemplo se puede complicar un poco pero Emilio Feijó, en los foros de Access, ya se tomó la molestia de desglosar todo el procedimiento para que sea fácilmente entendible. Y así mismo te lo explico:

Suponiendo que tenemos una tabla de «Facturas» con el campo «NumFra» de tipo numérico. En el formulario, en el evento «Al activar registro» y, una vez comprobado que es un registro nuevo podríamos aplicar esto…

Me.NumFra = Year(Date)

Con esto obtendríamos los 4 dígitos del año pero nos falta el autonumérico que serían los 4 dígitos siguientes, 4 porque hemos decidido que sean solo 4, hasta el 9999, si necesitas mas solo habrá que variar la fórmula ligeramente.

Para obtener los 4 dígitos siguientes a partir del año lo obtendríamos mediante:

Mid(NumFra, 5)

Pero Mid devuelve un texto que hay que convertir a numérico si queremos contabilizarlos:

Val (Mid(NumFra, 5))

Pero ahora necesitamos el último número de la tabla «Facturas»

Dmax (Val (Mid(NumFra, 5)), "Facturas")

¡Pero ojo!, necesitamos el valor máximo del año en el que estamos, así que debemos comparar con los 4 primeros dígitos de nuestro número. Primero extraeríamos el año así:

Left(NumFra,4)

Pero nuevamente, la función Left devuelve un texto así que debemos convertirlo a numérico:

Val(Left(NumFra,4))

Una vez obtenido el año del número de factura debemos de compararlo con el año actual:

Val(Left(NumFra,4)) = Year(date)

Esto devolverá, verdadero o Falso, en función de si el año de nuestro número de factura coincide con el año actual y esto podemos aprovecharlo combinándolo con la función de Dmax que escribimos unas líneas más arriba:

DMax("Val( Mid(NumFra, 5))", "Facturas", "Val( Left(NumFra,4)) = " & Year (date))

Lo que estamos haciendo es poner como condición, tercer parámetro que admite Dmax, que busque el máximo número del año actual pero ¿Que pasa si no encuentra el máximo número del año actual?, esto pasará cuando empecemos el 2015 y Dmax no será capaz de encontrar un número de factura con el 2015, así que devolverá un Null, o sea, error. Para evitar el error podemos hacer uso nuevamente de Nz que, en caso de devolver un nulo, pondrá el valor que nosotros le especifiquemos, en esta ocasión un cero:

Nz(DMax("Val( Mid(NumFra, 5))", "Facturas", "Val( Left(NumFra,4)) = " & Year (date)), 0)

Llegados aquí, ya tendríamos una cifra, la última del año o un cero si es la primera que estamos generando este año, a esa cifra deberíamos de sumarle 1, puesto que queremos obtener el siguiente número de factura:

Nz(DMax("Val( Mid(NumFra, 5))", "Facturas", "Val( Left(NumFra,4)) = " & Year (date)), 0) + 1

Y como queremos representar los números con 4 cifras, o sea, Factura número 0004 y no 4, podemos formatear el número de la siguiente forma:

Format(Nz(DMax("Val( Mid(NumFra, 5))", "Facturas", "Val( Left(NumFra,4)) = " & Year (date)), 0) + 1, "0000")

Con esto ya hemos conseguido un número de factura, bien sea uno consecutivo o el 0001 porque sea nuestra primera factura del año, lo único que nos queda es unir esta cifra al número del año que calculamos al principio:

Me.NumFra = Year(Date) & Format(Nz(DMax("Val( Mid(NumFra, 5))", "Facturas", "Val( Left(NumFra,4)) = " & Year (date)), 0) + 1, "0000")

Después de haberlo desglosado todo podríamos aplicar toda la fórmula, tal y como dijimos, en el evento «Al activar registro» y quedaría así:

Private Sub FORM_Current ()
If Not Me.NewRecord Then Exit Sub
Me.NumFra = Year(Date) & Format(Nz(DMax("Val( Mid(NumFra, 5))", "Facturas", "Val( Left(NumFra,4)) = " & Year (date)), 0) + 1, "0000")
End Sub

Y con esto ya tendríamos nuestra forma de crear un auto numérico como campo clave, porque no se repetirá.

 5 – Alfanumérico aleatorio

Y por último, rizando el rizo, tenemos un alfanumérico aleatorio. Esta función utiliza el rango de caracteres ASCII que incluye las letras mayúsculas y los números de 0 al 9,  junto con Rnd, para generar un código del número de caracteres que le especifiques. Al igual que en la primera función donde usábamos Rnd deberías de implementar un código de seguridad que detectara si el número ya está asignado y volver a generarlo en consecuencia.

Function AlfaNumAleatorio() As String

    Const TotalCaracteres As Byte = 10
    Const MaxAscii As Byte = 90
    Const MinAscii As Byte = 48
    
    Dim strNumAleatorio As String
    Dim strResultado As String
    Dim intX As Integer


    Randomize

    For intX = 1 To TotalCaracteres
CalculaAleatorio:
        strNumAleatorio = Int((MaxAscii - MinAscii + 1) * Rnd + MinAscii)

        If strNumAleatorio >= 58 And strNumAleatorio <= 64 Then
            ' los valores ASCII del 58 al 64 los ignoramos
            GoTo CalculaAleatorio
        End If

        strResultado = strResultado & Chr(strNumAleatorio)
    Next intX

    AlfaNumAleatorio = strResultado

End Function

Puedes comprobar el rango que hemos usado y porqué hemos excluido los valores del 58 al 64 en la siguiente tabla.

Tabla de Caracteres ASCII

7 comentarios

  1. He estado estudiando la forma de poner a una factura el nº secuencial con reinicio en cada año. quiero que aparezca el año pero con este formato 0/2015. ¿como podria hacerlo?
    Gracias

  2. Ingresar registros con números correlativos entre 2 cifras.
    Es un programa que ingresa los datos de cierta cantidad de artículos de la misma marca, modelo pero los números son correlativos.
    Por ejemplo: 100 artículos marca aiwa modelo db34 del 23456 al 23556 ingresarlos a una tabla temporal y luego lo grabo tabla a tabla. Lo hace pero no para nunca, se desborda.
    Desde ya gracias BONSI

  3. Hola, estoy creando una base de datos en acces 2010, mi problema es que necesito generar un campo autonumerico personalizado, el cual quedaria asi «01-2750-15», la situacion es que al llegar el primer numero a 100 el segundo cambiaria de «2750» a «2751» y asisucesivamente, pero el ultimo digito «15» corresponde al año o sea que al llegar al siguiente año este me cambiaria a «16», el campo en la tabla se llama historia clinica, alguien me puede ayudar, graciasde antemano.

  4. HOLA ME INTERESA ESTE ARTICULO PERO ME GUSTARIA APLICARLO QUE SE REINICIARA LA NUMERACION POR DIA EL OBJETIVO SERIA QUE LO QUE SE CAPTURE EL DIA DE HOY EXISTA UN CAMPO QUE DARIA DESDE EL NUMERO UNO HASTA QUE TERMINE EL DIA. Y AL DIA SIGUIENTE COMENZARA DESDE UNO OTRA VEZ.

  5. Hola!! Una duda… y para que te hagas una idea de mi nivel de conocimientos, estudié psicología 🙂 He conseguido replicar el código para que en uno de los campos me calcule un «número de ficha» que es secuencial y (espero) con reinicio anual. Lo he hecho campo clave. Mi problema ahora es el siguiente: la base de datos la compartimos dos usuarias, introducimos datos en el formulario al mismo tiempo y cuando le damos a «nuevo registro» a las dos nos genera el mismo «número de ficha» y hasta que no le damos a «grabar» no nos da un error diciendo que no se pueden duplicar… ¿cómo se puede solucionar? Gracias!

  6. Hola, cómo estás? En relación a tu codigo, hice el siguiente y me genera un error de compilación – se espera fin de la instrucción con la coma o punto y coma luego del primer &amp. Si esa coma la cambio por :, me genera un error donde se espera =

    If Not Me.NewRecord Then Exit Sub
    Me.IdGESTION = Year(Date) &amp, Format(Nz(DMax(«Val(Mid(IdGESTION,6))» , «Registro Inicial» , «Val(Left(IdGESTION,4)) = » &amp, Year(date)),0)+1, «00000»)

    tienes solucion a eso?

    Gracias

  7. me podrian ayudar soy nuevo con las macros necesito saber como hacer que excel me genere un registro unico en la primera columna co

Responder a Jose Luis Cancelar respuesta

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.