How to open a form record from a listbox selection.
Q: Martin Wichmand is trying to open a form to a specific record from the DblClick event of a listbox, using the ID primary key, but he can’t make it work with the following procedure:
Private Sub lstMandag_DblClick(Cancel As Integer)
DoCmd.OpenForm “Sagsstyring”, , , “ID”
End Sub
A: You need to pick up the ID value from the listbox, as below (note that listbox and combo box columns are zero-based, so Column 0 is the first column):
Private Sub lstContacts_DblClick(Cancel As Integer)
Dim lngID As Long
Dim prj As Object
Dim frm As Access.Form
Dim strSearch As String
lngID = Nz(Me![lstContacts].Column(0))
If lngID = 0 Then
MsgBox “Please select an ID”, vbCritical + vbOKOnly
GoTo ErrorHandlerExit
Else
Set prj = Application.CurrentProject
If prj.AllForms(“frmContacts”).IsLoaded = False Then
DoCmd.OpenForm FormName:=”frmContacts”, windowmode:=acWindowNormal
End If
Set frm = Forms![frmContacts]
strSearch = “[ContactID] = ” & lngID
Debug.Print “Search string: ” & strSearch
‘Find the record on the Contacts form that matches the selection in the listbox
frm.RecordsetClone.FindFirst strSearch
frm.Bookmark = frm.RecordsetClone.Bookmark
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
