Assistance with a Function

[Salty Mariner]

I have a Function (actually two similar ones) that are not giving me the desired results.



Here is the code:

Private Function GetNextLat()
Dim strNextLat As String
Dim rst As DAO.Recordset


    Set rst = Me.RecordsetClone
    
    With rst
        If Not .EOF Then
            .FindFirst ("LegNo = " & Nz(Me.LegNo, 0) + 1)
            strNextLat = rst![Latitude]
        End If
    End With
    
    GetNextLat = strNextLat
    
    rst.Close
    Set rst = Nothing
End Function

It's purpose is to look ahead on a continuous form and grab the contents of the next record...In this case a Latitude.(I have another for Longitude) Everything works great until I get to the last record. Even though I have checked for EOF to exclude the last record It seems to be returning a zero and then adding 1 which takes me full circle to record 1. I can see where my logic is breaking but I'm striking out trying to fix this. I thought that by checking for EOF It would stop and I could deal with it and return an empty string.

Suggestions?

[June7]

The last record is not EOF. So, code attempts to pull from next record which does not exist. Not sure Nz() is needed.

.FindFirst ("LegNo = " & Me.LegNo + 1)

If that doesn't work, try:

.FindFirst ("LegNo = " & Nz(Me.LegNo, -1) + 1)

Should probably use NoMatch property after FindFirst

If Not .NoMatch Then strNextLat = ![Latitude]

Could use DLookup() in textbox.

=DLookup("Latitude", "tablename", "LegNo=" & [LegNo] + 1)

[Salty Mariner]

This

.FindFirst ("LegNo = " & Me.LegNo + 1)

and

If Not .NoMatch Then strNextLat = ![Latitude]

worked like a charm!

Thank you so much June.