[AccessD] Next, Prev

    You are most welcome Kath! The function was custom built for you.

Best wishes,
A.D. Tejpal
-------------

  ----- Original Message ----- 
  From: Kath Pelletti 
  To: Access Developers discussion and problem solving 
  Sent: Sunday, September 07, 2008 15:25
  Subject: Re: [AccessD] Next, Prev


  Thanks so much everyone for your replies. I actually would never have 
  thought that > could even be used for strings as per Gustav/Stuart's 
  examples, so I have learnt something from that.

  Will be interesting to test the speed but given that the table will never 
  have more than 500 recs I don't imagine that's a big deal.

  A.D - your solution posted below is exactly what I was looking for - already 
  put it in place to be further enhanced tomorrow morning. All I need to work 
  out tomorrow is how to repeat it (ie. if user then moved fwd again then the 
  new position is 2 recs ahead, and if they do it again, 3 recs etc.) So more 
  playing tomorrow -

  Did you already have this code, or just wrote it to post?
  Either way you have helped me enormously.

  kath

  ----- Original Message ----- 
  From: "A.D.Tejpal" <adtp at airtelmail.in>
  To: "Access Developers discussion and problem solving" 
  <accessd at databaseadvisors.com>
  Sent: Saturday, September 06, 2008 5:12 PM
  Subject: Re: [AccessD] Next, Prev

  > Kath,
  >
  >    If the name field (let us call it PName) is likely to have embedded 
  > quotes, use of domain aggregate functions would generate error unless the 
  > value is duly fixed before concatenation.
  >
  >    Gustav - You might like to verify again - Use of DMin() with "ID" as 
  > the first argument would lead to inconsistent results, returning the 
  > record with lowest ID (out of all subsequent records), not necessarily the 
  > very next record. Perhaps you intended name field as the first argument.
  >
  >    As an alternative, function Fn_GetNextID() as given below, can be 
  > considered. It also circumvents the problem associated with embedded 
  > quotes in PName field.
  >
  > Best wishes,
  > A.D. Tejpal
  > -------------
  >
  > ' Code in general module
  > '===================================
  > Function Fn_GetNextID(frm As Form) As Variant
  >    Dim rst As DAO.Recordset, LastRec As Long
  >
  >    Fn_GetNextID = Null     ' Default
  >    If frm.NewRecord = False Then
  >        Set rst = frm.RecordsetClone
  >
  >        rst.MoveLast
  >        LastRec = rst.AbsolutePosition + 1
  >
  >        If frm.CurrentRecord < LastRec Then
  >            ' Grab inf from next record
  >            ' (Abs Pos is zero based)
  >            rst.AbsolutePosition = frm.CurrentRecord
  >            Fn_GetNextID = rst.Fields("ID")
  >        End If
  >
  >        rst.Close
  >        Set rst = Nothing
  >    End If
  > End Function
  > '===================================