Making a form select the first record in ADO recordset

Which object and which method do I use to code a form to select the first available record in an ADO recordset?
9XqUwH3SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tbsgadiCommented:
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
9XqUwH3SAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

9XqUwH3SAuthor Commented:
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
9XqUwH3SAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
tbsgadiCommented:
Try
DoCmd.RunCommand acCmdRecordsGoToFirst 'Probably don't need this first row
        DoCmd.RunCommand acCmdSelectRecord
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
9XqUwH3SAuthor Commented:
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
9XqUwH3SAuthor Commented:
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
tbsgadiCommented:
You can use VBA commands both with DAO and ADO
0
tbsgadiCommented:
Are you putting the code in the Form_Load?
0
9XqUwH3SAuthor Commented:
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
tbsgadiCommented:
Where are you putting the code?
0
9XqUwH3SAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Set the Me.Recordset BEFORE using the DoCmd lines
0
9XqUwH3SAuthor Commented:
$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
tbsgadiCommented:
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
9XqUwH3SAuthor Commented:
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
tbsgadiCommented:
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
9XqUwH3SAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.