Referring to the previous record in a continuous form

[Salty Mariner]

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.

I am indeed familiar with building functions with arguments My Mercator Sailing Functions Take 4 arguments for Course and 5 arguments for distance. Those 4 textboxes actually feed those 4 arguments for Course and then I need those 4 arguments Plus the course to obtain the distance. While it may seem redundant the formulas that make it all work require it. The methodology of it all was lifted from "Bowditch - The American Practical Navigator". I just translated their formulas to VBA. As I said this was a proof of concept and I can now work on streamlining things and perhaps making it all more efficient. I don't know If I can make this ALL work in code as I think I need a textbox or some control from which to call the code that is specific to that record. I'll be continuing to play with this and refine it.

This whole Navigation project started for me over 20 years ago. First in Excel formulas, Then to Excel with VBA functions, and then I modified my functions for Access. 20 years ago I had a voyage planner working in Excel. I wanted to try my hand at making this work in Access.

[Salty Mariner]

Hello everyone.

I thought I would update people here who might be following this thread. With a good deal of searching I found some module based code elsewhere written 13 years ago by a forum user named MajP. Essentially what it does is if you have a sort field Like my form does which is essentially what my LegNo field is the code will swap the value of the current sort number with that of the previous one and then requery and do a FindRecord. It does the reverse to perform a move down. This was a lifesaver for me as I might have never come up with this on my own. Examining the code is a great learning experience as well. Form the little bit I have played with it it seems to work. I need to enter some more data and see if I can find any bugs which I assume would occur when reaching BOF or EOF.

Here is the code:

Public Sub SetInitialSort(SortField, frm)
  Dim rs As DAO.Recordset
  Set rs = frm.Recordset
  Do While Not rs.EOF
    rs.Edit
    rs.Fields(SortField) = rs.AbsolutePosition + 1
    rs.Update
    rs.MoveNext
  Loop
  rs.MoveFirst
End Sub

Public Sub moveSortUp(SortField, frm As Access.Form)
  Dim rsClone As DAO.Recordset
  Dim lngNewSort As Long
  Dim lngOldSort As Long
  Set rsClone = frm.RecordsetClone
  rsClone.AbsolutePosition = frm.Recordset.AbsolutePosition
  If Not (IsNull(rsClone.Fields(SortField)) Or rsClone.AbsolutePosition <= 0) Then
    lngOldSort = rsClone.Fields(SortField)
    lngNewSort = lngOldSort - 1
    rsClone.Edit
    rsClone.Fields(SortField) = lngNewSort
    rsClone.Update
    rsClone.MovePrevious
    rsClone.Edit
    rsClone.Fields(SortField) = lngOldSort
    rsClone.Update
  frm.Requery
  frm.Recordset.FindFirst SortField & " = " & lngNewSort
  End If
End Sub

Public Sub MoveSortDown(SortField, frm As Access.Form)
  Dim rsClone As DAO.Recordset
  Dim lngNewSort As Long
  Dim lngOldSort As Long
  Set rsClone = frm.RecordsetClone
  rsClone.AbsolutePosition = frm.Recordset.AbsolutePosition
  If Not (IsNull(rsClone.Fields(SortField)) Or rsClone.AbsolutePosition >= rsClone.RecordCount - 1) Then
    lngOldSort = rsClone.Fields(SortField)
    lngNewSort = lngOldSort + 1
    rsClone.Edit
    rsClone.Fields(SortField) = lngNewSort
    rsClone.Update
    rsClone.MoveNext
    rsClone.Edit
    rsClone.Fields(SortField) = lngOldSort
    rsClone.Update
  frm.Requery
  frm.Recordset.FindFirst SortField & " = " & lngNewSort
  End If
End Sub

[Welshgasman]

Yes, he writes good classes as well. You should Google his FAYT classes.