How It Works

The Sort method of the Recordset object allows you to specify one or more fields by which to sort the recordset. The preceding example sorts the contacts by last name, first name, and middle name. I hope you noticed that when you ran the form again, the order of the records was now alphabetical based on those three fields.

The Find Method

The Find method of the Recordset object allows you to navigate to a particular record that matches certain criteria. Here is a sample procedure that uses the Find method to locate a record that has a value for intContactId of 2.

Sub TestFind()

Dim rsContacts As ADODB.Recordset

'create a new instance of a recordset Set rsContacts = New ADODB.Recordset 'set various properties of the recordset With rsContacts

.CursorType = adOpenStatic

'open the recordset based on tblContacts table using the existing connection .Open "tblContacts", CurrentProject.Connection End With

'find a contact with the intContactId value of 2 rsContacts.Find "[intContactId] = 2"

'output a message to the Immediate Window regarding find results If rsContacts.EOF Then

Debug.Print "Specified record not found"

Else

'record was found - display some info

Debug.Print "Contact Id: " & rsContacts!intContactId & _ " Last Name: " & rsContacts!txtLastName & _ " First Name: " & rsContacts!txtFirstName

End If

'close the recordset rsContacts.Close

'set the recordset and connection to nothing Set rsContacts = Nothing

End Sub

The Find method does not remove any records from the database, but instead just navigates you to a different record. Notice how the preceding example does not use a separate connection object, but uses the existing connection for the project to create the recordset. This is an acceptable way if you will always be working with the same database of your current project.

When you run the preceding procedure from the Immediate Window, you see results similar to those shown in Figure 5.12.

Immediate

Contact Id: 2 Last Name: Doe First Name: John

I -

1

_

The Filter Property

The Filter property allows you to filter the recordset to a smaller subset that meets a certain criteria. For example, you might want to filter your recordset to last names starting with a certain letter.

Sub TestFilter()

Dim rsContacts As ADODB.Recordset

'create a new instance of a recordset Set rsContacts = New ADODB.Recordset 'set various properties of the recordset With rsContacts

.CursorType = adOpenStatic

'open the recordset based on tblContacts table using the existing connection .Open "tblContacts", CurrentProject.Connection End With

'filter the recordset to contain only records with 'last names starting with D

rsContacts.Filter = "txtLastName Like 'D*'"

'output a message to the Immediate Window regarding find results If rsContacts.EOF Then

Debug.Print "No records met that criteria."

Else

'record was found - display some info for each record Do Until rsContacts.EOF

Debug.Print "Contact Id: " & rsContacts!intContactId & _ " Last Name: " & rsContacts!txtLastName & _ " First Name: " & rsContacts!txtFirstName rsContacts.MoveNext

Loop End If

'close the recordset rsContacts.Close

'set the recordset and connection to nothing Set rsContacts = Nothing

End Sub

The preceding example uses the Filter property to filter the recordset down to records that start with D. To prove the filter worked, all the records remaining are displayed in the Immediate Window, as shown in Figure 5.13.

Tm mediate

TestFilter

.A.

Contact Id:

2

Last

Name:

Doe

First

Name: John

--

Contact Id:

4

Last

Name:

Doe

First

Name: Jane

Figure 5.13

Using Bookmarks to Mark and Locate Data

Bookmarks allow you to mark a location in a recordset and return to that location later. For example, suppose you have a variable called varPosition that has been declared as a variant. You can save the location of the current record in a bookmark.

varPosition = rsContacts.Bookmark and then return to it later by setting the Bookmark property equal to the saved position.

rsContacts.BookMark = varPosition

0 0

Post a comment