IgnoreNulls Property Example

This example sets the IgnoreNulls property of a new Index to True or False based on user input, and then demonstrates the effect on a Recordset with a record whose key field contains a Null value.

Sub IgnoreNullsX()

   Dim dbsNorthwind As Database
   Dim tdfEmployees As TableDef
   Dim idxNew As Index
   Dim rstEmployees As Recordset

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set tdfEmployees = dbsNorthwind!Employees

   With tdfEmployees
      ' Create a new Index object.
      Set idxNew = .CreateIndex("NewIndex")
      idxNew.Fields.Append idxNew.CreateField("Country")

      ' Set the IgnoreNulls property of the new Index object 
      ' based on the user's input.
      Select Case MsgBox("Set IgnoreNulls to True?", _
         vbYesNoCancel)
         Case vbYes
            idxNew.IgnoreNulls = True
         Case vbNo
            idxNew.IgnoreNulls = False
         Case Else
            dbsNorthwind.Close
            End
      End Select

      ' Append the new Index object to the Indexes 
      ' collection of the Employees table.
      .Indexes.Append idxNew
      .Indexes.Refresh
   End With

   Set rstEmployees = _
      dbsNorthwind.OpenRecordset("Employees")

   With rstEmployees
      ' Add a new record to the Employees table.
      .AddNew
      !FirstName = "Gary"
      !LastName = "Haarsager"
      .Update

      ' Use the new index to set the order of the records.
      .Index = idxNew.Name
      .MoveFirst

      Debug.Print "Index = " & .Index & _
         ", IgnoreNulls = " & idxNew.IgnoreNulls
      Debug.Print "  Country - Name"

      ' Enumerate the Recordset. The value of the 
      ' IgnoreNulls property will determine if the newly 
      ' added record appears in the output.
      Do While Not .EOF
         Debug.Print "    " & _
            IIf(IsNull(!Country), "[Null]", !Country) & _
            " - " & !FirstName & " " & !LastName
         .MoveNext
      Loop

      ' Delete new record because this is a demonstration.
      .Index = ""
      .Bookmark = .LastModified
      .Delete
      .Close
   End With

   ' Delete new Index because this is a demonstration.
   tdfEmployees.Indexes.Delete idxNew.Name
   dbsNorthwind.Close

End Sub