NoMatch Property

Applies To Dynaset-Type Recordset object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object.

Description

Indicates whether a particular record was found by using the Seek method or one of the Find methods (Microsoft Jet workspaces only).

Return Values

The return value is a Boolean that is True if the desired record was not found. When you open or create a Recordset object, its NoMatch property is set to False.

Remarks

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 uses the NoMatch property to determine whether a Seek and a FindFirst were successful, and if not, to give appropriate feedback. The SeekMatch and FindMatch procedures are required for this procedure to run.

Sub NoMatchX()

    Dim dbsNorthwind As Database
    Dim rstProducts As Recordset
    Dim rstCustomers As Recordset
    Dim strMessage As String
    Dim strSeek As String
    Dim strCountry As String
    Dim varBookmark As Variant
    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    ' Default is dbOpenTable; required if Index property will
    ' be used.
    Set rstProducts = dbsNorthwind.OpenRecordset("Products")

    With rstProducts
        .Index = "PrimaryKey"

        Do While True
            ' Show current record information; ask user for
            ' input.
            strMessage = "NoMatch with Seek method" & vbCr & _
                "Product ID: " & !ProductID & vbCr & _
                "Product Name: " & !ProductName & vbCr & _
                "NoMatch = " & .NoMatch & vbCr & vbCr & _
                "Enter a product ID."
            strSeek = InputBox(strMessage)
            If strSeek = "" Then Exit Do

            ' Call procedure that seeks for a record based on
            ' the ID number supplied by the user.
            SeekMatch rstProducts, Val(strSeek)
        Loop

        .Close
    End With

    Set rstCustomers = dbsNorthwind.OpenRecordset( _
        "SELECT CompanyName, Country FROM Customers " & _
        "ORDER BY CompanyName", dbOpenSnapshot)

    With rstCustomers

        Do While True
            ' Show current record information; ask user for
            ' input.
            strMessage = "NoMatch with FindFirst method" & _
                vbCr & "Customer Name: " & !CompanyName & _
                vbCr & "Country: " & !Country & vbCr & _
                "NoMatch = " & .NoMatch & vbCr & vbCr & _
                "Enter country on which to search."
            strCountry = Trim(InputBox(strMessage))
            If strCountry = "" Then Exit Do

            ' Call procedure that finds a record based on
            ' the country name supplied by the user.
            FindMatch rstCustomers, _
                "Country = '" & strCountry & "'"
        Loop

        .Close
    End With

    dbsNorthwind.Close

End Sub

Sub SeekMatch(rstTemp As Recordset, _
    intSeek As Integer)

    Dim varBookmark As Variant
    Dim strMessage As String

    With rstTemp
        ' Store current record location.
        varBookmark = .Bookmark
        .Seek "=", intSeek

        ' If Seek method fails, notify user and return to the
        ' last current record.
        If .NoMatch Then
            strMessage = _
                "Not found! Returning to current record." & _
                vbCr & vbCr & "NoMatch = " & .NoMatch
            MsgBox strMessage
            .Bookmark = varBookmark
        End If

    End With

End Sub

Sub FindMatch(rstTemp As Recordset, _
    strFind As String)

    Dim varBookmark As Variant
    Dim strMessage As String

    With rstTemp
        ' Store current record location.
        varBookmark = .Bookmark
        .FindFirst strFind

        ' If Find method fails, notify user and return to the
        ' last current record.
        If .NoMatch Then
            strMessage = _
                "Not found! Returning to current record." & _
                vbCr & vbCr & "NoMatch = " & .NoMatch
            MsgBox strMessage
            .Bookmark = varBookmark
        End If

    End With

End Sub

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 dbs As Database, rst As Recordset
    Dim strCountry As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Create dynaset-type Recordset object.
    Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
    strCountry = InputBox("Please enter country name.")
    rst.FindFirst "ShipCountry = '" & strCountry & "'"
    If rst.NoMatch Then
        FindCountry = False
    Else
        FindCountry = True
        Debug.Print rst!OrderID
    End If
    rst.Close
    Set dbs = Nothing
End Function

Example (Microsoft Excel)

This example adds all the names of contacts for the state of Washington to a list on worksheet one. 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
rw = 0
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set rs = db.OpenRecordset("SELECT * FROM Customer")
criteria = "[REGION] = 'WA'"
Set wk = Worksheets.Add
rs.FindFirst criteria
Do Until rs.NoMatch
    rw = rw + 1
    wk.Range(rw, 1).Value = rs.fields("CONTACT").Value
    rs.FindNext criteria
Loop
rs.Close
db.Close