Find a Record in an ADODB.Recordset 1

[scking]

I'm converting an application from Jet to SQL Server using Access 2002. My legacy code frequently uses:

Dim db As DAO.Database
Set db = CurrentDb

I because getting errors on the 'Set' statement even though I had a reference set to the DAO 3.6 Object Library so I started to convert the code to use ADO and the 'Find' method of the ADO recordset is different from the 'FindFirst' of the DAO recordset. Using a criteria statement without an 'And' works but the second I make the criteria complex (two separate data elements combined with and) the code fails saying "Error 3001, Arguments are of the wrong type or out of the acceptable range." I have used debug to verify the values of the combo box columns are correct and the criteria is correctly forms so obviously there is something I don't know about building the criteria. None of the 8 books I have cover the 'Find' method is sufficiently to clear up my questions and the help is rather unhelpful.

Set rst = Me.RecordsetClone
rst.MoveFirst
strCriteria = "[PN]=""" & Me.cboQFind.Column(0) & """ And [Cage]=""" & Me.cboQFind.Column(1) & """;"
=> rst.Find strCriteria, 1, adSearchForward

Any assistance would be greatly appreciated.


-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[scking]

I discovered in an article on the knowledgebase that ADO does not have a parser capable of parsing complex criteria. That's why the single criteria works and not the complex. This obviously means I need to look for a new design for this routine.



-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[cmmrfrds]

Here is an example of sorting and filtering an ADO recordset. Use the Filter not the Find. The Filter works like a Where clause in SQL Server.
Example.
rs.Filter = "myfield = 3 and myfield = 4 " etc...


rs.Sort = "myid DESC"

rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; " "; rs!mydesc
rs.MoveNext
Wend

rs.Filter = "myid = 3"

rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; " "; rs!mydesc
rs.MoveNext
Wend

rs.Filter = adFilterNone

rs.Close
Set rs = Nothing

 

[scking]

cmmrfrds thanks for the suggestion. In this case I believe that would work just fine. What I actually did was to create a query column which concatenated the two columns and then searched for the concatenated string as a single criteria. It works fine but I believe that I will adopt your recommendatation as it is better and more elegant application.

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[cmmrfrds]

One thing to be careful of on the "Find" in ADO is that, the recordset will be reread from the server - probably not what you want. The "Filter" method will be performed on the client side assuming client side cursor location in both cases.