How-To: Locate a Record on a Recordset

Some or most operations on a table, a query, or a form require that you locate the necessary record first. For example in order to change the value stored in a record, you must first find out where that record is. As done visually using a table, a query or a form, on a record set, editing a record is done in various steps. If you know exactly the index of the record that contains the value you want to edit, you can call the Move() method of the Recordset object to jump to it.

Consider the following table that lists some items sold by a department store:

From this table, imagine that you want to change the name or description of the Bow Belt Skirtsuit to Bow Belt Skirt Suit. By looking at the table, you can see that it is an item from the 5th record. Before editing the value, you can first jump to the record number 5. Since the indexes of the records are zero-based, here is an example that use Microsoft Access Object library to jump to that record:

Private Sub cmdMovePosition_Click()
   Dim dbDepartmentStore As Object
   Dim rstStoreItems As Object

   Set dbDepartmentStore = CurrentDb
   Set rstStoreItems = dbDepartmentStore.OpenRecordset("StoreItems ")

   rstStoreItems.Move 4
End Sub

Although this uses the Microsoft Access Object Library, you can also apply it to DAO by simply changing the names of the objects to the appropriate ones:

Private Sub cmdMovePosition_Click()
   Dim dbDepartmentStore As DAO.Database
   Dim rstStoreItems As DAO.Recordset

   Set dbDepartmentStore = CurrentDb
   Set rstStoreItems = dbDepartmentStore.OpenRecordset("StoreItems")

   rstStoreItems.Move 4
End Sub

You can also call one of the other Move-oriented methods (MoveFirst(), MovePrevious(), MoveNext(), or MoveLast()). Once you get to a record, you can then perform the necessary operation. For example, you can retrieve the values held by that record.

In the same way, when you find out that the information about a record has changed, you can update it. To start you can create a form that would be used to represent each field of a table. Here is an example:

To locate an item, the most obvious request is probably to ask the user to enter the store item number in the top text box and, when the user presses Tab (or Enter) to move the focus away from the Item Number text box, you can use a Recordset object to locate the record and fill the controls with its value. This would be done as follows:

Private Sub cmdReset_Click()
    Me.txtDateEntered = Date
    Me.txtItemNumber = ""
    Me.txtItemName = ""
    Me.txtItemCategory = ""
    Me.txtItemSize = ""
    Me.txtOriginalPrice = "0.00"
    Me.txtQuantity = "0"
    Me.txtItemNumber.SetFocus
End Sub

Private Sub txtItemNumber_LostFocus()
    Dim rstStoreItems As ADODB.Recordset
    Dim blnFound As Boolean
    ' This flag will allow us to know whether the item number was found
    Dim fldItem As ADODB.Field
    
    ' Since we are only starting, we assume that no item number has been found
    blnFound = False
    
    ' If there is no value in the Item Number text box, don't do nothing
    If Me.txtItemNumber = "" Then Exit Sub
    
    Set rstStoreItems = New ADODB.Recordset
    rstStoreItems.Open "SELECT * FROM StoreItems WHERE ItemNumber = '" & _
                       txtItemNumber & "'", _
                       CurrentProject.Connection, _
                       adOpenStatic, adLockReadOnly, adCmdText
  
    With rstStoreItems
        ' Check each record
        While Not .EOF
            ' Check the name of the column
            For Each fldItem In .Fields
                ' If the current column is ItemNumber
                If fldItem.Name = "ItemNumber" Then
                    ' Check its value
                    ' If the current column holds the item number that the user entered
                    If fldItem.Value = txtItemNumber Then
                        ' ... then get the record and display its values in the controls
                        Me.txtDateEntered = .Fields("DateEntered")
                        Me.txtItemName = .Fields("ItemName")
                        Me.txtItemCategory = .Fields("ItemCategory")
                        Me.txtItemSize = .Fields("ItemSize")
                        Me.txtOriginalPrice = .Fields("OriginalPrice")
                        Me.txtQuantity = .Fields("Quantity")
                        ' Set the found flag to true (we will use it later)
                        blnFound = True
                    End If
                End If
            Next
            ' In case you didn't find it, move to the next record
            .MoveNext
        Wend
    End With
    
    ' If the item number was not found, ...
    If blnFound = False Then
        ' ... let the user know, ...
        MsgBox "The item number you entered is not in our list of products"
        ' ... and reset the form
        cmdReset_Click
    End If
                        
    rstStoreItems.Close
    Set rstStoreItems = Nothing
End Sub