Hi there
I am fairly new to vba though not access and am used to programing web pages using asp.

I have a form that adds a record to a databse.
I also have a search form that displays results on a surname.

This works fine with 1 record.
I need to add Next and Previous buttons to move through the recordset if there is more than 1 record with the same surname.

Here is a snipet of code used in the searchResults Form.
It displays one record only.
---------------------------------------------------------
Private Sub Form_Load()
'Get the name from the search form
Dim strNameToSearch
Dim sConnect As String
Dim objConn As New ADODB.Connection
Dim rs

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=c:\database.mdb"
'//Opening the Connection
objConn.Open sConnect

strNameToSearch = Form_Search.txtSurnameSearch.Value
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM clientDetails WHERE clientDetails.surname = '" & [strNameToSearch] & "' ", objConn

Do While Not rs.EOF
   rs.MoveFirst

    '//populate the textboxes on the form.
    txtFirstName.Value = rs!firstName
    txtSurname.Value = rs!surname
    txtAddress.Value = rs!address
    txtPostCode.Value = rs!postcode
    txtTelephoneNumber = rs!telephone
    txtMobileNumber = rs!mobile
    txtEmailAddress = rs!email
    txtComments = rs!comments

  rs.MoveNext
Loop


 objConn.Close

End Sub
-------------------------------------------------------------
Any help would be most appreciated.
peter

Hi peter,

Record navigation with ADO needs 3 pieces:

1. A module-level recordset variable that your First, Previous, Next and Last command button event procedures can see.

2. A generic routine to rebind the recordset fields to your controls that your First, Previous, Next and Last command button event procedures can call.

3. Code to: disable the the First and Previous command buttons when the beginning of the recordset is reached, and disable the Next and Last command buttons when the end of the recordset is reached.

Something like this:

Option Compare Database
Option Explicit

' module-level recordset variable
Dim m_rstCustomers As ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)

    Dim cnn As ADODB.Connection
    Dim strSQL As String

    Set cnn = CurrentProject.Connection

    Set m_rstCustomers = New ADODB.Recordset
    strSQL = "SELECT * FROM tblCustomers"
    m_rstCustomers.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

    BindFields

End Sub

' Generic binding routine
Private Sub BindFields()
    With Me
        .txtCustomerID = m_rstCustomers("CustomerID")
        .txtFirstName = m_rstCustomers("FirstName")
        .txtLastName = m_rstCustomers("LastName")
    End With
End Sub

Private Sub cmdFirst_Click()
    m_rstCustomers.MoveFirst
    cmdNew.SetFocus

    cmdFirst.Enabled = False
    cmdNext.Enabled = True
    cmdPrevious.Enabled = False
    cmdLast.Enabled = True

    BindFields
End Sub

Private Sub cmdLast_Click()
    m_rstCustomers.MoveLast
    cmdNew.SetFocus

    cmdFirst.Enabled = True
    cmdNext.Enabled = False
    cmdPrevious.Enabled = True
    cmdLast.Enabled = False

    BindFields
End Sub

Private Sub cmdNext_Click()
    m_rstCustomers.MoveNext
    cmdNew.SetFocus

    m_rstCustomers.MoveNext
    If m_rstCustomers.EOF Then
        cmdNext.Enabled = False
        cmdLast.Enabled = False
    Else
        cmdNext.Enabled = True
        cmdLast.Enabled = True
    End If
    m_rstCustomers.MovePrevious
    cmdFirst.Enabled = True
    cmdPrevious.Enabled = True

    BindFields
End Sub

Private Sub cmdPrevious_Click()
    m_rstCustomers.MovePrevious
    cmdNew.SetFocus

    m_rstCustomers.MovePrevious
    If m_rstCustomers.BOF Then
        cmdFirst.Enabled = False
        cmdPrevious.Enabled = False
    Else
        cmdFirst.Enabled = True
        cmdPrevious.Enabled = True
    End If
    cmdNext.Enabled = True
    cmdLast.Enabled = True
    m_rstCustomers.MoveNext

    BindFields
End Sub

HTH,

Bob

Thanks for the reply Bob, I have tried to re-create your example but when I run the form i get this error...

Member already exists in an object module from which this object module derives.

 I have no idea what it means...
------------------------------
ok...fixed it. All most got the sample working...but I get an error on the previous button.."Operation Is Not Allowed In This Context" ??
Peter

Yikes! Weird. A derived class is a class that inherits some or all of its properties or methods (class members) from a base class. So on the face of it, your error meassage means that a property or method name is some derived class your code is using duplicates a property or method name in a base class that it inherits. But since VBA doesn't support inheritance, I'm clueless. Must be an under-the covers thing.

Can you set a break point and see what line your code is breaking on? Or post your code so I can try and see whats up?

Bob

I'm really struggling with typing today:

"a property or method name is some derived class "

should read

a property or method name in some derived class

Bob

Hi BOB,
Here is a copy of the code in my sample database.

-----------------------------------------------------------
Option Compare Database
Option Explicit
Dim m_rst As ADODB.Recordset

'//Generic binding routine
Private Sub BindFields()
    With Me
        .txtCustomerID = m_rst("CustomerID")
        .txtFirstName = m_rst("FirstName")
        .txtLastName = m_rst("LastName")
    End With
End Sub
Private Sub Form_Load()
Dim strNameToSearch
Dim sConnect As String
Dim objConn As New ADODB.Connection
Dim strNotHealth As String

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=c:\customers.mdb"
'//Opening the Connection
objConn.Open sConnect
'//Provider for MS-Access 2000 Database file is '//Microsoft.Jet.OLEDB.4.0 Provider for MS-Access 97 Database its //'Microsoft.Jet.OLEDB.3.51.
'//Once our connection is established, we can read the records (Rows) of the table by using the ADODB Recordset object.


Set m_rst = New ADODB.Recordset

m_rst.Open "SELECT * FROM tblCustomers", objConn
'//Dim loopcount
'//Do While Not m_rst.EOF
'// loopcount = loopcount + 1
'//Loop
'//Debug.Print "loopcount = " & loopcount
'//txtCount.Value = m_rst.RecordCount
BindFields
End Sub

Private Sub cmdNext_Click()
On Error GoTo Err_cmdNext_Click

    m_rst.MoveNext
    BindFields

     If m_rst.EOF Then
        cmdNext.Enabled = False

    Else
        cmdNext.Enabled = True

    End If

Exit_cmdNext_Click:
    Exit Sub

Err_cmdNext_Click:
    MsgBox Err.Description
    Resume Exit_cmdNext_Click

End Sub
Private Sub cmdPrevious_Click()
On Error GoTo Err_cmdPrevious_Click

m_rst.MovePrevious
BindFields


Exit_cmdPrevious_Click:
    Exit Sub

Err_cmdPrevious_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrevious_Click

End Sub
------------------------------------------------------------
The next button works ok.
I still get the error "Operation Is Not Allowed In This Context" on the previous button.

Regards
Peter

Hi peter,

Why did you delete most of the cmdPrevious_Click() routine????

The BOF (Beginning of File) check is necessary. Your error message is telling you you can't move to the previous record because there is no previous record.

The code I posted runs. Try implementing it as written before making a lot of modifications. That way you'll have a solid code base, and can back-track as new error messages arise.

Bob

Hi Peter,

If you want only a Next and Previous button, use the code below. The form has three text boxes on it and three command buttons (cmdNext, cmdPrevious, cmdNew). The reason I included a few cmdNew.SetFocus statments is because you can't disable a control when it has the focus. Since you need to disable cmdNext when m_rstCustomers.EOF is True and cmdPrevious when m_rstCustomers.BOF is True, cmdNew just provides a convenient control to set focus to while you disable the other command buttons. Also, you should set the enabled property of cmdPrevious to No at design-time (use the Properties Window) so that is is initially disabled when the Form loads and displays the first record.

Option Compare Database
Option Explicit

Dim m_rstCustomers As ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)

    Dim cnn As ADODB.Connection
    Dim strSQL As String

    Set cnn = CurrentProject.Connection

    Set m_rstCustomers = New ADODB.Recordset
    strSQL = "SELECT * FROM tblCustomers"
    m_rstCustomers.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

    BindFields

End Sub

Private Sub BindFields()
    With Me
        .txtCustomerID = m_rstCustomers("CustomerID")
        .txtFirstName = m_rstCustomers("FirstName")
        .txtLastName = m_rstCustomers("LastName")
    End With
End Sub

Private Sub cmdNext_Click()
    m_rstCustomers.MoveNext
    cmdNew.SetFocus

    m_rstCustomers.MoveNext
    If m_rstCustomers.EOF Then
        cmdNext.Enabled = False
    Else
        cmdNext.Enabled = True
    End If

    m_rstCustomers.MovePrevious
    cmdPrevious.Enabled = True

    BindFields
End Sub

Private Sub cmdPrevious_Click()
    m_rstCustomers.MovePrevious
    cmdNew.SetFocus

    m_rstCustomers.MovePrevious
    If m_rstCustomers.BOF Then
        cmdPrevious.Enabled = False
    Else
        cmdPrevious.Enabled = True
    End If
    cmdNext.Enabled = True
    m_rstCustomers.MoveNext

    BindFields
End Sub

HTH,

Bob

Hi Bob
Many thanks for your help, the sample works ok now, I just have to apply to my main app, I'll let you know how it goes

Regards
Peter