Recordset Clone in Access2003 ADP connected to SQL 2005

I am simply trying to have a the result selected from a Combo box post to this form. I had it working in an mdb (see mdb code ) and tried to translate it to ADO (see adp code) however it is not quite right.

mdb Code
Private Sub cboCompany1_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Nz(Me![cboCompany1], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 
ADP Code
 
Private Sub cboCompany1_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset
    rs.MoveFirst
    rs.Find "[ID] = " & Str(Nz(Me![cboCompany1], 0))
    If rs.EOF Then Exit Sub
    
    Me.Bookmark = rs.Bookmark
End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:

Select all Open in new window

Instant Access to this Solution

Plus...

30 Day FREE access, no risk, no obligation

Collaborate with the world's top tech experts

Unlimited access to our exclusive solution database

Never be left without tech help again

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

"The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.

"Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin

"Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

In what way doesn't it work? i.e. What error if there's an error or what behaviour is manifested?

The code you're using isn't exactly the wizard generated code in an ADP.
The most crucial difference is that you're refering to the form's Recordset property. You're not creating a clone or working with the form's own clone.

What you would normally have instead would be

Private Sub cboCompany1_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Close '<<<<< ---- This line here
rs.Find "[ID] = " & Str(Nz(Me![cboCompany1], 0))
If rs.EOF Then Exit Sub
Me.Bookmark = rs.Bookmark
End Sub

This makes it a bit closer to the MDB created equivalent.
The reason for this is that a new Clone (which is what's created by the .Clone method) will be distinct from the form's own recordset. It will be position on the first row and the Find method will search from there. (The default should be the Current Row - but in a form's own recordset - you may or may not see consistent behaviour). The MoveFirst should guarantee that you code worked - when you're working with the form's recordset as you are then the Bookmark line is unnecessary.
i.e. this should still have worked for you

Private Sub cboCompany1_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset
rs.MoveFirst
rs.Find "[ID] = " & Str(Nz(Me![cboCompany1], 0))
End Sub

or even

Private Sub cboCompany1_AfterUpdate()
Me.Recordset.Find "[ID] = " & Str(Nz(Me![cboCompany1], 0)), 0, adSearchForward, adBookmarkFirst
End Sub

The rest of the code is wizard standard, including the presupposition that "0" isn't a viable value in your records selection. (It's an Autonumber/Identity assumption that you'll have started from 1 and are unlikely to have come back to 0!)

If something specific is wrong then certainly spell it out.

Cheers.

Question

Recordset Clone in Access2003 ADP connected to SQL 2005

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Microsoft ADP

Microsoft Access Database

Access Forms

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press