Double Click event isn't working properly (1 Viewer)

Chateauk

New member
Local time
Yesterday, 23:06
Joined
Nov 20, 2023
Messages
7
I need help figuring out what I'm doing wrong here.
I've created a form called frmAnalystsReviews( Main form on the right) and added a subform called qryMonthlyReviewsSUBFORM (on the left). The subform is just a continuous form of the frmAnalystReviews form. Users will use the single form for data entry. Both forms are connected by the field called IPIP. Under a specific IPID, we can find multiple records. Each record has a unique LOADID. Users want to be able to double click on any record on the subform, and have the single form points to the corresponding LOADID and keep that LOADID selected or highlighted. Currently, the tool is doing that except that the cursor is going back to the first row of the subform list. I'm unable to create the procedure at the subform detail level, I've created that for each control of the subform. Attached are both procedure and screenshot of the forms. I hope my explanation makes. If not feel free to ask questions.
Thank you
Code:
Private Sub Acct_Nbr_DblClick(Cancel As Integer)

    Dim LoadIDValue As Variant
    Dim rs As Recordset

    ' Get the LoadID value from the subform
    LoadIDValue = Me.LoadID

    ' Set the recordset of the "frmAnalystReviews" form
    Set rs = Forms("frmAnalystReviews").RecordsetClone

    ' Find the matching record based on LoadID in the recordset
    rs.FindFirst "[LoadID] = " & LoadIDValue

    ' Save the current record's Bookmark
    Dim currentBookmark As Variant
    currentBookmark = rs.Bookmark

    ' Set the focus to the "frmAnalystReviews" form
    Forms("frmAnalystReviews").SetFocus

    ' Restore the cursor to the LoadID of the double-clicked record
    Forms("frmAnalystReviews").Recordset.Bookmark = currentBookmark

End Sub
 

Attachments

  • Screenshot_20231129_123650_Gmail.jpg
    Screenshot_20231129_123650_Gmail.jpg
    435.5 KB · Views: 48
Last edited by a moderator:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:06
Joined
Feb 19, 2002
Messages
43,587
Let me explain my method. It is simpler than what you are doing and also more efficient.
1. Define a hidden, unbound control on the main form. Name it FindIPID
2. Change the recordsource query of the main form to add a where clause --- Where IPID = Forms!mymainform!FindIPID
3. In the current event of each of the three subforms add these two lines of code:
Me.Parent!FindIPID = Me.IPID
Me.Parent.Requery

The mainform will open to an empty record. pick a search form to use, click on a row in the search form to make it current.

I may not have used the correct control names so please correct them if necessary.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:06
Joined
Oct 29, 2018
Messages
21,581
Hi @Chateauk Welcome to AWF!

Just curious, did you say you linked the subform to the main form using the Link Master and Child Fields properties? If you did, try unlinking them just to see if that makes any difference.
 

Chateauk

New member
Local time
Yesterday, 23:06
Joined
Nov 20, 2023
Messages
7
Hi @Chateauk Welcome to AWF!

Just curious, did you say you linked the subform to the main form using the Link Master and Child Fields properties? If you did, try unlinking them just to see if that makes any difference.
Your option seems to be working but it's pulling all IPID in the database to the subform. I want the subform to have only the records for the corresponding IPID to appear. Is it doable?
 

GPGeorge

George Hepworth
Local time
Yesterday, 21:06
Joined
Nov 25, 2004
Messages
2,042
Your option seems to be working but it's pulling all IPID in the database to the subform. I want the subform to have only the records for the corresponding IPID to appear. Is it doable?
Post #3 explains one way to do it.
 

Chateauk

New member
Local time
Yesterday, 23:06
Joined
Nov 20, 2023
Messages
7
Let me explain my method. It is simpler than what you are doing and also more efficient.
1. Define a hidden, unbound control on the main form. Name it FindIPID
2. Change the recordsource query of the main form to add a where clause --- Where IPID = Forms!mymainform!FindIPID
3. In the current event of each of the three subforms add these two lines of code:
Me.Parent!FindIPID = Me.IPID
Me.Parent.Requery

The mainform will open to an empty record. pick a search form to use, click on a row in the search form to make it current.

I may not have used the correct control names so please correct them if necessary.
Thank you Pat. Your solution works great.
 

Users who are viewing this thread

Top Bottom