Double Click event isn't working properly

[Chateauk]

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

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

 

[Gasman]

Set focus to a control in the mainform after setting focus to the mainform?
In fact from this no need to set the form first according to this link?

Transferring Focus Between a Main Form and its Subform: Microsoft Access Tips and Tricks from DataGnostics

 

[Pat Hartman]

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]

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]

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]

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]

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.

 

[Pat Hartman]

You're welcome