fCounter (campo contador)

Pregunta

Sé cómo generar un número correlativo en un campo numérico. Pero me gustaría saber cómo puedo generar un número correlativo en un campo numérico y que además tenga en cuenta los huecos dejados por anteriores eliminaciones de registros. Es decir, suponiendo que los valores de ese campo numéricos fueran, por ejemplo: 1, 2, 3, 5, 6, 8, 9, 11, querría un código que me diera como resultado el 4. Una vez añadido el 4, la siguiente vez debería devolverme el 7, la siguiente el 10 y la siguiente el 12.

¿Cómo puedo hacerlo?

Respuesta

Una manera de hacerlo sería abrir un recordset de esa tabla e ir recorriendo los registros comparando los valores de ese campo con los del registro anterior, hasta encontrar el primer hueco en donde el valor no fuera correlativo. Pero este método tal vez empiece a consumir demasiado tiempo, una vez la tabla tenga unos cuantos miles de registros.

Para ganar rapidez, siempre será mejor si lo podemos implementar con una instrucción SQL. Y eso es lo que hace la función fCounter, la cual consta de los siguientes

Argumentos

Ejemplo de uso

SiguienteNumero = fCounter("campo_numerico", "Tabla")

Código

'---------------------------------------------------------
'
' fCounter
'
' Código escrito originalmente por Màrius Puig y Juan M.
' Afán de Ribera.
' Estás autorizado a utilizarlo dentro de una aplicación
' siempre que esta nota de autor permanezca inalterada.
' En el caso de querer publicarlo en una página Web,
' por favor, contactar con los autores en
'
'     accessvbafaq@ya.com
'
' Este código se brinda por cortesía de
' Màrius Puis
' Juan M. Afán de Ribera
'
Function fCounter( _
                  FieldName As String, _
                  TableName As String, _
                  Optional DbPath As String) As Long
                
Dim SQL As String
Dim db As Object 'DAO.Database
Dim rst As Object 'DAO.Recordset

   On Error GoTo fCounter_Error

    SQL = "SELECT " & FieldName _
        & " FROM " & TableName _
        & " WHERE " & FieldName & " = 1"
      
    If DbPath = "" Then
        Set db = CurrentDb
    Else
        Set db = DBEngine.OpenDatabase(DbPath)
    End If
    
    Set rst = db.OpenRecordset(SQL, DB_OPEN_SNAPSHOT)
    
    If rst.EOF Or rst.BOF Then
        fCounter = 1
    Else
        SQL = "SELECT " & TableName & "." & FieldName & " + 1" _
            & " FROM " & TableName _
            & " LEFT JOIN " & TableName & " AS TMP" _
            & " ON " & TableName & "." & FieldName & " + 1" _
            & " = TMP." & FieldName _
            & " WHERE TMP." & FieldName & " IS NULL" _
            & " ORDER BY " & TableName & "." & FieldName
    
        Set rst = db.OpenRecordset(SQL)
        fCounter = rst(0)
    End If
    
exit_Function:

    If Not rst Is Nothing Then rst.Close
    Set rst = Nothing
    If Not db Is Nothing Then db.Close
    Set db = Nothing

   On Error GoTo 0
   Exit Function

fCounter_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    Resume exit_Function

End Function
'---------------------------------------------------------

Créditos

con el fin de dar a conocer el trabajo de uno de los más grandes en el mundo de Access en español, y tras solicitar su permiso, replico este artículo de Juan M Afán de Ribera y Màrius Puig. Todos los derechos y autoría corresponden a él. Access & VBA FAQ – fCounter (campo contador) (archive.org)