NoMatch Property

Applies To

Dynaset-Type Recordset Object, Recordset Object, Snapshot-Type Recordset Object, Table-Type Recordset Object.

Description

Returns a value indicating whether a particular record was found using the Seek method or one of the Find methods. True (-1) indicates that the desired record was not found.

Remarks

You can use the NoMatch property with Recordset objects.

When you open or create a Recordset object, its NoMatch property is set to False (0).

To locate a record, use the Seek method on a Table-Type Recordset object or one of the Find methods on a dynaset- or Snapshot-Type Recordset object. Check the NoMatch property setting to see whether the record was found.

If the Seek or Find method is unsuccessful and the NoMatch property is True, the current record will no longer be valid. Be sure to obtain the current record's bookmark before using the Seek method or a Find method if you'll need to return to that record.

Note

Using any of the Move methods on a Recordset object won't affect its NoMatch property setting.

See Also

BOF, EOF Properties; FindFirst, FindLast, FindNext, FindPrevious Methods; Seek Method.

Example

This example changes the job title of all sales representatives in an Employees table. It creates a Dynaset-Type Recordset and then uses FindFirst and FindNext to locate every record satisfying the criteria for the title. It prepares each record for editing, changes the title, and saves the change with the Update method.

Dim strCriteria As String, varSaveHere As Variant
Dim dbsNorthwind As Database, rstEmployees As Recordset
strCriteria = "Title = 'Sales Representative'"    ' Define search 
                        ' criteria.
Set dbsNorthwind =  DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
' Create Recordset.
Set rstEmployees = dbsNorthwind.OpenRecordset("Employees", dbOpenDynaset)
varSaveHere = rstEmployees.Bookmark    ' Save Bookmark.
rstEmployees.FindFirst strCriteria    ' Locate first occurrence.
Do Until rstEmployees.NoMatch    ' Loop until no matching 
                ' records.

    With rstEmployees
       .Edit        ' Enable editing.
       !Title = "Account Executive"    ' Change title.
       .Update    ' Save changes.
       .FindNext strCriteria    ' Locate next record.
    End With
Loop            ' End of loop.
rstEmployees.Bookmark = varSaveHere

Tip

In some situations, using an update query is faster.

Example (Microsoft Access)

The following example uses the NoMatch property to determine whether a FindFirst method process has been successful.

Function FindCountry() As Integer
    Dim intI As Integer, varRecord() As Variant
    Dim dbs As Database, rstOrders As Recordset
    Dim strCountry As String
    
    ' Return Database variable that points to current database.
    Set dbs = CurrentDb
    ' Create Dynaset-Type Recordset object.
    Set rstOrders = dbs.OpenRecordset("Orders", dbOpenDynaset)
    strCountry = InputBox("Please enter country name.")
    intI = 0
    rstOrders.FindFirst "[ShipCountry] = '" & strCountry & "'"
    If rstOrders.NoMatch Then
        FindCountry = False
        Exit Function
    Else
        Debug.Print rst!OrderID
    End If
    FindCountry = True
End Function

Example (Microsoft Excel)

This example adds all the names of contacts for the state of Washington to a list box on a new dialog sheet and then runs the dialog box. The data is drawn from the Customer recordset in the NWINDEX.MDB database.

To create the NWINDEX.MDB database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database
Dim rs As Recordset
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set rs = db.OpenRecordset("SELECT * FROM Customer")
criteria = "[REGION] = 'WA'"
Set customDialog = DialogSheets.Add

Set list1 = customDialog.ListBoxes.Add(78, 42, 84, 80)
rs.FindFirst criteria
Do Until rs.NoMatch
    list1.AddItem (rs.fields("CONTACT").Value)
    rs.FindNext criteria
Loop
customDialog.Show
rs.Close
db.Close