Number of records in a table

[ria.arora]

Hi All,

I’m having problem returning number of records available in the Table using VBA.

Below is the code returning number of records available in the table. If I run the same code in debug mode is shows correct number of records but when it prints on screen it's showing 0 record.

Call function:

[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                    gNoOfRecords = NumberOfRecords(gsPnPDatabaseID, strDestTbl)
                    lstProcessed.AddItem ("Total " & gNoOfRecords & " records imported")
[/FONT][/COLOR][COLOR=black][FONT=Verdana]

[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Public Function NumberOfRecords(sDatabaseID As String, sTableName As String) As Long
    On Error GoTo ErrHandler
    
    Dim rsTmp As Recordset
'    Dim NumberOfRecords As Integer
    NumberOfRecords = 0
    Dim strSelectSQL As String
    
'    Dim db As DAO.Database
'    Set db = CurrentDb()
    
    strSelectSQL = "SELECT count(*) AS Total FROM " & sTableName
    If sDatabaseID = "PnP" Then
        If gsPnPDatabaseOpened = False Then
            ConnectPnPDatabase
        End If
        Set rsTmp = gsPnPDatabase.OpenRecordset(strSelectSQL)
    ElseIf sDatabaseID = "NNA" Then
        If gsNNADatabaseOpened = False Then
            ConnectNNADatabase
        End If
        Set rsTmp = gsNNADatabase.OpenRecordset(strSelectSQL)
    ElseIf sDatabaseID = "TBD" Then
        If gsTBDDatabaseOpened = False Then
            ConnectTBDDatabase
        End If
        Set rsTmp = gsTBDDatabase.OpenRecordset(strSelectSQL)
    End If
    If Not rsTmp.EOF And rsTmp.BOF = False Then
        NumberOfRecords = rsTmp!Total
    End If
ErrHandler:
    On Error GoTo 0
    rsTmp.Close
    Set rsTmp = Nothing
End Function[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]

Quite weird … Below is the VBA code I’m using in MS Access 2003. OS – Windows XP.

Any idea what are these problems and how to resolved this?

 

[John Big Booty]

You could use the DCount() function.

It would look something like this;

=DCount("AFieldInTheRecordSource","YourTableName")