Opening a Form from a Listbox

How to open a form when making a selection from a listbox on another form.

Q:  Martin wrote to ask if I could help with code to open a form to a specific record when the user makes a selection from a listbox on another form.

A:  This is easy to do, at least if you have a unique ID to work with.  Assuming that you have a table of contacts with an AutoNumber ContactID field, and the listbox row source includes ContactID (as well as contact name or other identifying information), the following code will open frmContacts to the selected contact record when the user double-clicks an item in the listbox:

Private Sub lstContacts_DblClick(Cancel As Integer)


   Dim lngID As Long

   Dim strFilter As String

   Dim frm As Access.Form


   lngID = Nz(Me![lstContacts].Column(0))

   If lngID > 0 Then

      strFilter = “[ContactID] = ” & lngID

      Debug.Print “Filter: ” & strFilter


      Cancel = True

   End If


   DoCmd.OpenForm FormName:=”frmContacts”, _

      view:=acNormal, _


   Set frm = Forms![frmContacts]

   frm.FilterOn = True

   frm.Filter = strFilter

   DoCmd.Close acForm, Me.Name


End Sub