CODEWITHSUNDEEP
vbams-access
Jaitnium
Jaitnium

Reputation: 650

Can an individual record in a recordset be moved?

I have a table subform containing jobs to be completed. I'm creating an algorithm in VBA to organize the jobs in the most efficient order to be completed.

Is there a way to move an individual record in a recordset or am I stuck with OrderBy?

Edit: To add some clarity, I want to be able to move a record to any other index in the same table. I intend to run my algorithm which will move the records into the order they are to be completed. Then each records' "Process Date" field is set to keep track of the order.

Upvotes: 0

Views: 189

Answers (1)

C Perkins
C Perkins

Reputation: 3882

The short answer is "No", the index of a record in a recordset cannot be directly updated. The order of rows in a recordset can only be changed by either setting a different ORDER BY clause and requerying the database, or by setting the Recordset.Sort property or the Form.OrderBy property (when bound to a form).

Let's assume that there is a updatable recordset field called [JobOrder]. The SQL source query can include a sort order like ... ORDER BY [JobOrder] ASC which first sorts the data when it is retrieved from the database. As a matter of fundamental database concept, it should be assumed that if no ORDER BY clause is specified that the database can return data in a random order. (In practice that is not usually the case. It will be sorted by some indexed primary key by default, but that should not be assumed if the order is important.)

The form's (or subform's) sort order can be set and changed without requerying the data from the database again. That is done by setting the OrderBy property and ensuring that OrderByOn = True. (FYI: Unless you take measures to hide default tool ribbons (i.e. toolbars) and shortcut menus, this sort order can be altered by the user.)

Now your VBA code can use various techniques to set the JobOrder values. You could perhaps use the Me.RecordsetClone method to enumerate and update the values using the recordset object. Using RecordsetClone will avoid certain side effects of updating the bound primary recordset. Lastly, the following assumes that all records already have valid, unique JobOrder values, but it assumes that JobOrder is not required to be unique (since the swap technique temporarily sets two rows to the same value). It's up to you to write your own clever implementation to guarantee that JobOrder values remain valid and unique.

Private Sub MoveCurrentUp()
  Dim rs As Recordset2

  Dim thisID As Long
  Dim thisSort As Long
  Dim previousID As Long
  Dim previousSort As Long

  On Error Resume Next
  '* Error handling to avoid cases where recordset is empty
  '* and/or the current record is not valid (i.e. new record)

  If Not IsNull(Me.ID.Value) Then
    thisID = Me.ID.Value
    If Err.Number = 0 Then

      On Error GoTo Catch
      '* Any errors from this point should be
      '* handled specifically rather than ignored

      Set rs = Me.RecordsetClone

      rs.FindFirst "ID=" & thisID
      If Not rs.NoMatch Then
        thisSort = rs!JobOrder
        rs.MovePrevious
        If Not rs.BOF Then
          previousID = rs!ID
          previousSort = rs!JobOrder

          rs.Edit
          rs!JobOrder = thisSort
          rs.Update

          rs.MoveNext
          rs.Edit
          rs!JobOrder = previousSort
          rs.Update

          Set rs = Nothing
          RefreshSort
        End If
      End If
      Set rs = Nothing

      Debug.Print Me.Sort
    End If
  End If

  Exit Sub
Catch:
  MsgBox "Error updating order." & vbNewLine & vbNewLine & _
    "    " & Err.Number & ": " & Err.Description, vbOKOnly Or vbExclamation, "Error"
End Sub

Aferward, you would refresh the form's sort order with something like:

Private Sub RefreshSort(Optional restoreCurrentRecord As Boolean = True)
  Dim rs As Recordset2
  Dim saveID As Long
  saveID = Me.ID.Value

  Me.OrderBy = "[JobOrder] ASC"
  Me.OrderByOn = True

  If restoreCurrentRecord Then
    Set rs = Me.RecordsetClone
    rs.FindFirst "ID=" & saveID
    If Not rs.NoMatch Then
      Me.Bookmark = rs.Bookmark
    End If
    Set rs = Nothing
  End If
End Sub

Or you could update rows using SQL queries, then call Me.OrderByOn = False then Me.Requery to force the entire recordset to be reloaded in the proper order (assuming the record source has a proper ORDER BY clause). This technique has the benefit of wrapping all the changes in a transaction which can be committed or rolled back altogether, something you can't do with the bound form's recordset objects.

Upvotes: 2

Related Questions