Our office accesses a database of 17K records several times a day to answer questions by phone. Users are expected to record info on a given record if the transaction warrants it. Occasionally, a user will forget to make a note in the current record and will navigate to a new record, or several new records, before realizing the error. Access 2000 keeps track of the last 7 searches in a form during the current session (one can access this list by clicking the search icon and clicking the drop-down list), but if a user has searched for more than that, the earliest searched for item is dropped from the list. To keep track of all records visited during the current session, a list box is added to the form, with a default rowsource of " ". The following code populates the list box with the claim number, name, and record number of the current record each time the current record changes. Users can then click an item on this list to return to a given record. Note that the list is reset when the user closes the form.
Private Sub Form_Current() 'adds this record to the list of records last visited by the user Dim strCurrentClaimNo As String Dim strCurrentVictim As String Dim intCurrentRecordNo As Integer intCurrentRecordNo = Recordset.AbsolutePosition + 1 strCurrentClaimNo = [VC_LM] 'the claim number strCurrentVictim = [VICTIM] 'the client name Dim strCurrentList As String strCurrentList = lstRecordsVisited.RowSource If strCurrentList = " " Then 'this is the first record visited strCurrentList = strCurrentClaimNo & " " & strCurrentVictim Else 'add this record to the list strCurrentList = strCurrentList & "; '" & strCurrentClaimNo & " " & strCurrentVictim & " " & intCurrentRecordNo & "'" End If lstRecordsVisited.RowSource = strCurrentList end sub
Private Sub lstRecordsVisited_DblClick(Cancel As Integer) 'determine which item was selected from the list of last visited records and go to that record Dim strSelection As String Dim intSelectedItem As Integer Dim strFormName As String strFormName = Me.Name intSelectedItem = lstRecordsVisited.ListIndex strSelection = lstRecordsVisited.ItemData(intSelectedItem) Dim intRecordNo As Integer intRecordNo = Val(LTrim(Right(strSelection, (Len(strSelection) - InStrRev(strSelection, " "))))) DoCmd.GoToRecord acActiveDataObject, strFormName, acGoTo, intRecordNo End Sub