Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
how to get total records no. from data base
- Thread starter cmaknp
- Start date
- Thread starter
- #2
Post code showing where error occurs - tell us what error you're getting.
READ FAQ referred below! Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first
'There are 10 kinds of people in the world: those who understand binary, and those who don't.'
READ FAQ referred below! Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first
'There are 10 kinds of people in the world: those who understand binary, and those who don't.'
Hi,
I don't consider this to be the most efficient method but you could try.
In the load event of the form that contains the text box put in the following code:
Private Sub Form_Load()
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim tdfs as DAO.TableDefs
Dim tdf as DAO.TableDef
Dim total as long
total = 0
Set db = CurrentDb
Set tdfs = db.TableDefs
For Each tdf In tdfs
Set rs = db.OpenRecordset(tdf.Name, dbOpenDynaset)
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
total = total + rs.RecordCount
End If
rs.Close
Next tdf
Me.Text1 = total
End Sub
The problem with this code is that it will return the record count of tables in the database including the MSysObjects and so. So if you wnat to have only the records in the tables that you created counted, then you must hard code each table name into the system.
With regards,
PGK
I don't consider this to be the most efficient method but you could try.
In the load event of the form that contains the text box put in the following code:
Private Sub Form_Load()
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim tdfs as DAO.TableDefs
Dim tdf as DAO.TableDef
Dim total as long
total = 0
Set db = CurrentDb
Set tdfs = db.TableDefs
For Each tdf In tdfs
Set rs = db.OpenRecordset(tdf.Name, dbOpenDynaset)
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
total = total + rs.RecordCount
End If
rs.Close
Next tdf
Me.Text1 = total
End Sub
The problem with this code is that it will return the record count of tables in the database including the MSysObjects and so. So if you wnat to have only the records in the tables that you created counted, then you must hard code each table name into the system.
With regards,
PGK
- Thread starter
- #6
With a dao recordset you can do it this way.
This was set up in the form load procedure. Dim lastRecord as integer in the Declarations Section
Dim dbsOurMembers As Database
Dim rstLocalMembers As Recordset
Set dbsOurMembers = OpenDatabase("c:\YourFolder\YourDatabase.mdb"
'
With rstLocalMembers
.MoveLast
LastRecord = .RecordCount
.MoveFirst
End With
This was set up in the form load procedure. Dim lastRecord as integer in the Declarations Section
Dim dbsOurMembers As Database
Dim rstLocalMembers As Recordset
Set dbsOurMembers = OpenDatabase("c:\YourFolder\YourDatabase.mdb"
'
With rstLocalMembers
.MoveLast
LastRecord = .RecordCount
.MoveFirst
End With
- Status
