Record Navigation in AccessDB on UserForm

Question

Please, am creating a bible. So far so good, every other parts of the UI has been coded successfully but can't seem to code the "Next" and "Previous" navigation buttons. As designed, the user enters his or her desired bible reference through certain comboboxes (cmbBook, cmbChapter, cmbVerse), to generate the respective bible readings. The result is shown in a Label. From this point, I would want that when the "Next" button is clicked, the next bible verse is shown, and when the "Previous" button is clicked, the previous bible verse is shown. My access database has two columns namely the BibleReference and BibleReadings. See code, thank you.

Imports System.Data.OleDb 'for db  
  
Public Class Form1  
  
 Private connectionkjvbible As OleDbConnection = New OleDbConnection()  
  
 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load  
        Try  
            connectionkjvbible.Open()  
            checkConnection.Text = "Server Connected!"  
            connectionkjvbible.Close()  
        Catch ex As Exception  
            Dim nex As String  
            nex = MessageBox.Show("Sorry, databse server not connected!", "OfflineBible1 DB, Error", MessageBoxButtons.OK)  
            If nex = DialogResult.OK Then  
                Application.Exit()  
            End If  
        End Try  
 End Sub  
   
 Public Sub New()  
        connectionkjvbible.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=connectionkjvbible.accdb; Persist Security Info=False;"   
 End Sub  
   
 Search data and show in label - new testament   
  
 Private Sub BtnSubmitToDatabase_Click(sender As Object, e As EventArgs) Handles BtnSubmitToDatabase.Click  
  
        connectionkjvbible.Open()  
  
        Dim command As OleDbCommand = New OleDbCommand()  
        command.Connection = connectionkjvbible  
        command.CommandText = "Select * from NewTestament where BibleReference='" & txt_WordID2.Text & "'"   
  
        Dim reader As OleDbDataReader = command.ExecuteReader()  
        Dim count As Integer = 0  
  
        While reader.Read()  
            count = count + 1  
        End While  
  
        connectionkjvbible.Close()  
  
        If count = 1 Then  
            connectionkjvbible.Open()  
            command.Connection = connectionkjvbible  
            command.CommandText = "Select BibleReadings from NewTestament where BibleReference='" & txt_WordID2.Text & "'"    
            Dim dr As OleDbDataReader = command.ExecuteReader()  
  
            While dr.Read()  
                LblBibleVerses2.Text = dr("BibleReadings").ToString()  
            End While  
  
            dr.Close()  
            connectionkjvbible.Close()  
        Else  
            MessageBox.Show("Sorry, this entry does Not exist in database. Please ensure your entry is correctly spelt.")  
        End If  
  
 End Sub  
   
End Class  

Answer 1

Hi

You may benefit from using a BindingNavigator, which is specifically designed for such tasks.

Here is a LINK that may help, and, if you Google for 'VB.NET BindingNavigator' you will get LOTS of info.

Answer 2

What i did was simple. The bible verses have sequential IDs in the database. When the user searches a bible reference, the corresponding ID of the reference in db is generated and shown in a label. To get the next verse, i simply increment the ID by 1, then retrieve the associated record. To get the previous verse, I decrement the ID by 1, and retrieve the associated record

 Dim NewT_Counter As Integer = 0  
    Dim NewT_ID As Integer  
    Dim NewT_IncrementedID As Integer  
  
    Dim OldT_Counter As Integer = 0  
    Dim OldT_ID As Integer  
    Dim OldT_IncrementedID As Integer  
  
    Dim BothT_Counter As Integer = 0  
    Dim BothT_ID As Integer  
    Dim BothT_IncrementedID As Integer  
  
 'A. forward track verse (forward button)  
  
    Private Sub BtnOfflineBibleForwardTrack_Click(sender As Object, e As EventArgs) Handles BtnOfflineBibleForwardTrack.Click  
  
        '1. new testamneNt   
          
 If RadiobuttonNewTestament.checked = true and RadiobuttonOldTestament.checked = false then  
  
            If ComboBoxSearchBible.Text = "Revelation 22:21" And LblBibleIDNewtestament.Text = "7957" Then  'last reference in the bible  
                'do nothing  
            Else  
  
                'a.LblBibleIDNewtestament.Text Convert from String to Integer, then increment obtained number by 1  
  
                NewT_Counter += 1  
                NewT_ID = LblBibleIDNewtestament.Text  
                NewT_IncrementedID = NewT_ID + NewT_Counter  
                LblBibleIDNewtestamentIncrement.Text = NewT_IncrementedID  
  
                'b. submit value of LblBibleIDNewtestamentIncrement.Text, being ID into db to search and produce its corresponding bible verse  
  
                '--> firstly, find out if ID as present in LblBibleIDNewtestamentIncrement.text, is in database   
  
                connectionofflinebible1.Open()  
                Dim command As OleDbCommand = New OleDbCommand()  
                command.Connection = connectionofflinebible1  
                command.CommandText = "Select * from NewTestament where ID=" & LblBibleIDNewtestamentIncrement.Text & ""  
  
                '--> Notify and Prevent duplicate entries in database  
  
                Dim reader As OleDbDataReader = command.ExecuteReader()  
                Dim count As Integer = 0  
  
                While reader.Read()  
                    count = count + 1  
                End While  
  
                connectionofflinebible1.Close()  
  
                '--> if the value of LblBibleIDNewtestamentIncrement.text in is in database, then fetch its corresponding value i.e. BibleReadings, and add to label (LblBibleVerses2)  
  
                If count = 1 Then  
  
                    connectionofflinebible1.Open()  
  
                    command.Connection = connectionofflinebible1  
                    command.CommandText = "Select BibleReadings from NewTestament where ID =" & LblBibleIDNewtestamentIncrement.Text & ""   
                    Dim dr As OleDbDataReader = command.ExecuteReader()  
  
                    While dr.Read()  
                        LblBibleVerses2.Text = dr("BibleReadings").ToString()  
                    End While  
  
                    dr.Close()  
  
                    connectionofflinebible1.Close()  
  
                Else  
                    MessageBox.Show("Sorry, this entry does not exist in database. Please ensure your entry is correctly spelt.")  
                End If  
  
                '--> if the value of LblBibleIDNewtestamentIncrement.text in is in database, then fetch its corresponding value i.e. BibleReference, and add to ComboBoxSearchBible  
  
                If count = 1 Then  
  
                    connectionofflinebible1.Open()  
  
                    command.Connection = connectionofflinebible1  
                    command.CommandText = "Select BibleReference from NewTestament where ID =" & LblBibleIDNewtestamentIncrement.Text & ""  
                    Dim dr2 As OleDbDataReader = command.ExecuteReader()  
  
                    While dr2.Read()  
                        ComboBoxSearchBible.Text = dr2("BibleReference").ToString()  
                    End While  
  
                    dr2.Close()  
  
                    connectionofflinebible1.Close()  
  
                Else  
                    MessageBox.Show("Sorry, this entry does not exist in database. Please ensure your entry is correctly spelt.")  
                End If  
  
            End If  
 End If  
          
        '2. old testament   
          
 If RadiobuttonNewTestament.checked = false and RadiobuttonOldTestament.checked = true then  
  
            If ComboBoxSearchBible.Text = "Malachi 4:6" And LblBibleIDOldtestament.Text = "23146" Then  'last reference in the bible  
                'do nothing  
            Else  
  
                'a. LblBibleIDNewtestament.Text Convert from String To Integer, then increment obtained number by 1  
  
                OldT_Counter += 1  
                OldT_ID = LblBibleIDOldtestament.Text  
                OldT_IncrementedID = OldT_ID + OldT_Counter  
                LblBibleIDOldtestamentIncrement.Text = OldT_IncrementedID  
  
                'b. submit value of LblBibleIDNewtestamentIncrement.Text, being ID into db to search and produce its corresponding bible verse  
  
                '--> firstly, find out if ID as present in LblBibleIDNewtestamentIncrement.text, is in database   
  
                connectionofflinebible1.Open()  
                Dim command As OleDbCommand = New OleDbCommand()  
                command.Connection = connectionofflinebible1  
                command.CommandText = "Select * from OldTestament where ID=" & LblBibleIDOldtestamentIncrement.Text & ""   
  
                '--> Notify and Prevent duplicate enteries in database  
  
                Dim reader As OleDbDataReader = command.ExecuteReader()  
                Dim count As Integer = 0  
  
                While reader.Read()  
                    count = count + 1  
                End While  
  
                connectionofflinebible1.Close()  
  
                '--> if the value of LblBibleIDNewtestamentIncrement.text in is in database, then fetch its corresponding value i.e. BibleReadings, and add to label (LblBibleVerses2)  
  
                If count = 1 Then  
  
                    connectionofflinebible1.Open()  
  
                    command.Connection = connectionofflinebible1  
                    command.CommandText = "Select BibleReadings from OldTestament where ID =" & LblBibleIDOldtestamentIncrement.Text & ""  
                    Dim dr As OleDbDataReader = command.ExecuteReader()  
  
                    While dr.Read()  
                        LblBibleVerses2.Text = dr("BibleReadings").ToString()  
                    End While  
  
                    dr.Close()  
  
                    connectionofflinebible1.Close()  
  
                Else  
                    MessageBox.Show("Sorry, this entry does not exist in database. Please ensure your entry is correctly spelt.")  
                End If  
  
                '--> if the value of LblBibleIDNewtestamentIncrement.text in is in database, then fetch its corresponding value i.e. BibleReference, and add to ComboBoxSearchBible  
  
                If count = 1 Then  
  
                    connectionofflinebible1.Open()  
  
                    command.Connection = connectionofflinebible1  
                    command.CommandText = "Select BibleReference from OldTestament where ID =" & LblBibleIDOldtestamentIncrement.Text & ""   
                    Dim dr2 As OleDbDataReader = command.ExecuteReader()  
  
                    While dr2.Read()  
                        ComboBoxSearchBible.Text = dr2("BibleReference").ToString()  
                    End While  
  
                    dr2.Close()  
  
                    connectionofflinebible1.Close()  
  
                Else  
                    MessageBox.Show("Sorry, this entry does not exist in database. Please ensure your entry is correctly spelt.")  
                End If  
  
            End If  
 End If  
              
    End Sub  

'B. backward track verse (previous button)  

Private Sub BtnOfflineBibleBackwardTrack_Click(sender As Object, e As EventArgs) Handles BtnOfflineBibleBackwardTrack.Click  
  
    '1. new testament   
  
 If RadiobuttonNewTestament.checked = true and RadiobuttonOldTestament.checked = false then  
  
        If ComboBoxSearchBible.Text = "Matthew 1:1" And LblBibleIDNewtestament.Text = "1" Then  'first reference in the bible  
            'do nothing  
        Else  
  
            'a.LblBibleIDNewtestament.Text Convert from String To Integer, then increment obtained number by 1  
  
            NewT_Counter -= 1  
            NewT_ID = LblBibleIDNewtestament.Text  
            NewT_IncrementedID = NewT_ID + NewT_Counter  
            LblBibleIDNewtestamentIncrement.Text = NewT_IncrementedID 'decrement by 1  
  
            'b. submit value of LblBibleIDNewtestamentIncrement.Text, being ID into db to search and produce its corresponding bible verse  
  
            '--> firstly, find out if ID as present in LblBibleIDNewtestamentIncrement.text, is in database   
  
            connectionofflinebible1.Open()  
            Dim command As OleDbCommand = New OleDbCommand()  
            command.Connection = connectionofflinebible1  
            command.CommandText = "Select * from NewTestament where ID=" & LblBibleIDNewtestamentIncrement.Text & ""  
  
            '--> Notify and Prevent duplicate enteries in database  
  
            Dim reader As OleDbDataReader = command.ExecuteReader()  
            Dim count As Integer = 0  
  
            While reader.Read()  
                count = count + 1  
            End While  
  
            connectionofflinebible1.Close()  
  
            '--> if the value of LblBibleIDNewtestamentIncrement.text in is in database, then fetch its corresponding value i.e. BibleReadings, and add to label (LblBibleVerses2)  
  
            If count = 1 Then  
  
                connectionofflinebible1.Open()  
  
                command.Connection = connectionofflinebible1  
                command.CommandText = "Select BibleReadings from NewTestament where ID =" & LblBibleIDNewtestamentIncrement.Text & ""   
                Dim dr As OleDbDataReader = command.ExecuteReader()  
  
                While dr.Read()  
                    LblBibleVerses2.Text = dr("BibleReadings").ToString()  
                End While  
  
                dr.Close()  
  
                connectionofflinebible1.Close()  
  
            Else  
                MessageBox.Show("Sorry, this entry does not exist in database. Please ensure your entry is correctly spelt.")  
            End If  
  
            '--> if the value of LblBibleIDNewtestamentIncrement.text in is in database, then fetch its corresponding value i.e. BibleReference, and add to ComboBoxSearchBible  
  
            If count = 1 Then  
  
                connectionofflinebible1.Open()  
  
                command.Connection = connectionofflinebible1  
                command.CommandText = "Select BibleReference from NewTestament where ID =" & LblBibleIDNewtestamentIncrement.Text & ""   
                Dim dr2 As OleDbDataReader = command.ExecuteReader()  
  
                While dr2.Read()  
                    ComboBoxSearchBible.Text = dr2("BibleReference").ToString()  
                End While  
  
                dr2.Close()  
  
                connectionofflinebible1.Close()  
  
            Else  
                MessageBox.Show("Sorry, this entry does not exist in database. Please ensure your entry is correctly spelt.")  
            End If  
  
        End If  
 End If  
     
    '2. old testament   
      
 If RadiobuttonNewTestament.checked = False and RadiobuttonOldTestament.checked = True then  
  
        If ComboBoxSearchBible.Text = "Genesis 1:1" And LblBibleIDOldtestament.Text = "1" Then  'last reference in the bible  
            'do nothing  
        Else  
  
            'a. LblBibleIDNewtestament.Text Convert from String To Integer, then increment obtained number by 1  
  
            OldT_Counter -= 1  
            OldT_ID = LblBibleIDOldtestament.Text  
            OldT_IncrementedID = OldT_ID + OldT_Counter  
            LblBibleIDOldtestamentIncrement.Text = OldT_IncrementedID  
  
            'b. submit value of LblBibleIDNewtestamentIncrement.Text, being ID into db to search and produce its corresponding bible verse  
  
            '--> firstly, find out if ID as present in LblBibleIDNewtestamentIncrement.text, is in database   
  
            connectionofflinebible1.Open()  
            Dim command As OleDbCommand = New OleDbCommand()  
            command.Connection = connectionofflinebible1  
            command.CommandText = "Select * from OldTestament where ID=" & LblBibleIDOldtestamentIncrement.Text & ""  
  
            '--> Notify and Prevent duplicate entries in database  
  
            Dim reader As OleDbDataReader = command.ExecuteReader()  
            Dim count As Integer = 0  
  
            While reader.Read()  
                count = count + 1  
            End While  
  
            connectionofflinebible1.Close()  
  
            '--> if the value of LblBibleIDNewtestamentIncrement.text in is in database, then fetch its corresponding value i.e. BibleReadings, and add to label (LblBibleVerses2)  
  
            If count = 1 Then  
  
                connectionofflinebible1.Open()  
  
                command.Connection = connectionofflinebible1  
                command.CommandText = "Select BibleReadings from OldTestament where ID =" & LblBibleIDOldtestamentIncrement.Text & ""   
                Dim dr As OleDbDataReader = command.ExecuteReader()  
  
                While dr.Read()  
                    LblBibleVerses2.Text = dr("BibleReadings").ToString()  
                End While  
  
                dr.Close()  
  
                connectionofflinebible1.Close()  
  
            Else  
                MessageBox.Show("Sorry, this entry does not exist in database. Please ensure your entry is correctly spelt.")  
            End If  
  
            '--> if the value of LblBibleIDNewtestamentIncrement.text in is in database, then fetch its corresponding value i.e. BibleReference, and add to ComboBoxSearchBible  
  
            If count = 1 Then  
  
                connectionofflinebible1.Open()  
  
                command.Connection = connectionofflinebible1  
                command.CommandText = "Select BibleReference from OldTestament where ID =" & LblBibleIDOldtestamentIncrement.Text & ""   
                Dim dr2 As OleDbDataReader = command.ExecuteReader()  
  
                While dr2.Read()  
                    ComboBoxSearchBible.Text = dr2("BibleReference").ToString()  
                End While  
  
                dr2.Close()  
  
                connectionofflinebible1.Close()  
  
            Else  
                MessageBox.Show("Sorry, this entry does not exist in database. Please ensure your entry is correctly spelt.")  
            End If  
  
        End If  
 End if  
              
End Sub  

To return the counter to zero, add the following code in the button event that calls the container control that holds bible reference controls.

NewT_Counter = 0          
    OldT_Counter = 0         
    BothT_Counter = 0