double click event to open form where

[johnlute]

I have a main form with a listbox that displays primary keys that are
related to the main form's primary key. The list box has a double
click event that re-opens the main form and navigates to its record:

Private Sub lstProfilesAssocs_DblClick(Cancel As Integer)
DoCmd.OpenForm "KEYSTONEqryrpt", acNormal, _
WhereCondition:="txtProfileID='" & Me!lstProfilesAssocs.Column
(0) & "'"

End Sub

The problem with this is that it filters the main form to the one
record. How can I change this so that it navigates to the desired
record but NOT filter everything else out?

Thanks!

 

[Dale Fye]

Instead of reopening the form, just reset the record pointer.

Private Sub lstProfilesAssocs_DblClick(Cancel as Integer)

Dim rs as dao.recordset

set rs = me.recordsetclone
rs.findfirst "[txtProfileID] = " & me.lstProfilesAssocs
if rs.nomatch then
msgbox "Unable to find record"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing

end Sub

 

[Jack Leach]

As you see, the Where clause effects the recordset of the opening form. What
you want to do is pass the record ID via OpenArgs when opening, and read that
ID in the Open event of the form, directing the form to go to that record.

Ex: (aircode)

(from the caller)
DoCmd.OpenForm "KEYSTONEqryrpt", acNormal, _
OpenArgs: Me!lstProfilesAssocs.Column(0)


and in the form being opened:

Private Sub Form_Open()
If Len(Nz(Me.OpenArgs, "")) <> 0 Then
Call psGoToRecord
End If
End Sub

Private Sub psGoToRecord()
With Me.RecordsetClone
.FindFirst "[IDField] = """ & Me.OpenArgs & """"
If .NoMatch Then
MsgBox "Record Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub

add error handling, etc.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[johnlute]

Thanks Dale and Jack! I tried Dale's approach and tweaked it a bit.
Thanks for the great idea!

Private Sub lstProfilesAssocs_DblClick(Cancel As Integer)
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "txtProfileID='" & Me.lstProfilesAssocs.Column(0) &
"'"

If rs.NoMatch Then
MsgBox "Unable to find record!"
Else
Me.Bookmark = rs.Bookmark
End If

rs.Close
Set rs = Nothing

End Sub

Instead of reopening the form, just reset the record pointer.

Private Sub lstProfilesAssocs_DblClick(Cancel as Integer)

    Dim rs as dao.recordset

    set rs = me.recordsetclone
    rs.findfirst "[txtProfileID] = " & me.lstProfilesAssocs
    if rs.nomatch then
        msgbox "Unable to find record"
    else
        me.bookmark = rs.bookmark
    endif

    rs.close
    set rs = nothing

end Sub

----
HTH
Dale

I have a main form with a listbox that displays primary keys that are
related to the main form's primary key. The list box has a double
click event that re-opens the main form and navigates to its record:

Private Sub lstProfilesAssocs_DblClick(Cancel As Integer)
        DoCmd.OpenForm "KEYSTONEqryrpt", acNormal, _
        WhereCondition:="txtProfileID='" & Me!lstProfilesAssocs.Column
(0) & "'"

End Sub

The problem with this is that it filters the main form to the one
record. How can I change this so that it navigates to the desired
record but NOT filter everything else out?

Thanks!- Hide quoted text -

- Show quoted text -