IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, email@example.com 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|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Return to the same record next time form is opened'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...