Provided by Allen Browne. Last updated: February 2008.
When a form is opened, you may like to automatically load the most recently edited record. To do so:
As an example, take a form that has CustomerID as the primary key field.
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.
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
Set the form's On Load property to [Event Procedure], and add the following code. It performs these steps:
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
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'")
|Home||Index of tips||Top|