=========
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