Solved

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

Posted on 2016-10-21
19
48 Views
Last Modified: 2016-10-21
Hello all,
I'm looking for a line of code that would make it possible for me to select a specific record in a datasheet view form based upon a field criteria.  More specifically, I would like that the selected record be the one that has id_activite = 6310.  I do not want to play with datasheet sorting or anything.  Is there any way to perform this simple operation?
Thank you.
Francis
Extrait.png
0
Comment
Question by:Francis Larocque
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
  • +2
19 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41854010
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
0
 

Author Comment

by:Francis Larocque
ID: 41854040
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
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 41854052
It is: RecordsetClone

/gustav
1
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41854054
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41854057
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
1
 

Author Comment

by:Francis Larocque
ID: 41854137
That's working like hell!!!  Thank you very much Caballero Obrero!
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41854179
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
1
 

Author Comment

by:Francis Larocque
ID: 41854198
Good question Eric.  That was going to be my next question!
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41854214
This should fix it ...

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

ET
0
 

Author Comment

by:Francis Larocque
ID: 41854226
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.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41854236
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
0
 

Author Comment

by:Francis Larocque
ID: 41854267
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41854269
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
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41854311
You need a control on your form to determine which id_activite you want to select.

ET
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41854381
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.
0
 

Author Comment

by:Francis Larocque
ID: 41854411
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
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 41854428
so you want the two subform to be on the same record

- before refreshing/requery of the 2nd subform, save the  "id_activite" to a tempVars variable
 Tempvars!MyID=me.id_activite.value

- after you refresh/requery the 2nd subform

do a .findfirst on the 2nd subform to go back  to the record that was previously selected, using the code posted above, but using the Tempvars!MyID




 Private Sub Détail_Click()

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

       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 = " & Tempvars!MyID

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

   End Sub
1
 

Author Comment

by:Francis Larocque
ID: 41854463
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
0
 

Author Closing Comment

by:Francis Larocque
ID: 41854471
This forum is very efficient.  Thank you all for your quick help on this matter.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question