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
Else
Cancel = True
End If
DoCmd.OpenForm FormName:=”frmContacts”, _
view:=acNormal, _
windowmode:=acWindowNormal
Set frm = Forms![frmContacts]
frm.FilterOn = True
frm.Filter = strFilter
DoCmd.Close acForm, Me.Name
End Sub
