NoMatch Property 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