Access VBA

Capture Record Counts

We wanted something to count the records to a table along the way so we could see what the effects of various edits did.

Public Function UpdateValTable(ValType As String, ProcTable As String)
    Dim sqlString       As String
    Dim DB              As Database
    Dim Builder         As Recordset
    Dim SetUpdt         As Recordset
    Set DB = CurrentDb
    On Error GoTo ErrHandleS
    sqlString = "SELECT COUNT(*) as RecordCount FROM " & ProcTable
	'Count records in Target Table
    Set Builder = DB.OpenRecordset(sqlString, dbOpenDynaset)
    Set SetUpdt = DB.OpenRecordset("tblValidation", dbOpenDynaset)
	'Get table to write counts
        SetUpdt.AddNew
            SetUpdt!Type = ValType
            SetUpdt!Quantity = Builder.Fields("RecordCount")
            SetUpdt!UserName = UserName()
        SetUpdt.Update
    Set Builder = Nothing		' Clean up
    Set SetUpdt = Nothing		' Clean up
    GoTo Exit_SomeName
    Exit Function
Exit_SomeName:                          ' Label to resume after error.
     Exit Function                          ' Exit before error handler.
ErrHandleS:                            ' Label to jump to on error.
    Select Case Err
    Case 9999                         ' Whatever number you anticipate.
        Resume Next                   ' Use this to just ignore the line.
        Resume Exit_SomeName          ' Use this to give up on the proc.
    Case Else                         ' Any unexpected error.
        Call LogError(Err, Error$, "UpdateValTable()")
        Resume Exit_SomeName
    End Select
End Function
 '+----------------------------------
 'Call your function from inside any mod using: 

Call UpdateValTable TheNameofType, Table2PullFrom

To access the function, I use Call UpdateValTable TheNameofType, Table2PullFrom within my code. I can use it anywhere to write a Validation record.