Access™ 2007 VBA Programmer's Reference

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
        EnableDisableControls
        GoTo Exit_Procedure
    End If

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

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

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 ...