Home

Data Navigation
 

Introduction

To make information of a table available to the user, you can display it on a form, a web page, etc. There are two main types of display used for the records. You can display all records in a type of table or grid made of columns and rows, as done on a spreadsheet like StarCalc, Microsoft Excel, or Corel Quattro Pro, etc. You can also display only one record. Whatever technique you use, you should provide the user with the means of moving from one record to another. While this is usually easy on a spreadsheet that displays as many records as can be seen, if you decide to display one record at a time, you can then create some buttons that the user would click to navigate through records.
 

Practical Learning: Navigating Through Records
  1. This exercise continues from the previous lesson
    Add a new form to the project
  2. Copy the labels and text boxes on the first form and paste them in the second form
  3. Add five new command buttons and a label to the form designed as follows:
     
    The new controls are named from left to right as cmdClose, cmdFirst, cmdPrevious, lblRecNbr, cmdNext, cmdLast
  4. Save the form's file as EmplRecords
  5. Double-click and empty area of the form to access its Load event then return to the form
  6. Double-click all buttons
  7. Implement the module as follows:
      
    Option Explicit
Dim conEmployees As New ADODB.Connection
Dim rcsEmployees As New ADODB.Recordset
Dim intRecNumber As Integer

Private Sub cmdClose_Click()
    Hide
End Sub

Private Sub cmdFirst_Click()
    rcsEmployees.MoveFirst
    intRecNumber = 0
                     
    Me.txtEmployeeNbr.Text = rcsEmployees.Fields("EmplNumber")
    Me.txtDateHired.Text = rcsEmployees.Fields("DateHired")
    Me.txtFirstName.Text = rcsEmployees.Fields("FirstName")
    Me.txtLastName.Text = rcsEmployees.Fields("LastName")
    Me.txtDepartment.Text = rcsEmployees.Fields("Department")
    Me.txtEmailAddress.Text = rcsEmployees.Fields("EmailAddress")
        
    Me.lblRecNbr.Caption = "1 of " & rcsEmployees.RecordCount
End Sub

Private Sub cmdLast_Click()
    rcsEmployees.MoveLast
    intRecNumber = rcsEmployees.RecordCount
    
    Me.txtEmployeeNbr.Text = rcsEmployees.Fields("EmplNumber")
    Me.txtDateHired.Text = rcsEmployees.Fields("DateHired")
    Me.txtFirstName.Text = rcsEmployees.Fields("FirstName")
    Me.txtLastName.Text = rcsEmployees.Fields("LastName")
    Me.txtDepartment.Text = rcsEmployees.Fields("Department")
    Me.txtEmailAddress.Text = rcsEmployees.Fields("EmailAddress")
        
    Me.lblRecNbr.Caption = CStr(intRecNumber) & " of " & _
				rcsEmployees.RecordCount
End Sub

Private Sub cmdNext_Click()
    rcsEmployees.MoveNext
    intRecNumber = intRecNumber + 1
    
    If Not rcsEmployees.EOF Then
        Me.txtEmployeeNbr.Text = rcsEmployees.Fields("EmplNumber")
        Me.txtDateHired.Text = rcsEmployees.Fields("DateHired")
        Me.txtFirstName.Text = rcsEmployees.Fields("FirstName")
        Me.txtLastName.Text = rcsEmployees.Fields("LastName")
        Me.txtDepartment.Text = rcsEmployees.Fields("Department")
        Me.txtEmailAddress.Text = rcsEmployees.Fields("EmailAddress")
            
        Me.lblRecNbr.Caption = CStr(intRecNumber + 1) & " of _
				" & rcsEmployees.RecordCount
    Else
        cmdLast_Click
    End If
End Sub

Private Sub cmdPrevious_Click()
    rcsEmployees.MovePrevious
    intRecNumber = intRecNumber - 1
    
    If Not rcsEmployees.BOF Then
        Me.txtEmployeeNbr.Text = rcsEmployees.Fields("EmplNumber")
        Me.txtDateHired.Text = rcsEmployees.Fields("DateHired")
        Me.txtFirstName.Text = rcsEmployees.Fields("FirstName")
        Me.txtLastName.Text = rcsEmployees.Fields("LastName")
        Me.txtDepartment.Text = rcsEmployees.Fields("Department")
        Me.txtEmailAddress.Text = rcsEmployees.Fields("EmailAddress")
            
        Me.lblRecNbr.Caption = CStr(intRecNumber) & " of " & _
				rcsEmployees.RecordCount
    Else
        cmdFirst_Click
    End If
End Sub

Private Sub Form_Load()
    intRecNumber = 0
    
    conEmployees.Open "Provider='Microsoft.JET.OLEDB.4.0';" & _
                      "Data Source='C:\ADO Lessons\MVA2\MVA.mdb'"
    rcsEmployees.Open "Employees", conEmployees, _
			adOpenStatic, adLockOptimistic
    
    Me.txtEmployeeNbr.Text = rcsEmployees.Fields("EmplNumber")
    Me.txtDateHired.Text = rcsEmployees.Fields("DateHired")
    Me.txtFirstName.Text = rcsEmployees.Fields("FirstName")
    Me.txtLastName.Text = rcsEmployees.Fields("LastName")
    Me.txtDepartment.Text = rcsEmployees.Fields("Department")
    Me.txtEmailAddress.Text = rcsEmployees.Fields("EmailAddress")
    
    Me.lblRecNbr.Caption = CStr(intRecNumber + 1) & " of " & _
				rcsEmployees.RecordCount
End Sub

Private Sub Form_Unload(Cancel As Integer)
    rcsEmployees.Close
    conEmployees.Close
    Set rcsEmployees = Nothing
    Set conEmployees = Nothing
End Sub
  8. Add two number buttons to the firs form as follows:
     
    The new buttons are named cmdEmplRecords and cmdClose
  9. Double-click the new buttons and implement their Click events as follows:
      
    Private Sub cmdClose_Click()
    End
End Sub

Private Sub cmdEmplRecords_Click()
    Form2.Show
End Sub
  10. Execute the application
  11. Return to your programming environment
 
Previous Copyright © 2001-2005 FunctionX, Inc.