VBA: Table Record Count

=========
REF:

http://www.pcreview.co.uk/forums/vba-table-record-count-t1104006.html
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26940281.html
http://classicasp.aspfaq.com/general/why-does-recordcount-return-as-1.html

=========
NOTES
———
Be careful with the use of CurrentDb.TableDefs(“TABLENAME”).RecordCount option. It’s only available to the system when it is using a certain cursorType in your calls to the database. The default cursorType is Forward-Only and will not show the RecordCount.

The best option is to open a record set:
     Set rst = CurrentDb.OpenRecordset (“6050c__DrawingWinners”)
Then check if it has any records:�
     If rst.RecordCount = 0 Then
     ‘ the recordset is empty
     rowcnt = 0
    Else
Then quickly move through the records in the table:�
     rst.MoveLast
     rst.MoveFirst
     rowcnt = rst.RecordCount
    End If
Now you can use the rowcnt variable where you want to display the record count.

=========
EXAMPLES:
———
Set rst = CurrentDb.OpenRecordset (“6050c__DrawingWinners”)
If rst.RecordCount = 0 Then
 ‘ the recordset is empty
 rowcnt = 0
Else
 rst.MoveLast
 rst.MoveFirst
 rowcnt = rst.RecordCount
End If

StatusBox.Value = StatusBox.Value & vbCrLf & vbCrLf & “Complete: ” & rowcnt & ” winners drawn.”

———
CurrentDb.TableDefs(“1010d__Stats-TY”).RecordCount

———
MsgBox “Complete. 1010d=” & CurrentDb.TableDefs(“1010d__Stats-TY”).RecordCount & ” and 1020d=” & CurrentDb.TableDefs(“1020d__Stats-LY”).RecordCount, vbOKOnly, “”

———
StatusBox.Value = StatusBox.Value & CurrentDb.TableDefs(“1020d__Stats-LY”).Name & “: ” & CurrentDb.TableDefs(“1020d__Stats-LY”).RecordCount

———
Private Sub Run1000SeriesQueries_Click()
    DoCmd.Hourglass True
    Beep
 �
    StatusBox.Value = “Starting Series 1000 Queries…” & vbCrLf
    Me.Repaint
 �
    DoCmd.SetWarnings False
    DoCmd.OpenQuery “1010a_Stats-TY”, acViewNormal, acEdit
    DoCmd.OpenQuery “1010b_MCCApps-TY”, acViewNormal, acEdit
    DoCmd.OpenQuery “1010c_MCCPercBus-TY”, acViewNormal, acEdit
    DoCmd.OpenQuery “1010d_Stats-TY”, acViewNormal, acEdit
     �
    StatusBox.Value = StatusBox.Value & vbCrLf & “1010a, b, c, d Completed”
    Me.Repaint
    StatusBox.Value = StatusBox.Value & vbCrLf & “1010a Record Count: ” & CurrentDb.TableDefs(“1010a__Stats-TY”).RecordCount
    Me.Repaint
    StatusBox.Value = StatusBox.Value & vbCrLf & “1010d Record Count: ” & CurrentDb.TableDefs(“1010d__Stats-TY”).RecordCount & vbCrLf
    Me.Repaint
 �
    DoCmd.OpenQuery “1020a_Stats-LY”, acViewNormal, acEdit
    DoCmd.OpenQuery “1020b_MCCApps-LY”, acViewNormal, acEdit
    DoCmd.OpenQuery “1020c_MCCPercBus-LY”, acViewNormal, acEdit
    DoCmd.OpenQuery “1020d_Stats-LY”, acViewNormal, acEdit
 �
    StatusBox.Value = StatusBox.Value & vbCrLf & “1020a, b, c, d Completed”
    Me.Repaint
    StatusBox.Value = StatusBox.Value & vbCrLf & “1020a Record Count: ” & CurrentDb.TableDefs(“1020a__Stats-LY”).RecordCount
    Me.Repaint
    StatusBox.Value = StatusBox.Value & vbCrLf & “1020d Record Count: ” & CurrentDb.TableDefs(“1020d__Stats-LY”).RecordCount & vbCrLf
    Me.Repaint
 �
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
 �
    StatusBox.Value = StatusBox.Value & vbCrLf & “Query Series 1000 Completed”
    Me.Repaint
 �
    Beep

End Sub


http://www.anysitesupport.com/vba-table-record-count/

By | 2017-12-01T23:47:52+00:00 March 30th, 2011|Access, Computers, Documentation, Microsoft, VBA|0 Comments