[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2016-10-21
19
Medium Priority
?
64 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 52

Assisted Solution

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

/gustav
1
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
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 40

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 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

831 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