Microsoft Access Tips for Serious Users

Provided by Allen Browne.  Last updated: February 2008.

Return to the same record next time form is opened

When a form is opened, you may like to automatically load the most recently edited record. To do so:

• Create a table to save the record's Primary Key value between sessions;
• Use the form's Unload event to save the current record's ID;
• Use the form's Load event to find that record again.

As an example, take a form that has CustomerID as the primary key field.

1. Create a table to save the Primary Key value between sessions

Create a table with these 3 fields:

    Field Name    Type        Description
    Variable      Text, 20    Holds the variable name. Mark as primary key.
    Value         Text, 80    Holds the value to be returned.
    Description   Text, 255   What this variable is used for/by.

Save this table with the name "tblSys". You may care to mark this as a hidden table.

2. Use the form's UnLoad event to save the record's ID.

Set the form's On Unload property to [Event Procedure], and add the following code. It finds (or creates) a record in tblSys where the field Variable contains "CustomerIDLast", and stores the current CustomerID in the field called Value.

Sub Form_Unload (Cancel As Integer)
    Dim rs As DAO.Recordset

    If Not IsNull(Me.CustomerID) Then
        Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)
        With rs
            .FindFirst "[Variable] = 'CustomerIDLast'"
            If .NoMatch Then
                .AddNew        'Create the entry if not found.
                    ![Variable] = "CustomerIDLast"
                    ![Value] = Me.CustomerID
                    ![Description] = "Last customerID, for form " & Me.Name
                .Update
            Else
                .Edit          'Save the current record's primary key.
                    ![Value] = Me.CustomerID
                .Update
            End If
        End With
        rs.Close
    End If
    Set rs = Nothing
End Sub

3. Use the form's Load event to find that record again.

Set the form's On Load property to [Event Procedure], and add the following code. It performs these steps:

• locates the record in tblSys where the Variable field contains "CustomerIDLast";
• gets the last stored CustomerID from the Value field;
• finds that CustomerID in the form's clone recordset;
• moves to that record by setting the form's Bookmark.

Sub Form_Load()
    Dim varID As Variant
    Dim strDelim As String
    'Note: If CustomerID field is a Text field (not a Number field), remove single quote at start of next line.
    'strDelim = """"

    varID = DLookup("Value", "tblSys", "[Variable] = 'CustomerIDLast'")
    If IsNumeric(varID) Then
        With Me.RecordsetClone
            .FindFirst "[CustomerID] = " & strDelim & varID & strDelim
            If Not .NoMatch Then
                Me.Bookmark = .Bookmark
            End If
        End With
    End If
End Sub

That's it!

Incidentally, once you have a tblSys, you will find numerous uses for it. Instead of hard coding your company name into perhaps a dozen reports, add another record to tblSys where the field Variable contains "CompanyName", and the field Value contains the actual company name. A text box at the head of any report can then look up this value by setting its ControlSource to:

    =DLookup("Value", "tblSys", "Variable = 'CompanyName'")