Referring to the previous record in a continuous form

[Salty Mariner]

I know this has likely been asked may times before. I have been searching for a simple solution but have not yet found anything that was not overly complex. I have a form with a subform. The subform contains geographical waypoints in Latitude and Longitude. I have Functions that break the Lat/Lon strings into decimal degrees and functions to compute the course and distance. Those functions require Lat1, Lon1, and Lat2, Lon2. I have a rough draft of my form below.





Starting at row 2 in the continuous form and continuing for as many lines as necessary I want to get the values of the Latitude and Longitude in row 1 and assign them to Lat1 and Lon 1. to use in my function. I would like to use the function in each textbox as a calculated control. and recreate this in the form (and future Report) as needed and not store the results in the DB as I want to be able to recalculate when the user decides to insert a waypoint or move a waypoint up or down the waypoint list.

I am looking for something simple, and robust.... Hopefully that is not too tall of an order. Any suggestions?

[June7]

Actually, that is quite a tall order. Access does not easily deal with pulling data from another record of same table, especially on forms. This sort of data manipulation is best handled in an output process, such as report design.

Review http://allenbrowne.com/subquery-01.html#AnotherRecord

If you just want the minmum lat and lon values from the form's recordset, that can be done with aggregation function in textboxes:
=Min(Latitude)
=Min(Longitude)

How would a waypoint be moved up or down?

[Salty Mariner]

Actually, that is quite a tall order. Access does not easily deal with pulling data from another record of same table, especially on forms. This sort of data manipulation is best handled in an output process, such as report design.

Review http://allenbrowne.com/subquery-01.html#AnotherRecord

How would a waypoint be moved up or down?

I am presently assigning Waypoint Numbers when adding them using:

Forms!frmVoyPlan!subVpLegs.Form.txtLegNo.Value = DCount("Wpt_ID", "tblVpLegs", "VP_ID = Forms!frmVoyPlan!subVpLegs.Form.VP_ID") + 1

I was thinking about writing code to determine the leg number of a Waypoint and then Incrementing its value by -1 and incrementing the previous value by +1 and then refreshing or re-querying when the user clicks the "Move Up" Button and the reverse for the "Move Down" Button. Of course I have not yet gotten that far.

My Waypoints are stored in a Waypoints table and I am using a join table that looks like this:



As I said this is all kind of a rough draft and I am still hashing a lot of the design out. All is subject to change as I discover what works and what doesn't.

[Welshgasman]

I would look at processing the recordsetclone, but if controls are unbound in a continuous form, then they will all be the same I believe?
Might need to store in a temp table so they can be bound?

[Salty Mariner]

Oh, and I did find Allan Browns page on subqueries in my search. It looked complicated. I was wondering if I could do something with a Dlookup that uses "LegNo - 1" or something similar as it's criteria or perhaps a querydef that accomplishes something similar. You are correct about it being a tall order because I have not yet found a simple solution to this and it seems like there should be something.... Somewhere.

[Salty Mariner]

I would look at processing the recordsetclone, but if controls are unbound in a continuous form, then they will all be the same I believe?
Might need to store in a temp table so they can be bound?

Something like RecordsetClone.MovePrevious and then assign values to variables?

[Welshgasman]

Something like RecordsetClone.MovePrevious and then assign values to variables?

No I was thinking more walking the recordsetclone with move next and storing the previous records values.

Could be done with a query (I think) using Max() < your ID and a function with those Dmax values perhaps. Beyond my expertise though.

[Micron]

Off the top of my head, and notwithstanding factors not revealed:

Assuming first record ID is 1,
Select a record for moving up: get that record autonum ID (9), subtract 2 (= 7).
Copy records 1 to 7 to staging table, then append ID 9, then append 8 then append the rest
Overwrite original records in form table from lowest to highest ID using the staging table.
Requery form.

Moving down would be adding 2.

[Salty Mariner]

Off the top of my head, and notwithstanding factors not revealed:

Assuming first record ID is 1,
Select a record for moving up: get that record autonum ID (9), subtract 2 (= 7).
Copy records 1 to 7 to staging table, then append ID 9, then append 8 then append the rest
Overwrite original records in form table from lowest to highest ID using the staging table.
Requery form.

Moving down would be adding 2.

It was my thought to stay away from autonumbers for Waypoint Leg numbers. tblVpLegs has an Autonumber as its PK but I am using my leg number for that particular VP_ID as a navigation sequence. and my thought was by manipulating the LegNo and then resorting I change navigation sequence. I would have to do this for Move Up, Move Down, and for Waypoint Inserts. Once I get the renumbering mechanism working this should function as intended.

But back to the RecordSet Clone method and getting the previous records values. I'm thinking I could use the Recorset clone and say I was at LegNo. 10. I could then use the cloned recordset to arrive at the record where the LegNo was equal to (10-1) or 9, and then assign the corresponding Lat/Lon values to variables and use them in my functions. to calculate course and distance. After all the whole purpose of the LegNo field is to provide for a navigation sequence....go from this position to the that one and then another and so on.

[Micron]

To each their own I guess. I long ago stopped thinking that using real data as primary keys was ok. I've never found an instance where an AN id was a problem, but sure did come across situations where not having one was a problem. Perhaps for your situation something else would work better.

[Salty Mariner]

To each their own I guess. I long ago stopped thinking that using real data as primary keys was ok. I've never found an instance where an AN id was a problem, but sure did come across situations where not having one was a problem. Perhaps for your situation something else would work better.

I have a Primary Key in tblVpLegs and it is an AutoNumber. The table is an intermediate join table because I have a table for Waypoints which can be re-used. I have a table for Voyages. Each voyage has many legs, the legs use Waypoints which could conceivably be used more than once in any particular voyage. Aside from maintaining referential integrity I could not figure out how one would use the PK to determine a navigation sequence which is why I created a field called LegNo so that there would be a logical order of navigation from one point to another. That order can be changed by manipulating the value of the leg number, while ensuring it is sequential, and without duplicates. I'm not trying to start a debate or an argument, but rather explain what the application is supposed to do which is to perform navigation calculations and that is why I created a field to control the navigation sequence. I realize that it is not a common thing that people do with Access but I think it should be doable.

[June7]

Could do DLookup if you are certain there will never be any gaps in LogNo sequence.

As for RecordsetClone, again, if there a no gaps in sequence could use FindFirst with criteria of LogNo = LogNo - 1, otherwise use FindFirst to go to current record then MovePrevious.

VBA would have to be a Function that is called from textbox because VBA setting Value property of UNBOUND textbox will show the same value for all records.

[Salty Mariner]

Could do DLookup if you are certain there will never be any gaps in LogNo sequence.

As for RecordsetClone, again, if there a no gaps in sequence could use FindFirst with criteria of LogNo = LogNo - 1, otherwise use FindFirst to go to current record then MovePrevious.

VBA would have to be a Function that is called from textbox because VBA setting Value property of UNBOUND textbox will show the same value for all records.

Thank you June,

That gives me some food for thought and some ideas to begin toying around with. I'm sure I will probably try and abandon a couple of methods but I think I'm pointing in the right direction now. I appreciate everyone's thoughts on this as it forces me to consider things I might have otherwise ignored.

[Salty Mariner]

I'm happy to report that I have had some success... at least enough for a proof of concept at this point. I created the following 2 functions.

Private Function GetPreviousLat()
Dim strPrevLat As String
Dim rst As DAO.Recordset


    Set rst = Me.RecordsetClone
    
    With rst
        If Not .BOF Then
            .FindFirst ("LegNo = " & Me.LegNo - 1)
            strPrevLat = rst![Latitude]
        End If
    End With
    
    GetPreviousLat = strPrevLat
    
    rst.Close
    Set rst = Nothing
End Function
Private Function GetPreviousLon()
Dim strPrevLon As String
Dim rst As DAO.Recordset


    Set rst = Me.RecordsetClone
    
    With rst
        If Not .BOF Then
            .FindFirst ("LegNo = " & Me.LegNo - 1)
            strPrevLon = rst![Longitude]
        End If
    End With
    
    GetPreviousLon = strPrevLon
    
    rst.Close
    Set rst = Nothing
End Function

I placed 4 textboxes on my subform for Lat1, Lon1, Lat2, and Lon2.

Lat1, and Lon1 use the functions above Inside of another function that converts the string to decimal degrees.

=LatLonToDegs(GetPreviousLat())

Lat2, and Lon2 get their values from the latitude and longitude textboxes and are also converted to decimal degrees.

I have a function for Course by Mercator Sailing and a function for Distance by Mercator Sailing. and they are churning out the correct and expected values.

I can either hide the 4 textboxes behind the other controls or find another way to get that same data into the course and distance functions. At present it's kind of a kludge but at least it's a proof of concept that I can make this all work. It's progress and I can make it look pretty later.

[June7]

Those two functions could be modified to one function. Are you familiar with how to build functions with arguments?

If users don't need to see these calcs, textboxes can be not Visible. In fact, if these calcs are only necessary for further calcs, possibly don't even need textboxes. Call the function(s) from other function, use arguments.

Should probably test for EOF not BOF.