FIND NEXT function?

[pu8y]

Hi all,

I am currently developing a "Find" function to ease the application's users while facing with bulk data.

From the main page, I have a FIND button which will prompt a FIND child form. After user entered the keyword and click on FIND NEXT on the child form, just assumed the database returns 10 records. So, in the main page i will display all the data for the first row of returned data. If user click on FIND NEXT again, main page will show the second row, then third row, forth row, etc.

How can i make it with vb6 ADODB.RecordSet?

Thanks

[jp26198926]

im not sure.. maybe u can use something like this...:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim bClick As Boolean

Private Sub Form_Load()
  bClick = False
End Sub

Private Sub FindNext_Click()

If bClick = False Then
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Sample.mdb"
    
    Set rs = New ADODB.Recordset
    rs.Open "Select * From Table1 Where Table1.Name='Ur_Search_Here'", cn, adOpenDynamic, adLockOptimistic

    MainPage.Text1.Text = rs(0)
    MainPage.Text2.Text = rs(1)
    'etc................
    
    rs.MoveNext
    bClick = True
Else
    If rs.EOF = False Then
        MainPage.Text1.Text = rs(0)
        MainPage.Text2.Text = rs(1)
        'etc....................
        
        rs.MoveNext
    Else
        MsgBox "No more records"
        rs.Close
        cn.Close
    End If
End If

End Sub

[pu8y]

Hi jp26198926,

rs.MoveNext seems doesn't work. It only can display the first result. Once I press FIND NEXT again, it already reached EOF.

[LaVolpe]

The sample code posted is querying the database and filtering/returning only records that match the search criteria. Therefore, .MoveNext would move to next filtered record if there is one, else EOF

I think a misunderstanding. It sounds like you have a recordset that contains many records that may or may not contain the requested keywords. If so

Assumption is that before this is called, your rs is already on a matching record
1. Call .MoveNext to get off of the current record that is already matching
2. Check to see if .EOF, and if so, can't use .Find again
3. If not EOF, then call the .Find method again just like you did the first time
4. Check for .EOF again. If EOF, no more matches, otherwise, rs is on the next match
5. If .EOF, then disable your FindNext because there are not more records to search

Just repeat the above steps for each FindNext

[pu8y]

My rs.MoveNext only works within while loop:

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM document_request WHERE " & searchBy & _
            " LIKE '%" & TextSearch.Text & "%'", db_connect, adOpenKeyset, adLockOptimistic

Do While Not rs.EOF
        MsgBox ("next Record " & IIf(IsNull(Trim(rs!ctrl)), "", Trim(rs!ctrl)))
        rs.MoveNext
Loop

But I don't want them to show in one time in while loop, i wish to do the idea as jp26198926, when the user click "FIND NEXT", one record is showed.

Below is the code I wrote. When the user click on "FIND NEXT" for the first time, it is OK since it goes through the IF, and when the user click on "FIND NEXT" for the second time, it comes to ELSE. However, it already reached EOF. Anyone can correct me?

Private Sub Form_Load()
    bClick = False
End Sub

Private Sub CommandFind_Click()
If bClick = False Then
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM document_request WHERE " & searchBy & _
            " LIKE '%" & TextSearch.Text & "%'", db_connect, adOpenKeyset, adLockOptimistic
    MsgBox ("total=" & rs.RecordCount)

    rs.MoveFirst
    frm_verify_request.TextCTRL.Text = IIf(IsNull(Trim(rs!ctrl)), "", Trim(rs!ctrl))
    frm_verify_request.fillForm (IIf(IsNull(Trim(rs!ctrl)), "", Trim(rs!ctrl)))
    rs.MoveNext

    bClick = True
   
Else

    Do While Not rs.EOF
        MsgBox ("next Record " & IIf(IsNull(Trim(rs!ctrl)), "", Trim(rs!ctrl)))
        rs.MoveNext
    Loop
       
    If rs.EOF = True Then
        MsgBox "No more records"
    End If
End If
End Sub

[LaVolpe]

Are you declaring rs at the top of your form? Are you using Option Explicit also?
Is CommandFind button's caption "Find Next"? If not, what code do you have in "Find Next"'s click event?
Is rs being used/manipulated elsewhere between your button clicks actions?

P.S. Instead of using code like:
IIf(IsNull(Trim(rs!ctrl)), "", Trim(rs!ctrl)))
You can simplify it a bit and use following to handle Null values, assuming rs!ctrl is a text field
Trim(rs!ctrl & "")

[MarkT]

I think you could simplify it a bit by generating your RS outside of the button click.

Option Explicit
Dim rs as ADODB.Recordset

Private Sub Form_Load()
    'Connect to the database and then create your recordset
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM document_request WHERE " & searchBy & _
            " LIKE '%" & TextSearch.Text & "%'", db_connect, adOpenKeyset, adLockOptimistic
End Sub

Private Sub CommandFind_Click()
    If Not rs.EOF Then
        frm_verify_request.TextCTRL.Text = Trim(rs!ctrl & "")
        'Not sure what this line is
        'frm_verify_request.fillForm Trim(rs!ctrl & "")
        rs.MoveNext   
    Else
        MsgBox "No more records"
    End If
End Sub