Work Through Records We Have Viewed Today

We would like the ability to re-visit records we have examined, in reverse chronological sequence. Sometimes after moving to one record by a Find or similar contrivance, we wish to return to the previous record without re-issuing the Find.

To achieve this we will need a unique identifier for each record, we will need to record this identifier as we move from record to record, and we will need a mechanism to find a record based on its unique identifier.

Unique Identifier

We have created a field in our table, with an identifier or name of "Id"; we have set the data type to be "AutoNumber". Each record added to our table will be assigned a unique identifier.

Recording the Identifier

We could write the identifiers to a file, or we could record them in an array in memory. We choose to do the latter. We will need to declare a numeric integer array associated with the GUI form; we must initialize the array. We will need to associate a "store to array" procedure with each and every event that moves us away from the current record; our "Oldest" and "Current" command buttons are such events.

In our User Module we introduce a declaration:

Public intRecords() As Integer

This declares an array "intRecords" to be an integer array, available publicly throughout our procedural code.

In the Open event for the form itself, we add code to initialize the array:

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
ReDim intRecords(0)
End Sub

(The line "DoCmd.Maximize" arises from an earlier essay in Access itself, using the Tools, Startup menu).

We Re-dimension the integer array to have one element; it is immediately available for storage.

We insert code at the start of any event that moves us away from the current record, thus:

Private Sub cmdOldest_Click()
intRecords(UBound(intRecords)) = Me.ID
ReDim Preserve intRecords(UBound(intRecords) + 1)
Etc

And

Private Sub cmdCurrent_Click()
intRecords(UBound(intRecords)) = Me.ID
ReDim Preserve intRecords(UBound(intRecords) + 1)
Etc

Finding a record based on an identifier

We will introduce a new command button that will allow us to work backwards through our stored array. Here is skeletal procedure code for the Click event of our "previous" Command Button.

Private Sub cmdPrevious_Click()
If UBound(intRecords) > 0 Then
ReDim Preserve intRecords(UBound(intRecords) - 1)
MsgBox "Going to " & intRecords(UBound(intRecords))
Else
MsgBox "No more records!"
End If
End Sub

Of course, instead of a message box announcing that we plan to go to a specific record, we need a call to a procedure that will actually go to that record:

Private Sub cmdPrevious_Click()
If UBound(intRecords) > 0 Then
ReDim Preserve intRecords(UBound(intRecords) - 1)
Call RecordSeek("Id", intRecords(UBound(intRecords)))
Else
MsgBox "No more records!"
End If
End Sub

Here is the small procedure to locate a record with a specific and unique identifier:

Public Function RecordSeek(strkey, intValue)
Dim strFilter As String
Dim strSortOrder As String
strFilter = "[" & strkey & "] =" & Str(intValue)
strSortOrder = strkey
Me.Filter = strFilter
Me.FilterOn = True
Me.OrderBy = strSortOrder
Me.OrderByOn = True
DoCmd.GoToRecord , , acFirst
End Function

Search