Recordset.NoMatch Property (DAO)

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

Syntax

expression .NoMatch

expression A variable that represents a Recordset object.

Remarks

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

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.

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 

The following example shows how to use the Seek method to find a record in a linked table.

Sample code provided by: The Microsoft Access 2010 Programmer’s Reference | About the Contributors

Sub TestSeek()
    ' Get the path to the external database that contains
    ' the tblCustomers table we're going to search.
    Dim strMyExternalDatabase
    Dim dbs    As DAO.Database
    Dim dbsExt As DAO.Database
    Dim rst    As DAO.Recordset
    Dim tdf    As DAO.TableDef
    
    Set dbs = CurrentDb()
    Set tdf = dbs.TableDefs("tblCustomers")
    strMyExternalDatabase = Mid(tdf.Connect, 11)
    
    'Open the database that contains the table that is linked
    Set dbsExt = OpenDatabase(strMyExternalDatabase)
    
    'Open a table-type recordset against the external table
    Set rst = dbsExt.OpenRecordset("tblCustomers", dbOpenTable)
    
    'Specify which index to search on
    rst.Index = "PrimaryKey"
    
    'Specify the criteria
    rst.Seek "=", 123
    
    'Check the result
    If rst.NoMatch Then
        MsgBox "Record not found."
    Else
        MsgBox "Customer name: " & rst!CustName
    End If
    
    rst.Close
    dbs.Close
    dbsExt.Close
    Set rst = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    
    
End Sub

About the Contributors

Wrox Press is driven by the Programmer to Programmer philosophy. Wrox books are written by programmers for programmers, and the Wrox brand means authoritative solutions to real-world programming problems.