Moving to previous record in terms of sorted value?

[(PeteCresswell)]

Tomorrow is Show-And-Tell time.... I can probably put it off
until after lunch, but I'd really like to show the user something
in time for them to organize their observations/change requests
in time for me to work through the weekend.


I'm in a subform.

User has just updated a date field.

Subform's .Recordsource is a query, sorted by descending date.

I want to determine what the date prior to the one the user is
editing is so I can sum up a bunch of daily accruals between the
two dates.

The obvious .MoveNext doesn't seem tb doing the job. It's like it
doesn't know that the records are sorted by descending date.

I can't do a .Seek or .FindFirst because I don't know what the
previous date is.... could be yesterday, could be a year ago...
besides, if I could do one of those I'd already know what I want
to know... -)

Maybe something with .BookMark?

e.g.
-----------------------------------------
Set myRS = Me.RecordSetClone

With myRS
?.... .MoveNext isn't getting it....
End With

 

[Tom van Stiphout]

On Thu, 10 Jan 2008 21:51:39 -0500, "(PeteCresswell)" <[email protected]>
wrote:

You a teacher?

Unless I am really mistaken, the RecordsetClone is sorted the same way
as the (sub-)form. So your code should have worked if you added the
bookmark:
with me.recordsetclone
.bookmark = me.bookmark 'go to selected row
if not .eof then .movenext


An altermative is to use a query. Something like:
select max(SomeDate)
from SomeTable
where SomeDate <
Forms!SomeForm!SomeSubformControl.Form!SomeControl

-Tom.

 

[Chuck]

I think Tom is on exactly the right track.
Normally, think of your data as unsorted, even if you are displaying it
as sorted. (Unless there are keys involved.)
So, it helps to think of your data as in a random order.
You need to write a query to do what you are asking.

 

[(PeteCresswell)]

Per Tom van Stiphout:

You a teacher?

No... that was a little poetic license. I had to show it to a
fund manager.

An altermative is to use a query. Something like:
select max(SomeDate)
from SomeTable
where SomeDate <
Forms!SomeForm!SomeSubformControl.Form!SomeControl

That's where I went - and taking Chuck's point about thinking
"random".


Thanks.

 

[Tom van Stiphout]

Chuck was right that table data can be considered randomly ordered if
you don't have an OrderBy, but that was not your situation. You had a
subform, and the RecordsetClone is always ordered the same way as one
sees in the form.

-Tom.