?
Solved

Making a form select the first record in ADO recordset

Posted on 2010-01-09
20
Medium Priority
?
427 Views
Last Modified: 2013-12-05
Which object and which method do I use to code a form to select the first available record in an ADO recordset?
0
Comment
Question by:9XqUwH3S
[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
  • 10
  • 7
  • 3
20 Comments
 
LVL 46

Expert Comment

by:tbsgadi
ID: 26276525
0
 
LVL 85
ID: 26277564
How are you using the ADO Recordset? In general, ADO will "set" the recordset at the first record - you shouldn't have to do anything else to it. Note this is dependent on any fields you specify in your ORDER BY clause, if you use one. If not, it will set the order based on your Primary Key index (assuming you have one).

Are you "setting" the .Recordset property of your Form to an ADO recordset you've built?
0
 

Author Comment

by:9XqUwH3S
ID: 26278335
Nice website you have, LSM Consulting.

tbsqadi: I'm  not sure how cloning applies, but I'm trying to. Can you clarify what about the Cloning concept applies? I attached an image below to show what I'm looking to achieve.

LSM Consulting, I have set the form's recordset property to an ADO recordset (if I am understanding correctly). I show that code below for reference only.

My goal is to see the recordset's first record set on focus when the form opens. With this form, the record becomes highlighted when it is selected.

I didn't see the record 'highlighted' when the form opens, currently, (using ADO coding only), so I'm thinking I need to add something to the code to make that happen.

When the form opens, it does not 'select' the record as shown in the picture. I want to fix that.

PS If there is a delay in responding, I'm driving a semi at night and trying to teach myself developing whenever I can and hoping iy amounts to something in the workplace, someday.

Sometimes, I'm away.


Sincerely,
Steven



Private Sub cmdAllRecords_Click()

    'Declare and instantiate a recordset

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    Dim rec As Object
    
    'Establish the Connection, Cursor Type, and
    'Lock Type and open the recordset
    
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenKeyset
    rst.CursorLocation = adUseClient
    rst.LockType = adLockOptimistic
    rst.Open "Select * from tblMain", Options:=adCmdText
    rst.Sort = "[ReminderDate]"
 
   
    'Set the form's recordset to the recordset just created
    
  
    Set Me.Recordset = rst


    
End Sub

Open in new window

Capture.PNG
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:9XqUwH3S
ID: 26278429
Dear LSM Consulting,

I sent you some feedback on your website.

The subject heading reads, "for LSM".

I hope you find it useful to you.

Thank you.

Steven
0
 

Author Comment

by:9XqUwH3S
ID: 26303229
If I want the record to be highlighted when the form automatically opens to the first record, I would use VBA to set the property of the current record to highlight it, then, it sounds like.

Can I ask which property I would set which would control the highlighting of the record

OR

how would I use ADO to change the setfocus property to the first record (Yes, the form does load with the first record showing.)?
0
 
LVL 85
ID: 26303305
I'm not sure ... you could try using SetFocus to one of the Controls on the form. The first record would already be selected in the Recordset.
0
 
LVL 46

Accepted Solution

by:
tbsgadi earned 1336 total points
ID: 26303416
Try
DoCmd.RunCommand acCmdRecordsGoToFirst 'Probably don't need this first row
        DoCmd.RunCommand acCmdSelectRecord
0
 

Author Comment

by:9XqUwH3S
ID: 26333337
I think I should look through the books on ADO programming at Borders and sift through the pages of example code.

I'm away from home and it may be a while before I can move forward on this question. I will keep this thread alive by making an occasional comment until I do.

I Googled online and found nothing that comes close to this.

0
 

Author Comment

by:9XqUwH3S
ID: 26348582
tsqadi,
RE: DoCmd.RunCommand acCmdRecordsGoToFirst 'Probably don't need this first row
        DoCmd.RunCommand acCmdSelectRecord
Both suggestions didn't seem to work for me, so I want to ask, can I use a VBA command against an ADO recordset? I didn't think I could, but if it IS possible, I'll look further into your suggestion.


0
 
LVL 46

Assisted Solution

by:tbsgadi
tbsgadi earned 1336 total points
ID: 26348646
You can use VBA commands both with DAO and ADO
0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 26348681
Are you putting the code in the Form_Load?
0
 

Author Comment

by:9XqUwH3S
ID: 26348863
Okay, I was using a split form. Even without:
       DoCmd.RunCommand acCmdRecordsGoToFirst 'Probably don't need this first row
       DoCmd.RunCommand acCmdSelectRecord
The conditional format highlighting works.
It appears that the first record focus doesn't appear as might be expected.
Do you think this is an Access 2007(.accdb) limitation?
If not, where might you suggest I check next, or which approach comes to your mind?
0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 26348893
Where are you putting the code?
0
 

Author Comment

by:9XqUwH3S
ID: 26348955
Just saw your credentials because you sounded like a professor.

Wow. Raw intellectual horsepower.

I put the code you gave me with the recordset query (in ADO), if I'm saying that correctly.

See attached code Sub if it is helpful.

Is there a better place to place it?

(I was going to close the question out because this is above my current 'skill set' but a moderator urged me to continue.)
Private Sub cmdAllRecords_Click()

    'Declare and instantiate a recordset

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    Dim rec As Object
    
    'Establish the Connection, Cursor Type, and
    'Lock Type and open the recordset
    
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenKeyset
    rst.CursorLocation = adUseClient
    rst.LockType = adLockOptimistic
    rst.Open "Select * from tblMain", Options:=adCmdText
    rst.Sort = "[ReminderDate]"
    
    DoCmd.RunCommand acCmdRecordsGoToFirst 'Probably don't need this first row
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.GoToRecord acDataForm, "frmMain", acGoTo, 1

    'Set the form's recordset to the recordset just created
      
    Set Me.Recordset = rst
    
End Sub

Open in new window

0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 664 total points
ID: 26348970
Set the Me.Recordset BEFORE using the DoCmd lines
0
 

Author Closing Comment

by:9XqUwH3S
ID: 31675062
$1 for the code and $1 million dollars for knowing where to place it!!!

Oh I love this site and walking in the shadows of greatness.

A cuban cigar is in order (but not the way some people use it, okay?)

Thanks, fellas!
0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 26349093
Gee thanks!

You're making life a bit difficult, for access all you need to do is set the Record source of the form to "tblMain" with a Sortby "ReminderDate"
No code is really needed
0
 

Author Comment

by:9XqUwH3S
ID: 26349350
That tested to be true as you said.
So all I needed to do was establish the connection and use SQL after that.
Advice taken.
0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 26349587
Even easier than that..You can create an access Query & then you don't need to write any code.
I suggest you look at some tutorials.
Just search experts Exchange or google "MS access Tutorials"
0
 

Author Comment

by:9XqUwH3S
ID: 26356907
Okay, but I was trying out the new technology because it seemed quicker and sleeker and I wanted to see if I could make it work.
And with your gracious help, I did!
Thank you tbsqadi! Your experience and effort helped bring a sense of accomplishment to my life.
:  )
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

752 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