How To Identify The Last Row Worked On (VBA Variable Scope)

So, I had an interesting question regarding how to find the last row the user was working on before the closed the form, so when the reopen the form, they could continue to work on that record.

(This is actually expanding on an example I showed: https://vbahowto.com/how-to-highlight-the-current-row-in-a-continuous-form-in-3-steps/)

The the following code will demonstrate one way you can handle the situation.

Here is all the form code:

Private Sub Form_Unload(Cancel As Integer)
    'MsgBox "Last Record Worked On: " & Me.CustomerID
    Dim strSQL As String
    
    strSQL = "UPDATE MarkedRow SET CustomerID ='" & Me.CustomerID & "' WHERE FromForm ='frmB'"
    CurrentDb.Execute strSQL
    
End Sub


Private Sub Form_Open(Cancel As Integer)
 
    Dim strCriteria As String
    
    strCriteria = Nz(DLookup("CustomerID", "MarkedRow", "FromForm ='frmB'"), 0)
    
    Me.Recordset.FindFirst "CustomerID='" & strCriteria & "'"
    
End Sub

Private Sub Form_Current()
    Me.txtBlank = Me.CustomerID
        
End Sub

Simply, I am using the “Form_Unload” event to store the value of the current form row in a table.

VBA Variable Scope

As you may or may not know, that state of the variables on a form are for the time the the form is open. So I have to store the values I want to keep in a table if I need the program to remember where any variables.

It’s kind of like storing program information for websites in “cookies”.

Then I read these variables from the table when the form opens on the Form_Open event.

Here is the database for an example:

RowHighlightAndMarkRow.accdb

Let me know if you have any questions.

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!