8.9. Common VBA Techniques

Every Access developer will face some common VBA challenges at some point. There are simple and easy ways to handle drilling down to detail records, date math, rounding issues, and tricky string concatenation problems.

8.9.1. Drilling Down with Double-Click

It's a good design practice to use read-only continuous forms to display multiple records and then allow your user to drill down to the detail of a single selected record. This action should have a button at the bottom of the form (called Detail, for example) that opens the detail form for the currently selected record.

For convenience and to comply with Windows standards, it's also good to allow the user to drill down using double-click. Because you already have code behind the Detail button that opens the detail form, you can easily reuse that code:

Private Sub cmdDetail_Click()
On Error GoTo Error_Handler
    Dim stLinkCriteria As String
    If IsNull(Me!BusinessKey) Then
        GoTo Exit_Procedure
    End If

    gstrCallingForm = Me.Name
    stLinkCriteria = "[BusinessKey]=" & Me![BusinessKey]
    DoCmd.OpenForm FormName:="frmBusiness", _
    Me.Visible = False

    On Error Resume Next
    Exit Sub
    DisplayUnexpectedError Err.Number, Err.Description
    Resume Exit_Procedure

End Sub

Because this code is already written and tested, you only need to call it by name (cmdDetail_Click) when the user double-clicks a record. This is quite simple to do: you ...

Get Access™ 2007 VBA Programmer's Reference now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.