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:
Let me know if you have any questions.