[RESOLVED] Cannot search database and then cycle through with next button

[chris.cavage]

    

Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;" & _
      "Data Source=localhost;Initial Catalog=testdb" '
    cn.Open
    Set rs = New ADODB.Recordset 'as we did with the connection
    rs.Open "test", cn, adOpenKeyset, adLockPessimistic, adCmdTable

Private Sub LoadDataInControls()

    If rs.BOF = True Or rs.EOF = True Then
        Exit Sub

    End If
    
   
    txtTimeStart.Text = rs.Fields("time started") 
    txtThink.Text = rs.Fields("think")
    txtGender.Text = rs.Fields("gender")
    txtEMail.Text = rs.Fields("e-mail")
    Text2.Text = rs.Fields("id")
         
End Sub

Private Sub rs_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    
    If blnAddMode = False Then
        'If we are not in the add mode, load data in the
        'controls.
        
        Call LoadDataInControls
        
    End If
End Sub

Private Sub cmdNext_Click()
     If rs.EOF = False Then
       rs.MoveNext
        If rs.EOF Then
            rs.MoveLast
        End If
    Else
        If rs.BOF Then
   
            MsgBox "There is no data in the record set!"
        Else
            rs.MoveLast
        End If
    End If
End Sub


Private Sub cmdSearch_Click()

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM test where id='" & Text1.Text & "'", cn, adOpenDynamic, adLockOptimistic

End Sub

The fields load properly with all the information. When I hit the "Next" button first, just after the form is loaded, I CAN move to the next record without a problem.

Next, when I type something into a search text box and hit "search" the information loads into the controls as I need them too.

HOWEVER, when I hit the "next" button to go to the next record from the searched results, it does not work. It does nothing.

Any help? Thanks!!!

[chris.cavage]

I am assuming it is doing this because my search will only yield one record, so there is nothing to actually move to!

So how can I get it to move to the next record once a search is done?

[si_the_geek]

I would assume that the problem is a lack of info given by your program - it doesn't tell the user that there isn't a next record to go to.

In cmdNext_Click, I would recommend a MsgBox "You are at the last record" whenever you do a rs.MoveLast

I would also recommend enabling/disabling the buttons as apt - so that the Next button (and others) is only enabled when there is a record to go to. As you are using WithEvents, you can do that by adding the following code to the end of the _MoveComplete event:

cmdPrev.Enabled = Not(rs.BOF)
cmdFirst.Enabled = Not(rs.BOF)
cmdNext.Enabled = Not(rs.EOF)
cmdLast.Enabled = Not(rs.EOF)

[chris.cavage]

Private Sub rs_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    
    If blnAddMode = False Then
        'If we are not in the add mode, load data in the
        'controls.
        
        Call LoadDataInControls
        'This event was fired by opening the recordset

    End If
    
cmdPrevious.Enabled = Not (rs.BOF)
cmdFirst.Enabled = Not (rs.BOF)
cmdNext.Enabled = Not (rs.EOF)
cmdLast.Enabled = Not (rs.EOF)


End Sub

That correct? Still cannot move "next" after a search.

[chris.cavage]

Thanks for the recommendation on the "Last record msgbox", I will definitely do that.