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