IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, email@example.com
In xBase, record numbers are commonly used for the record count, to loop through records, or to jump back to a record after working with another. Discovering there is no equivalent in Access can be a conceptual hurdle, but the absence of record numbers is intentional. Strictly speaking, assigning a physical record number is not relationally correct, and interesting things can happen to record numbers in a multi-tasking environment.
There are two ways record-number addicts try to get around this in Access. The first is the AutoNumber type field (Counter in Access 1 and 2). While these at first appear similar, deleted AutoNumbers are not reassigned in the same way xBase reassigns record numbers, so the last number used will usually not be equal to the number of records. AutoNumber fields can be (and often are) used in referential lookups - an idea that would be disastrous with xBase record numbers. The other quasi-record number in Access is the number displayed in the bottom left of an open table, query, or form window, such as "Record 12 of 574". These numbers can be accessed if you have a good reason (see Numbering Entries in a Report or Form ), but please make sure you understand what not to do with them before you do so.
So how do you code without record numbers? This function demonstrates how to use the RecordCount property, how to loop through records, and how to save a bookmark to return to a record again later.
Function NoRecordNum () ' Purpose: shows how to get around without record numbers. ' Modifications for Access 1.x noted in comments as "v.1:" Dim db As Database Dim Rst As Recordset ' v.1: Rst as Table Dim NumRecords As Long Dim BM As String Set db = CurrentDB() ' v.2: Set db = dbengine(0)(0) Set Rst = db.OpenRecordset("tblCustomers") ' v.1: db.OpenTable("tblCustomers") If Rst.RecordCount = 0 Then Exit Function End If ' Show record count, and loop backwards through records. Rst.MoveLast NumRecords = Rst.RecordCount MsgBox NumRecords & " records." Do While Not Rst.BOF Debug.print Rst![ID] Rst.MovePrevious Loop ' Use a bookmark to move back to a chosen record. Randomize Timer Rst.MoveFirst Rst.Move Rnd * NumRecords 'Skip to a random record BM = Rst.BookMark 'Save the bookmark Rst.MoveLast 'Move somewhere else Rst.BookMark = BM 'Back to previous place Rst.Close Set Rst = Nothing Set db = Nothing Debug.Print "Finished" End Function
Please note that RecordCount contains:
Therefore, use the MoveLast method if you need to know the number of records in a recordset based on a query.
Bear in mind that you will loop through records far less often in Access. Wholesale changes are handled more efficiently by action queries (Delete, Update, Append, and MakeTable).
|Home||Index of tips||Top|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'What, no record numbers?'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...