please tell me how to search in a recordset?

Hi,

I want to serach records from the record set i hv created in the following code.This record set
Contains values such as

DD Admin-1
DD Admin-2
D-1
D-2
AD-1
AD-2

I just want to match these fields wid the "combo box value" n as in the example i gave the letters
b4 "-" can be same,so first i hv to extract the fields which hv same letters b4 - n then to find out
the last no.Such as first i found

D-1
D-2

thn i hv to check the max no..in this case it is 2..
So kindly tell me where is the problem in this code as it is giving error on "rs.BOF"

VB Code:

Public Sub Designation()
Dim find_value As String
Set conn = New clsConnection
Set rs = New ADODB.Recordset
 
Dim count_value As Integer
 
Dim results As String
Dim index As String
Dim no As String
 
 
 
 
conn.clsOpen
find_value = "SELECT Designation from System_Users;"
rs.Open find_value, conn.cn, adOpenDynamic, adLockBatchOptimistic
'count_value = rs.Fields.Count
 
rs.BOF
If Not rs.EOF Then
 
      Do While Not rs.EOF
        'type_value = rs("Type_Name")
          ' Form4.cmbID.AddItem type_value
       
           results = rs.find("form4.cmbRank.text", adSearchForward)
           index = InStr(index, "-")
           no = Left(results, Len(results) - index)
           MsgBox no, vbOKOnly, "ALERT"
            rs.MoveNext
            
      Loop
 
  End If
  MsgBox results(index), vbOKOnly, "ALERT"
  
  
  conn.clsClose
      
End Sub

Waiting for ur urgent reply

Regards,

If I understand your question correctly i.e. you have a value in a combo box, and you want to use that value to find a particular record then:

You could change your FindValue to include a Where statement.

If this does not appeal to you, then you need to change the code you have listed below, because the Find will search the entire recordset that you have brought into memory, so running it in a loop will only slow things down for you.

Because you are running your search forward, you should issue a MOVEFIRST before the find, to ensure that you are not omitting any records.

Also when using the FIND command, you have to tell it what you want to find
eg

Code:

---

rs.Find "UsrId = " & Chr$(39) & *****r & Chr$(39), , adSearchForward
If Rs.EOF then
'rcd not found
Else
'Rcd Found
End If

---

The above implies that you would have to include an extra field in your record set.
RS.BOF is a conditional that you can check, I don't believe that you can use it to move to the beginning of the file.

HTH

This code is giving error "Expected Function or variable" at the line.

VB Code:

results = rs.find("form4.cmbRank.text", adSearchForward)

please guide me about the error

Regards,

The Find method is a Sub not a Function.
The Criteria argument is basically the same as the Where clause in a SQL statement.
SearchDirection is the third argument not the second.

VB Code:

strFind = "Designation = '" & form4.cmbRank.text  & "'"
rs.find strFind, , adSearchForward

how the result of that find statement can be known.Means hoew we can retrive the records this statement will search.Please guide me.

Just keep calling the Find method until you hit EOF.

VB Code:

'Code to Open Recordset
strFind = "Designation = '" & form4.cmbRank.text  & "'"
rs.Find strFind, , adSearchForward
 
Do Until rs.EOF      
     'code to process the record
     rs.Find strFind,1 , adSearchForward
Loop

Can u please tell me the type of rsFind?

rsFind is a typo and should have been rs.Find

rs is the ADO Recordset object.

I updated the code in my previous post.

its giving error "Either BOF or EOF file true..Requested operation require a record.." on the line

VB Code:

rs.movenext

Kindly guide me

Are you using my code? If yes, MoveNext is not required within the loop.

Ok..Its done..Thankx now please tell me that how I can store the values of this recordset in an array so can have further serach over those values..As now the task is to serach the max no at the end of these values.

Waiting for ur reply

You do not need to do the search in a loop, unless there are multiple records in the recordset with the value you are looking for. You have a recordset, and the search starts at the current position, and looks from there to the bottom of the recordset, which is why I suggested using the .MOVEFIRST method, to position you at the top of the recordset.

If the Find was unsucessful, the property .EOF will be set true.

To load the record into an array, you would need to keep a counter variable, and every time you got a valid find you would add 1 to the counter, and then store the record in the array using the counter.

HTH