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!
Q: Record counting question(s)
- Thread starter mikeg8
- Start date
RJFost
Programmer
- Apr 11, 2002
- 180
1. RecordCount will give you what you want, but its usage varies depending on what type of table/recordset you're dealing with. You might look at doing a SELECT COUNT(*) query vs. the table, too; much faster than opening -> counting -> closing the actual table.
2. You can check the value of the AbsolutePosition property (again, availability & usage depend on recordset type). But if you're wanting to know where you are in a recordset so that you can do something else & come back, look at cloning the recordset & setting a bookmark.
The Access help file does a better-than-usual job of explaining & demonstrating both of these operations.
2. You can check the value of the AbsolutePosition property (again, availability & usage depend on recordset type). But if you're wanting to know where you are in a recordset so that you can do something else & come back, look at cloning the recordset & setting a bookmark.
The Access help file does a better-than-usual job of explaining & demonstrating both of these operations.
mikeg8
I guess you know about the menu bar used as a default by Access ... "Record xx of yy"
This gives you the current record you are on plus the the total records. But RJFost has made a point that these numbers are relative. Depends on the query you running - straight from a table, or uses a join with other tables.
The record number is also relative -- depends on the sort - Sort by last name vs first name will give you totally different "current" record number.
Moving to the coding level, since you have already develed into it on some of your other posts...
This is for DAO. A slightly different syntax is required for ADO
dim dbs as DAO.database
dim rst as DAO.recordset
dim lngCount as Long
set dbs = currentdb()
set rst = dbs.openrecordset("YourTableOrQuery"
rst.MoveLast
lngCount = rst.RecordCount
rst.Close
dbs.Close
Likewise, you can programatically track the current record "number" in a loop...
Dim intCur as Long
rs.MoveFirst
intCur = 0
For / While loop
intCur = intCur + 1
rst.MoveNext
end of loop
Lastly, autonumbers...
For the primay key, many developers will use a autonumber during the table design. If using a database that is not replicated, Access will assign a serial number for the primary key -- 1, 2, 3, ...
So this serial number will give you some idea of the "current record number". BUT, there are some things to consider with the use autonumbers.
- If the user aborts the entry of a record, the autonumber will have advanced any way. So the "pure" numeric sequence will have been broken.
- If records are deleted, the autonumber does not renumber the records.
Also, read MichaelRed' FAQ on autonumbers.
Richard
I guess you know about the menu bar used as a default by Access ... "Record xx of yy"
This gives you the current record you are on plus the the total records. But RJFost has made a point that these numbers are relative. Depends on the query you running - straight from a table, or uses a join with other tables.
The record number is also relative -- depends on the sort - Sort by last name vs first name will give you totally different "current" record number.
Moving to the coding level, since you have already develed into it on some of your other posts...
This is for DAO. A slightly different syntax is required for ADO
dim dbs as DAO.database
dim rst as DAO.recordset
dim lngCount as Long
set dbs = currentdb()
set rst = dbs.openrecordset("YourTableOrQuery"
rst.MoveLast
lngCount = rst.RecordCount
rst.Close
dbs.Close
Likewise, you can programatically track the current record "number" in a loop...
Dim intCur as Long
rs.MoveFirst
intCur = 0
For / While loop
intCur = intCur + 1
rst.MoveNext
end of loop
Lastly, autonumbers...
For the primay key, many developers will use a autonumber during the table design. If using a database that is not replicated, Access will assign a serial number for the primary key -- 1, 2, 3, ...
So this serial number will give you some idea of the "current record number". BUT, there are some things to consider with the use autonumbers.
- If the user aborts the entry of a record, the autonumber will have advanced any way. So the "pure" numeric sequence will have been broken.
- If records are deleted, the autonumber does not renumber the records.
Also, read MichaelRed' FAQ on autonumbers.
Richard
With regards to your second question, the AbsolutePosition property referred to by RJFrost isn't a reliable method of determining the actual position within the table because it changes as records get added and deleted, so for example record 8 one day may become record 7 the following day.
Remember that relational databases work in a set oriented nature (this goes for any RDBMS, not just Access) and therefore any particular query cannot guarantee to retrieve records in a particular order unless the query includes an Order By clause to force sorting. There is no general concept of a record number per se across the entire range of RDBMS's.
John
Remember that relational databases work in a set oriented nature (this goes for any RDBMS, not just Access) and therefore any particular query cannot guarantee to retrieve records in a particular order unless the query includes an Order By clause to force sorting. There is no general concept of a record number per se across the entire range of RDBMS's.
John
- Thread starter
- #7
- Thread starter
- #9
- Status
Similar threads
- Locked
- Solved
- Locked
- Question
- Locked
- Question
