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