# Visual Basic > Visual Basic .NET >  Record Navigation in AccessDB on UserForm

## kevwe234@gmail.com

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
```

----------


## jmcilhinney

Your verses should have sequential IDs in the database. When the user selects a verse by book, chapter and verse, you the relevant verse, including the ID. To get the next verse, you simply increment the ID and retrieve that record. To get the previous verse, you decrement the ID.

To allow for the your verse IDs to have values missing in the sequence, you might make the query relative to the current ID, e.g.

sql Code:
SELECT TOP 1 * FROM MyTable WHERE ID > @ID
That will get the first record where the ID is greater than the current ID, even if it's more than 1 greater.

----------


## vbdotnut

How much data are we talking here? I cant see it being all that much. Reading your code you have lots of runaround going on. Unless there is like a billion rows, You will be better off bringing in the entire dataset and use a bindingsource. From there you have all sorts of options to navigate, find and filter.
I believe this bit of code you have:



> ```
>              While reader.Read()
>                  count = count + 1
>              End While
> ```


Could use the same open connection/command. It looks like you are just checking if there is data returned from your query. Instead do something like this


```
If MyDataReader.HasRows Then
    'Do something with reader
```

Otherwise you are traversing and exhausting the data before you are using it logically

----------


## jmcilhinney

> How much data are we talking here?


There's a fair few verses in the bible.

----------


## kevwe234@gmail.com

Thank you to  jmcilhinney. 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
```

----------

