Solved

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

Posted on 2016-10-21
19
36 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
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 49

Assisted Solution

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

/gustav
1
 
LVL 119

Expert Comment

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

Expert Comment

by:Rey Obrero
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 119

Expert Comment

by:Rey Obrero
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 34

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 119

Accepted Solution

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

867 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

20 Experts available now in Live!

Get 1:1 Help Now