ADO - FIND Statement

[hyme]

Howdy!

OK.. Here's one I've never tried!

Using the FIND Statement with 1 field works fine, but can you it to find 2 Fields????


This works:

Lastname = "DOE"
Criteria = "Last_Name = '" & Lastname & "'"
adoPrimaryRS.MoveFirst
adoPrimaryRS.Find Criteria, , adSearchForward, 1

This doesn't:

Lastname = "DOE"
FirstName = "JOHN"
Criteria = "Last_Name = '" & Lastname & "'" & " AND First_Name = '" & FirstName & "'"
adoPrimaryRS.MoveFirst
adoPrimaryRS.Find Criteria, , adSearchForward, 1

HELP!!!!! Thanx!

[Granty]

Hrmmm...never tried the ADO find but try.....

Criteria = "Last_Name = '" & Lastname & "' AND First_Name = '" & FirstName & "'"

J.

[Gary.Lowe]

Hyme

Use the ADO seek method

MSDN

Sub ADOSeekRecord()
 
   Dim cnn As New ADODB.Connection
   Dim rst As New ADODB.Recordset
 
   ' Open the connection
   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\NorthWind.mdb;"
 
   ' Open the recordset
   rst.Open "Order Details", cnn, adOpenKeyset, adLockReadOnly, _
      adCmdTableDirect
 
   ' Select the index used to order the data in the recordset
   rst.Index = "PrimaryKey"
 
   ' Find the order where OrderId = 10255 and ProductId = 16
   rst.Seek Array(10255, 16), adSeekFirstEQ
 
   ' If a match is found print the quantity of the order
   If Not rst.EOF Then
   Debug.Print rst.Fields("Quantity").Value
   End If
 
   ' Close the recordset
   rst.Close
 
End Sub

Because Seek is based on an index, it is important to specify an index before searching. In the previous example, this is not strictly necessary because Microsoft Jet will use the primary key if an index is not specified.

In the ADO example, the Visual Basic for Applications Array function is used when specifying a value for more than one column as part of the KeyValues parameter. If only one value is specified, it is not necessary to use the Array function.

As with the Find method, use the NoMatch property with DAO to determine whether a matching record was found. Use the BOF and EOF properties as appropriate with ADO.

The Seek method will work correctly only for Microsoft Jet 4.0 databases. It will fail with a run-time error for all earlier formats, even if you use the Microsoft Jet 4.0 database engine to open the database. This will cause a problem if the application is written to support older database formats. If so, use the Supports method of the Recordset object to determine whether the Seek method is available for the open Recordset. However, if all client applications use the newer format, this check is unnecessary. Use either Microsoft Access 2000 or the CompactDatabase method to convert older databases to the newer format.

[Kokomo]

I wouldn't bother messing around with the .Find method. Here's
how I would approach this problem.

Dim qry As QueryDef
Dim rs As Recordset
Dim sSQL As String

sSQL = "SELECT tblUsers.[Last_Name], tblUsers.[First_Name] " & _
"FROM tblUsers WHERE (tblUsers.[Last_Name])=""DOE"") " & _
"AND tblUsers.[First_Name]=""JOHN""));"

Set qry = CurrentDb.CreateQueryDef("", sSQL)
Set rs = qry.OpenRecordset

If rs.RecordCount > 0 Then
----- MsgBox "FoundRecord"
Else
----- MsgBox "No Record Found"
End If

[Granty]

I agree with the above 2 guys about what to use (hence my lack of knowledge of ADO Find), was just trying to fix the find problem :P

But in the above example I would test for EOF rather than use recordcount, because I find .recordcount is a bit....dodgy...

J.

[Gary.Lowe]

The only quarms I would have with kokamo's code is that I think Hyme is using ADO rather than DAO

[hyme]

YUP!

[Granty]

Heh, that he is Gary. Can be changed a bit tho....

POB!!!!

J.

[Gary.Lowe]

[Lee_S]

POB!!!!

I knew I recognised that face from somewhere!

Takes me back too many yrs that..

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~>>>>>>>
Follow the string!!!

[hyme]

OK!
What is POB???

[Gary.Lowe]

Originally posted by Lee_S

I knew I recognised that face from somewhere!

Takes me back too many yrs that..

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~>>>>>>>
Follow the string!!!

Ha Haa

Now that was entertainment...

Ahhh gone are the days of simple things please simple minds eh?

[Lee_S]

-Insert Brian Conely voice-

Its a puuppeeeetttt!

Well, sort of. Very old kids tv proggie. Gary's avatar is a piccie of Pob!!

[hyme]

Thanx, now I know!

[Gary.Lowe]

Hyme

Either your too young or not from the UK

Anyway

did any of the above stuff help you out with your problem

[hyme]

I'm 28 and from Canada - I don't know if I qualify!

While I was waiting for a response. I came up with this.
It does a double check (so, it's not built for optimum performance)
But, for the one-time I need to use it, it worked

Here it is:

Do While Not adoSecondaryRS.EOF

TempName = adoSecondaryRS!Name
TempName2 = InStr(1, TempName, ",")

Lastname = Mid$(TempName, 1, TempName2 - 1)
FirstName = Mid$(TempName, TempName2 + 1, 3)
Criteria = "Last_Name = '" & Lastname & "'"

adoPrimaryRS.MoveFirst
adoPrimaryRS.Find Criteria, , adSearchForward, 1

Do Until adoPrimaryRS.EOF = True
If Left$(adoPrimaryRS!First_Name, 3) = FirstName Then
adoPrimaryRS!Employee_ID = adoSecondaryRS!EmployeeID
adoPrimaryRS.Update
adoPrimaryRS.MoveLast
Else
adoPrimaryRS.MoveNext
End If
Loop
adoSecondaryRS.MoveNext

Loop

This is built for finding firstname & lastname (in two seperate fields in 1 table and ONLY in 1 field in the 2nd table) Then adding an employee number to the table.

[Gary.Lowe]

Old enough

but wrong country.

Good to see you got it sorted out anyway.