[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
> '===================================
>
>  ----- 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
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>