Campo alfanumérico consecutivo personalizado para una BBDD en Access
Necesito crear un campo alfanumérico, el cual tenga esta configuración, XXX-0000-00, donde las tres equis sean el prefijo de país, los cuatro ceros el consecutivo y los dos últimos ceros el año en curso, y que cuando termine el año se reinicialice tanto el consecutivo como el año.
No tengo mucha experiencia en código VBA.
2 respuestas
Si disponemos de una tabla (DATOS) que tiene un campo (CLAVE) con las condiciones indicadas, para obtenerlo solo es indispensable el código del país (CodPais) y esta fórmula:
= CodPais & Format(Nz(DMax("Val(Mid(CLAVE, 5, 4))", "DATOS", "LEFT(Clave,3)= '" & CodPais & "' AND RIGHT(Clave, 2)='" & Format(Date, "yy") & "'"), 0) + 1, "-0000-") & Format(Date, "yy")
Hay quienes utilizan la función de dominio DCount en lugar de DMax, si se borrase un registro (o más) de la tabla, se duplicarían datos.
(0001, 0002, 0003, 0004) = 4 registros, el siguiente 0005 con Dmax y DCount
Se 'pierde' uno
(0001, 0002, 0004) = 3 registros, el siguiente el 0005 con Dmax y 0004 con DCount
Se 'pierden' dos
(0002, 0004) = 2 registros, el siguiente el 0005 con Dmax y 0003 con DCount
Gracias Enrique Feijóo pero el prefijo del país habría que seleccionarlo a parte cuando se supiera nacionalidad
Se parte del supuesto de que el código de país ya se tiene a disposición (en una tabla, en un cuadro de lista, en un combo) y según la funcionalidad puede ser el código internacional o el telefónico o ... y un simple CLICK seleccionándolo podrá generar 'el siguiente' para generar (acompañado de mas datos) el nuevo registro.
Un enlace para informarse (por eso de que 'enseñar no cuesta nada'):Normas de codificación
Solo habrá que sustituir 'CodPais ' por la referencia al objeto que tiene la lista de códigos.
La única línea que se utiliza para obtener el siguiente obtiene el mayor filtrando al conjunto por código de país y año, si no lo localiza (el primero de la serie o el cambio de año) reinicia la serie sin modificar sus virtudes.
¡Gracias!
Lo mismo en forma de función:
Public Function Correlativo(CodPais As String) As String
Correlativo = CodPais & Format(Nz(DMax("val(mid(CLAVE,5,4))", "DATOS", "LEFT(Clave,3)= '" & CodPais & "' AND RIGHT(Clave,2)='" & Format(Date, "yy") & "'"), 0) + 1, "-0000-") & Format(Date, "yy")
End FunctionSu aplicación practica (en el ejemplo la referencia es el codigo de España) sustituible por la referencia al combo cuadro de lista ... etc.
En la ventana de inmediato:
¿? Correlativo("ESP")
ESP-0001-22
¡Gracias!
Enrique Feijóo me coge el ID y no el prefijo del país el resto lo hace perfectamente
Los cuadros de lista (al igual que los Combo Box) devuelven por defecto el valor de su columna dependiente (que suele ser el ID porque lo define como único y normalmente la primera), pero puede ser cualquier columna.
Para este caso (un listado que se supone sus elementos no tiene repeticiones) el ID y el elemento (el código de país) son únicos por lo que el ID no tiene valor practico y se puede prescindir de el o la alternativa de establecer como columna dependiente el 'código de país'.
Opciones:
.- En la vista diseño del formulario y en las propiedades del objeto, pestaña DATOS indicar numéricamente que columna es la predeterminada (si tiene tres columnas, ID, COD_PAIS, NOMBRE_PAIS) indicar que se desea el COD_PAIS (la columna 2) independiente de que sea o no visible
.- En el origen de datos del cuadro de lista eliminar el ID (quedaran dos columnas, COD_PAIS, NOMBRE_PAIS), será la primera la que contiene el dato que nos interesa.
(Y el cuadro de lista será mas ligero al no tener datos sin valor practico para esta funcionalidad)
Private Sub CliNumCliente_GotFocus()
CliNumCliente = CliPrefijoPais & Format(Nz(DMax("Val(Mid(CliNumCliente, 5, 4))", "tbl_Clientes", "LEFT(CliNumCliente,3)= '" & CliPrefijoPais & "' AND RIGHT(CliNumCliente, 2)='" & Format(Date, "yy") & "'"), 0) + 1, "-0000-") & Format(Date, "yy")
End Sub
Gracias, como has visto funciona
Solo a titulo de curiosidad:
¿Qué opción has aplicado?, la de eliminar lo innecesario, la de cambiar la columna dependiente, otras.
Eso no lo pillo, si puedes explicármelo te lo agradezco. Yo he puesto el código que me pusiste cambiando los datos de la tabla y campos, que es el código que te he puesto hay
Insisto en que solo es curiosidad.
En un mensaje anterior tenias el problema de que utilizaba un dato que no era el correcto (ID <> COD_PAIS)
Se ofrecieron alternativas y la curiosidad es saber cual de ellas (o si fue otra) la que dio con la solución al problema.
Y ahora con imágenes aunque a muchos no les gusta.
Asumo que tiene una tabla con los códigos de países.
Y tengo una tabla de consecutivos
Y tengo el formulario
Como Colombia ya tiene 2 consecutivos me asigna el 0003. Ahora tomo otro país.
Como Costa Rica no tiene registros me asigna 0001. Ahora tomo Colombia y cambio de año.
Efectivamente la función me asigna 0001 y el año 2023.
Código de la función.
Public Function siguiente(intPais As Integer, mfecha As Date) As String
Dim pais As String
Dim periodo As String
Dim strSQL As String
Dim rs As DAO.Recordset
pais = Format(Nz(DLookup("codigopais", "tblpaises", "idpais=" & intPais)), "000")
periodo = Right(mfecha, 2)
strSQL = "SELECT Max(Mid([consecutivo],5,4)) AS numera" & vbCrLf
strSQL = strSQL & " FROM tblconsecutivo WHERE ((Mid([consecutivo],1,3)='" & pais & "'" & vbCrLf
strSQL = strSQL & " AND Right([consecutivo],2)=" & periodo & "));"
Set rs = CurrentDb.OpenRecordset(strSQL)
If Nz(rs!numera) = 0 Then
siguiente = pais & "-" & "0001" & "-" & periodo
Else
siguiente = pais & "-" & Format(rs!numera + 1, "0000") & "-" & periodo
End If
rs.Close
Set rs = Nothing
End FunctionObserve que a la función le paso como parámetros el ID del país y la fecha.
Creo que las imágenes son necesarias y complementarias
Gracias Eduardo Pérez Fernández pero el consecutivo de la tblconsecutivo como lo creas si ya está el id.
y como llamo luego a la función en el cuadro de texto
Digamos que el campo id de la tabla tblconsecutivo sobra. Disculpe se me olvido el código del evento Después de actualizar del cuadro combinado y al recibir el enfoque.
Private Sub cboPais_AfterUpdate() Me.ctlConsecutivo = siguiente(Me.cboPais, Me.ctlFecha) End Sub Private Sub cboPais_GotFocus() If Not IsDate(Me.ctlFecha) Then MsgBox "Indique la fecha!", vbInformation, "Cuidado" Me.ctlFecha.SetFocus End If End Sub
Le aclaro porque no utilizo la función Dmax(). Por que utilizo como backend PostgreSQL y en este servidor y en todos no existe esta función, es propia de bases de datos Jet. Me recomendación trate en lo posible utilizar SQL.
¡Gracias!
Eduardo Pérez Fernández pero entonces el consecutivo de la tblconsecutivo seria con formato y si es así como sería ese formato
No necesita formato solo fije el campo como texto corto y tamaño 11 caracteres, requerido Sí, indexado Si sin duplicados
Eduardo Pérez Fernández te envié un correo
No llegó revise mi correo es [email protected]
yo Tengo esté mailto:[email protected]
[email protected] es el qué yo tengo
Es correcto puede enviarme su base de datos a este correo.
ya te lo envíe Eduardo
Como no me llegó su correo le explico gráficamente cómo se toma el contenido del campo cboPais del cuadro combinado.
Origen de datos del cuadro combinado
Observe que el SELECT consta de 3 columnas, idpais, pais, codigopais pero la "Columna dependiente" es la 1 (idpais), es decir sería por defecto el valor que toma el cuadro combinado, pero como quiero es el contenido del codigopais y NO es la columna dependiente para tomar este valor debo utilizar Column(indice), en donde indice es 0 para la primera columna 1 para la segunda y 2 para la tercera. Ver figura
Ahora, cuando selecciono el cuadro combinado Pais y quiero utilizar el contenido de la columna codigopais para pasárselo a la función debo hacer referencia como muestra la imagen.
En estas condiciones cambia el código que le había suministrado.
Código del evento "Después de Actualizar"
Private Sub cboPais_AfterUpdate() Me.ctlConsecutivo = siguiente(Me.cboPais.Column(2), Me.ctlFecha) End Sub
Código de la función
Public Function siguiente(StrPais As String, mfecha As Date) As String Dim pais As String Dim periodo As String Dim strSQL As String Dim rs As DAO.Recordset pais = Format(StrPais, "000") periodo = Right(mfecha, 2) strSQL = "SELECT Max(Mid([consecutivo],5,4)) AS numera" & vbCrLf strSQL = strSQL & " FROM tblconsecutivo WHERE ((Mid([consecutivo],1,3)='" & pais & "'" & vbCrLf strSQL = strSQL & " AND Right([consecutivo],2)=" & periodo & "));" Set rs = CurrentDb.OpenRecordset(strSQL) If Nz(rs!numera) = 0 Then siguiente = pais & "-" & "0001" & "-" & periodo Else siguiente = pais & "-" & Format(rs!numera + 1, "0000") & "-" & periodo End If rs.Close Set rs = Nothing End Function
Observe la variable pais que ahora cambio a
pais = Format(StrPais, "000")
Porque le he pasado es una cadena de texto, es decir, la columna 3 de la tabla tblpaises. No obstante, había utilizado el idpais debido a que este valor puede servir para otras consultas etc.