Previous and Next Button function to VBA Data Entry form is not working

Question

Previous Record and Next Record sub routine is not working. I marked with 1 and 2. These two navigation bars (1&2) works on the what is entered on WaypointId.

Say for example, if I say waypoint id=1235, then next record should appear in a data entry form. My vba code is first search the row number of waypoint id in observation sheet and then I decrease the row number by 1 for displaying previous record and increase the row number by 1 for next record. Depends on the functionality it shows data in the Data Entry Form.

My VBA code is not working for those two things. Attach workbook with name Problem-1.xlsm See Navigation Control Module.

Sub FindRecord(WyPt)

    Dim Value As String
    WyPtRow = 0
    
    ReadRow = 2
    Value = Cells(ReadRow, 2)
    
    While Value <> ""
        
        If WyPt = Value Then
            
            WyPtRow = ReadRow
            Exit Sub
            
        End If
        
        ReadRow = ReadRow + 1
        Value = Cells(ReadRow, 2)
    Wend
    
End Sub

 Sub ViewPreviousRecord()

    Set DEFrm = Sheets("DataEntryForm")
    Set ObsData = Sheets("Observations")
    
    Dim WyPt As String
    
    WyPt = Trim(DEFrm.Cells(6, 2))
    
    Call FindRecord(WyPt)
                
    LastRow = WyPtRow - 1
    
    With DEFrm
    
        .Cells(6, 2).Value = ObsData.Cells(LastRow, 2).Value 'WaypointID
        .Cells(6, 4).Value = ObsData.Cells(LastRow, 3).Value 'ObsType
        
        .Cells(8, 2).Value = ObsData.Cells(LastRow, 4).Value 'Date
        .Cells(8, 4).Value = ObsData.Cells(LastRow, 5).Value  'LoggedBy
    End With
End Sub

Sub ViewNextRecord()

Set DEFrm = Sheets("DataEntryForm")
Set ObsData = Sheets("Observations")
Dim WyPt As String

WyPt = Trim(DEFrm.Cells(6, 2))

Call FindRecord(WyPt)
            
LastRow = WyPtRow + 1

With DEFrm

    .Cells(6, 2).Value = ObsData.Cells(LastRow, 2).Value 'WaypointID
    .Cells(6, 4).Value = ObsData.Cells(LastRow, 3).Value 'ObsType
    .Cells(35, 10).Value = ObsData.Cells(LastRow, 115)   'Photo4Desc

   
End With

End Sub

Answer 1

This is the most important procedure in your project.

Sub DisplayRecord(ByVal Rs As Long)
    ' 235
    
    Dim Arr         As Variant                  ' Data from row Rs in database
    Dim Target()    As String                   ' Dashboard addresses matching Arr
    Dim i           As Long                     ' loop counter: Arr(Index)
    
    ' cell addresses are aligned with column numbers in database (-2)
    Arr = "B6,D6,B8,D8,G6,H6,G7,H7,G8,H8,B11,C11,D11,E11,F11,G11,H11,I11"
    Arr = Arr & ",B14,C14,D14,E14,F14,G14,B17,C17,D17,E17,F17,G17"
    Arr = Arr & ",I14,J14,I15,J15,I16,J16,I17,J17,B20,C20,D20,E20,F20,G20"
    Arr = Arr & ",B23,C23,D23,E23,F23"
    Arr = Arr & ",I20,J20,K20,I21,J21,K21,I2,J22,K22,I23,J23,K23"
    Arr = Arr & ",B26,C26,D26,E26,F26,G26,H26,I26,J26,K26"
    Arr = Arr & ",B27,C27,D27,E27,F27,G27,H27,I27,J27,K27"
    Arr = Arr & ",B28,C28,D28,E28,F28,G28,H28,I28,J28,K28"
    Arr = Arr & ",B29,C29,D29,E29,F29,G29,H29,I29,J29,K29"
    Arr = Arr & ",B32,H32,I32,J32,H33,I33,J33,H34,I34,J34,H35,I35,J35"
    Target = Split(Arr, ",")
    
    With Sheets("Observations")
        Arr = .Range(.Cells(Rs, 1), .Cells(Rs, 115)).Value
    End With
    
    Application.ScreenUpdating = False      ' speed up execution
    For i = 2 To UBound(Arr, 2)               ' skip first database column
        Sheets("DataEntryForm").Range(Target(i - 2)).Value = Arr(1, i)
    Next i
    Application.ScreenUpdating = True
End Sub

It displays the data of the row Rs given to it as an argument. You already have a function that finds the row number needed by the above procedure. Below please find an improvement.

Function RecordRow(ByVal WyPt As String) As Long
    ' 235
    ' return the row number where WyP was found or 0

    Dim Fnd     As Range
    
    With Worksheets("Observations")
        Set Fnd = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))
        Set Fnd = Fnd.Find(WyPt, , LookIn:=xlValues, lookat:=xlWhole)
        If Not Fnd Is Nothing Then
            RecordRow = Fnd.Row
        End If
    End With
End Function

The deal is simple: you give the Waypoint ID and receive the row number where it was found. If it isn't found the function returns 0, and that is how you avoid crashes.

With these two procedures in place you can easily call up the first and the last records.

Sub ViewFirstRecord()
    ' 235
    DisplayRecord 2
End Sub

Sub ViewLastRecord()
    ' 235
    
    With Worksheets("Observations")
        DisplayRecord .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
End Sub

The next and previous records are just a matter of finding the row number and displaying its data.

Sub ViewNextRecord()
    ' 235

    Dim Rs          As Long                 ' data source row
    
    Rs = RecordRow(Trim(Cells(6, 2).Value)) + 1
    If Rs > 1 Then
        With Worksheets("Observations")
            If Rs <= .Cells(.Rows.Count, "A").End(xlUp).Row Then
                DisplayRecord Rs
            Else
                MsgBox "No more records to show.", vbInformation, "Last record"
            End If
        End With
    End If
End Sub

Sub ViewPreviousRecord()
    ' 235

    Dim Rs          As Long                 ' data source row
    
    Rs = RecordRow(Trim(Cells(6, 2).Value)) - 1
    If Rs > 1 Then
        DisplayRecord Rs
    Else
        MsgBox "No more records to show.", vbInformation, "First record"
    End If
End Sub

Answer 2

If that's the whole code, you may be finding a problem with scope. It seems ViewPreviousRecord() is not able to see WyPtRow. You can try adding dim WyPtRow Before the Sub FindRecord(WyPt) definition.

Another implementation would be changing the Sub for a function, and returning the WyPtRow value.