Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 779
  • Last Modified:

ADO If .find record then display the fields

In DAO code the following code finds the record and displays the fields selected on the form..
I have ADO code that I think is almost working but it doesn't fill out the fields like I expect.
With ADO do I need to set each field to a value or is there DAO equivalent code that populates the fields on the form.
Thanks
ADO Code - See attached.
DAO Code
    Set db = CurrentDb()
    Set rs = Me.RecordsetClone
    rs.FindFirst "[WorkReleaseId] = " & cboReleaseNo.Value
        If rs1.NoMatch Then
            MsgBox "Not Found"
        Else
            Me.Bookmark = rs1.Bookmark
        End If
   'rs.Close
ADO
Set cn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cn.Open "Provider=SQLNCLI;Server=10.0.0.10\SQLExpress;Database=FDThomasQC;Trusted_Connection=yes;"
rs1.Open "SELECT WorkReleaseID FROM tblWorkRelease WHERE WorkReleaseID = " & cboReleaseNo, cn, adOpenKeyset
    rs1.MoveFirst
    rs1.Find "[WorkReleaseId] = " & cboReleaseNo.Value
        If rs1.EOF Then
            MsgBox "Not Found"
        Else
            'HOW DO I POPULATE FORM FIELDS HERE?
        End If

Open in new window

0
BobRosas
Asked:
BobRosas
  • 4
  • 3
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Set cn = New ADODB.Connection
Set rs1 = New ADODB.Recordset

cn.Open "Provider=SQLNCLI;Server=10.0.0.10\SQLExpress;Database=FDThomasQC;Trusted_Connection=yes;"

rs1.Open "SELECT WorkReleaseID FROM tblWorkRelease WHERE WorkReleaseID = " & cboReleaseNo, cn, adOpenKeyset

If Not (rs1.EOF and rs1.BOF) then
  '/you found a record
Else
  '/you did NOT find a record
End IF
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I think I misunderstood, although the "search" I show is more efficient than yours.

You cannot set a bookmark in the same manner as you're doing with DAO. Are you using a bound form? If you are, then you can set the .Recordset value of your form to your rs! variable, if you set the CursorLocation to adUseClient.

0
 
BobRosasAuthor Commented:
Thank you for your quick response.  
How to fill the form if I find something is what I'd like to know more about.  Could you be more specific using code on how I set the recordset value?

If Not (rs1.EOF and rs1.BOF) then
  Set rs1 = Me.Recordset  'GET TYPE MISMATCH ERROR
Else
  '/you did NOT find a record
End IF

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You've got it backwards:

Set Me.Recordset = rs1

However, you must use the Clientside cursor ... before you open your recordset do this:

rs1.CursorLocation = adUseClient
0
 
BobRosasAuthor Commented:
Obviously I still don't have something right because I get the message ...
                Operation is not allowed when the object is open.
but here's what I have.
Thanks again.

       If Not (rs1.EOF And rs1.BOF) Then
            rs1.CursorLocation = adUseClient
            Set Me.Recordset = rs1
        Else
            '/you did NOT find a record
        End If
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As I wrote earlier, you must set the CursorLocation BEFORE you open the Recordset ... not in the If block.
0
 
BobRosasAuthor Commented:
You were a huge help so I increased points.
Thanks again!
0
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now