VBA code that would select a specific record in a datasheet view form

place this code in the click event of a button

private sub button_click()
with me.subformcontrolname.recordsourceclone
    .findfirst "id_activite = 6310"
    if not .nomatch then
    me.subformcontrolname.bookmark=.bookmark
    end if
end with
end sub

or this

private sub button_click()
with me.subformcontrolname.form.recordsourceclone
    .findfirst "id_activite = 6310"
    if not .nomatch then
    me.subformcontrolname.form.bookmark=.bookmark
    end if
end with
end sub

Hi Rey!
Thank you very much for your answer!

When I run my version of your code, I get the following error:

error 438 "object doesn't support this property or method

Here's my code:

Private Sub Détail_Click()

With Forms![Formation]![TableActivités sous-formulaire].Form.[id_activite].recordsourceclone
.FindFirst "id_activite = 6310"
    If Not .NoMatch Then
    Forms![Formation]![TableActivités sous-formulaire].Form.[id_activite].Bookmark = .Bookmark
    End If
End With

End Sub

Would you know what's wrong?

Thanks again!

Francis
Erreur.png

try this

Private Sub Détail_Click()

 With Forms![Formation]![TableActivités sous-formulaire].Form.recordsourceclone
 .FindFirst "id_activite = 6310"
     If Not .NoMatch Then
     Forms![Formation]![TableActivités sous-formulaire].Form.Bookmark = .Bookmark
     End If
 End With

 End Sub


or this


Private Sub Détail_Click()

 With Forms![Formation]![TableActivités sous-formulaire].recordsourceclone
 .FindFirst "id_activite = 6310"
     If Not .NoMatch Then
     Forms![Formation]![TableActivités sous-formulaire].Bookmark = .Bookmark
     End If
 End With

 End Sub

try this, change recordsourceclone with recordsetclone

 Private Sub Détail_Click()

  With Forms![Formation]![TableActivités sous-formulaire].Form.recordsetclone
  .FindFirst "id_activite = 6310"
      If Not .NoMatch Then
      Forms![Formation]![TableActivités sous-formulaire].Form.Bookmark = .Bookmark
      End If
  End With

  End Sub


 or this


 Private Sub Détail_Click()

  With Forms![Formation]![TableActivités sous-formulaire].recordsetclone
  .FindFirst "id_activite = 6310"
      If Not .NoMatch Then
      Forms![Formation]![TableActivités sous-formulaire].Bookmark = .Bookmark
      End If
  End With

  End Sub

That's working like hell!!!  Thank you very much Caballero Obrero!

Rey ... What happens when the id_activite changes???

Shouldn't this line be ...

 With Forms![Formation]![TableActivités sous-formulaire].recordsetclone
  .FindFirst "id_activite =" & Me.id_activite


ET

Good question Eric.  That was going to be my next question!

This should fix it ...

 With Forms![Formation]![TableActivités sous-formulaire].recordsetclone
  .FindFirst "id_activite =" & Me.id_activite

ET

Unfortunately not.  I get this error:

Error 3077 syntax error missing operator in expression

I also tried putting the quote mark at the end, but it's returning the same error.

What is the Control Name that id_activite is bound to???

What Control are you using to specify which id_activite that you want selected???

ET

What is the Control Name that id_activite is bound to??? Forms![Formation]![TableActivités sous-formulaire].id_activite

What Control are you using to specify which id_activite that you want selected??? Me.id_activite

you need a combo box on the main form to select the "id_activite", then refer to it as

 .FindFirst "id_activite =" & Me.cboid_activite

You need a control on your form to determine which id_activite you want to select.

ET

I'm confused.  If you are clicking on the record which selects it, why are people offering suggestions to use the recordsetclone to find it?

Is the real question that you want to do something with the selected record?  If so, I would use the double-click event rather than the click event.  People frequently click in a control as they are scrolling and you don't necessarialy want that to activate your code.

So, perhaps you want to open a detail form to show the record selected from the list form.

In the double-click event of the date field or the description field (or whatever you think makes sense), add the code to open the popup form.

DoCmd.OpenForm "yourformname", acNormal,,"d_activite =" & Me.d_activite,,acDialog

This will open the specified form to the record identified by d_activite

When I add this type of code to the double-click event of a control, I usually set its DisplayAsHyperlink property to Always which gives people a visual clue that something will happen if they double-click on the control.

The thing is I'm using a 1st subform to add records to a table which is queried by a 2nd subform which I need to refresh in order to see that the record from the 1st form has well been added.  The 1st subform is used to add records, but is also used to view records, according to what is selected in the 2nd subform.  So as I just said, after a record is added from the 1st subform, a refresh is performed on 2nd form. Then the selector of the 2nd subform goes back to the first record according to the sort order of the datasheet.  That causes the information displayed in the 1st form to be different from what was just added from the 1st form.  What I want is that, no matter how the second form is sorted, the record that has just been added remain viewable in the 1st subform.  Please see example.
Example.png

Hello Rey! : )

So here's what I did.  All you wrote was perfect, but for an unexplainable reason, I had to add a delay before the record selection is performed.

Private Sub Détail_Click()

Dim CurrentSelector As Long
 CurrentSelector = Me.id_activite.Value
 
'Pour enregistrer
SendKeys "+{ENTER}"
SendKeys "+{F9}"
SendKeys "{F9}"

'To leave time before the selection is made, if not it's not working!
Dim time1, time2

time1 = Now
time2 = Now + TimeValue("0:00:01")
    Do Until time1 >= time2
        DoEvents
        time1 = Now()
    Loop

With Forms![Formation]![TableActivités sous-formulaire].Form.RecordsetClone
.FindFirst "id_activite=" & CurrentSelector
      If Not .NoMatch Then
      Forms![Formation]![TableActivités sous-formulaire].Form.Bookmark = .Bookmark
      End If
  End With
 
End Sub

Thank very much!  Your help was really really appreciated on this!

Francis

This forum is very efficient.  Thank you all for your quick help on this matter.