Bookmarking and Moving through Records with ADO - Moving through the Recordset and Bookmarking

(Page 3 of 4 )

Moving the cursor from one record to another gives us the ability to cycle through the records one after another. The databound controls leverage this ability to display all the columns for a given row of data. Moving the record pointer is facilitated by the various listed Move methods. The code at the end of this section in a Form_Load() event of a form shows how you may move to records as well as bookmark them along the way. The following methods will be considered with some built in constants for the Bookmark.

• Move(<number of records>,start)
• MoveFirst
• MovePrevious
• MoveNext
• MoveLast
• Bookmark

Move Method

The Move method takes two arguments, the first being the number of records to move through and the second parameter called Start. It's the record to start from. It could also represent a record that has already been bookmarked, or any of the constants adBookMarkFirst, adBookmarkLast, and adBookmarkCurrent. You will see how the bookmark is established shortly. You will also see the how the Move method is called with the arguments. The first argument, which is a number, could be negative as well, but care has to be exercised in using it. With number=0 nothing happens.

MoveFirst, MoveLast, MoveNext, and MovePrevious

These 'moves' do exactly what they say. They do not take any arguments. You have to be careful using these because some movements are not allowed for certain  CursorTypes. For example, for the forward only cursor you cannot obviously call 'MovePrevious,' or 'MoveFirst' when you are looking at record 2. For movements allowed by cursor types, refer to the second article of the tutorial. You must also exercise caution when you are on the first and last records and trying to use the MovePrevious and MoveNext methods as they fall in the BOF and EOF areas. In these cases you can come back to a previous bookmark you have established without aborting the program.

Bookmark Property

The MoveFirst and MoveLast methods take you to the first and last records in the recordset. The bookmark property lets you mark a recordset you may want to go back to, very much like a bookmark in a book you are reading. This means once you have bookmarked a record, you can retrieve it later. When you set the bookmark, the current record pointer will be set to that record. The absolute value of the bookmark is seldom needed; all that is needed is to go back to that record, should you need to. Recordset will not retain the bookmark property if you close and open again, or if you call the Requery. You may also use the values adBookMarkFirst, adBookMarkLast, and adBookMarkCurrent for the Start parameter. You will find an example of this in the code that uses adBookmarkFirst.

The following code shows how to set a bookmark and how you may retrieve it. It also shows how to move between the records with the move methods. You may not be able to cut and paste the code in MS Access because there are formatting elements in the text.

Private Sub Form_Load()
'Example for Bookmark
Dim strsql As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb"
End With
strsql = "Select EmployeeID, LastName, FirstName, City from Employees"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strsql
End With

'Record Movement	'Absolute Position
rst.MoveNext	'Absolute position is now=2
rst.MoveNext	'Absolute position is now=3
Debug.Print rst.AbsolutePosition	'3
Dim varBkMark
varBkMark = rst.Bookmark	'absolute position =3 is bookmarked
Debug.Print varBkMark	'514
rst.MoveFirst	'absolute position is now=1
Debug.Print rst.AbsolutePosition	'1
rst.Move 4, varBkMark	'move four records from Bookmark, absolute position is 7
Debug.Print rst.AbsolutePosition	'7
rst.Bookmark = varBkMark	'Bookmark back to 3
Debug.Print (rst.AbsolutePosition)	'3
rst.Requery
Debug.Print rst.Bookmark	'512 !oops Bookmark got changed
rst.Move 2, adBookMarkFirst	'move two records from the first record, absolute position is 3
Debug.Print rst.AbsolutePosition	'3

End Sub

Next: Binding the recordset to Controls on the form >>
 

More ASP.NET Articles
More By Jayaram Krishnaswamy

Comments On: Bookmarking and Moving through Records with ADO

· Yes, there are lot more things to discuss with ADO. Even the 'Bookmark' is not...   >>> Post your comment now!