Recordset.Clone Problem Since Moving To ADP 1

[DomDom3]

Hi there,

I used the code below on an access form (mdb database) and it worked very well. I've since moved the back end to SQL Server and the code no longer works. The code is on On Enter, the status bar on the form says its calculating but the record is not found.

Does anyone know why this would stop working when moved to an adp?

Dim rs As Object
'Searches for ID entered into ID Search textbox
' Set rs = Me.Recordset.Clone
Set rs = Me.Recordset.Clone
rs.FindFirst "[unique ID] = " & Str(Nz([sfrmDataEntrySummaryView]![Outlook ID], 0))
If rs.NoMatch Then
msgbox "No Project Found", vbOKOnly, "Installation Outlook"
Else
Me.Bookmark = rs.Bookmark


Thanks

 

[danvlas]

Try:

    Set rs = Me.Recordset
    rs.MoveFirst
    rs.Find "[unique ID] = " & Val(Nz([sfrmDataEntrySummaryView]![Outlook ID], 0))
    If rs.EOF Then
    msgbox "No Project Found", vbOKOnly, "Installation Outlook"
    Else
    Me.Bookmark = rs.Bookmark
    End If

Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[DomDom3]

Thanks for your response, rather frustratingly this gives the same results as the code I was using.

I've been trying to narrow down the possibilities and I can only think after trying your code, that the VBA is good, and that importing a form from an mdb to and adp does something (or doesn't do something) to form's settings which prevents the code from running correctly.


Cheers

 

[DomDom3]

Sorry Daniel, I've done you an injust your code has worked.

I originally copied it verbatim without adding
Dim rs As Object
and hence it didn't work.

I wonder if you could explain the significance of rs.movefirst

This line means the forms doubles its work - it has to look for the first record each time its run adn then the required record. I've removed it but then it finds the records once, then goes to the Project Not Found msgbox.

 

[cmmrfrds]

Instead of defining rs as an object, define as a recordset object.

Dim rs as New ADODB.Recordset

In ADO the Filter method is implemented more efficiently than the Find method and is more flexible (for example it can use multiple criteria) and no need to position on the 1st record.

rs.Filter = "[unique ID] = " & Val(Nz([sfrmDataEntrySummaryView]![Outlook ID], 0))

Also, Filters are accumulative so that you can keep narrowing the criteria.

To turn off the Filter and return to original.
rs.Filter = adFilterNone