Datasheet subform to Select Main Form Record -Datasheet losing focus after selection

[Carouser]

It's actually a Main form with 2 nested subforms. Selecting the datasheet record subform3 selects the corresponding record in the Form of subform2. After clicking on the datasheet to select the record, the focus automatically goes to the first record. I can click on the same datasheet record to regain the focus. I've been searching, but this activity is not very common. I just want to click on the datasheet record and have it stay focused on that record. The code I have used for the on_click of subform3 is:

Private Sub Form_Click()


     Dim rs As Object
    
     Set rs = Forms!frmSPK.frmSPKSF.Form.Recordset.Clone
        rs.FindFirst "ID=" & Me.ID
           Forms!frmSPK.frmSPKSF.Form.Bookmark = rs.Bookmark
     

End Sub

[June7]

Don't understand. Is this a form/subform/subsubform arrangement? Or two subforms on main form? How can a subform dictate main form? Are Master/Child links properties set?

Want to provide db for analysis? Follow instructions at bottom of my post.

[Carouser]

It is a form/subform/subsubform arrangement.

Subform Master- Spk_ID Links to SubSubform Child- Spk_ID. Subform (Single Form View) and Subsubform (DataSheet View). The individual records are in the ID field and I am able to select a row in the datasheet and the selected record will be displayed in the Subform Single form view. This is all working. Subsubform dictates Subform. The annoying issue is that after the subform has the new selected record focus, the data sheet defaults to the first record rather than remaining on the record I selected. If I click the same record in the datasheet again, then that selection will have the focus. I just want to have a one click solution and don't know how to get the datasheet to refocus to the subform record.

The Subform is used to add new records and a variety of tasks including renaming and sending files (that you may be familiar with) for each record.

[June7]

Oh So the subform and subsubform have the same data source and you a trying to emulate a split form?

Seems I had a similar issue. Requery of subform was setting focus on first record instead of staying on the selected record. I changed to Refresh and that no longer happened.

However, I don't have anything setup like yours. If you want to provide db so I can test I will see what I can do.

[Carouser]

I've made a simple mockup for demo purposes. Just use the frmSF and select a record in the datasheet. The on_click event of frmSFSF has the rs cloning code. Select a datasheet record, wait about 1 second and select it again and it will have focus in datasheet, even if you set the focus outside of the datasheet. It was s a challenge to get the file size down to 500kb.

[Carouser]

I have found a work around that is producing promising results. I placed my datasheet on the main form and disabled the parent child links. In the record source of the datasheet Query Builder, in the SPK_ID field criteria, added:

Like Forms!frmMain!frmSubform.Form.SPK_ID

I am able to select datasheet record and the selected record will remain highlighted even when moving to the Subform. I just have to find the right method of requery/refresh to update the datasheet when changes are made to the subform and hopefully retain the datasheet selected record focus.

[June7]

Try this:

Private Sub Form_Click()
With Forms!frmSF.Form.Recordset.Clone
    .FindFirst "ID=" & Me.ID
    Forms!frmSF.Form.Bookmark = .Bookmark
    Me.Bookmark = .Bookmark
End With
End Sub

I left the Master/Child links properties set.

Why is there a combobox and textbox both bound to ID_DaySessions?

[Carouser]

Everything seems to work. Attached is a far better example of the whole process with content working. I have rejoined the Parent /child fields of second subform Even adding a new record updates the second subform. I inserted your code June7, not sure if it is doing anything.

The sessions combo is to add/or edit the current record. In my real db, i have 3 cascading combos which gives the combo ID.

Just open frmMain.

[June7]

The click events work, but error when you click NEW record. Try:

If Me.ID & "" <> "" Then
...
End If