Solved

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

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

Assisted Solution

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

/gustav
1
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 35

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
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…

809 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