[AccessD] Next, Prev

Hi A.D.

Good catch! You are right as always. I had a general situation in mind.

The method using the RecordsetClone is indeed preferable - and probably superior in speed too.

/gustav

>>> adtp at airtelmail.in 06-09-2008 09:12 >>>
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
'===================================

  ----- Original Message ----- 
  From: Gustav Brock 
  To: accessd at databaseadvisors.com 
  Sent: Friday, September 05, 2008 12:07
  Subject: Re: [AccessD] Next, Prev


  Hi Kath

  I guess you could and should use DMin without any issues:

  NextItemID = DMin("ID","qryMyFormQuery","[Name] > '" & [Name] & "'")

  It will sort "correctly", that is the default sorting of your database. If you need any other sorting the plot thickens.

  /gustav


  >>> kp at sdsonline.net 05-09-2008 04:33 >>>
  Your solution is nice and simple but think it is going to get me into all 
  kinds of problems when I hit names (workplacenames) with strange characters 
  and apostrophes in them.

  And if I use code to handle the apostrophes (replace them etc.) then the 
  statement won't work.

  hhmm..

  ----- Original Message ----- 
  From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
  To: "Access Developers discussion and problem solving" 
  <accessd at databaseadvisors.com>
  Sent: Friday, September 05, 2008 12:01 PM
  Subject: Re: [AccessD] Next, Prev


  > NextItemID =
  > Dlookup("ID","qryMyFormQuery","[Name] > " & [Name])
  >
  >
  > Incidentally, it's very bad practice to use "Name" as a field name - it  can bite you in all sorts
  > of ways.
  >
  >
  > On 5 Sep 2008 at 10:51, Kath Pelletti wrote:
  >
  >> Must be the week for working on Next, Prev functions on the list............
  >>
  >> This is a bit of an odd situation, but what I have a continuous form 
  >> which displays names and ID's. It's source is a query which is sorted by 
  >> the Name field.
  >>
  >> In another area of the application, I need to be able to know the ID of 
  >> the 'next' rec in that continuous form.
  >>
  >> eg. Continuous form shows:
  >> Name                 ID
  >> AAB                    90312
  >> AAC                    23
  >> AAD                    845
  >> AAE                     32
  >>
  >> If I have a form for AAB open, I need to get the ID of the next item AAC, 
  >> which is 23.
  >>
  >> Maybe I am making this too hard, but the fact that the list is 
  >> alphabetically sorted has me thrown...............
  >>
  >> tia
  >> Kath
  >>
  >> ______________________________________
  >> Kath Pelletti
  >> Software Design and Solutions Pty Ltd
  >> kp at sdsonline.net