Solved

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

Posted on 2016-10-21
19
32 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
  • 8
  • 5
  • 4
  • +2
19 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
Comment Utility
It is: RecordsetClone

/gustav
1
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
That's working like hell!!!  Thank you very much Caballero Obrero!
0
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
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
Comment Utility
Good question Eric.  That was going to be my next question!
0
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
This should fix it ...

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

ET
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:Francis Larocque
Comment Utility
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
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
You need a control on your form to determine which id_activite you want to select.

ET
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
This forum is very efficient.  Thank you all for your quick help on this matter.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now