Moving to previous record in terms of sorted value?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

(PeteCresswell) - 11 Jan 2008 02:51 GMT

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
-----------------------------------------

Signature

PeteCresswell

Reply to this Message

Tom van Stiphout - 11 Jan 2008 03:29 GMT

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.

>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
[quoted text clipped - 29 lines]
>End With
>-----------------------------------------

Reply to this Message

Chuck - 11 Jan 2008 03:44 GMT

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.

> You a teacher?
>
[quoted text clipped - 46 lines]
>> End With
>> -----------------------------------------

Reply to this Message

(PeteCresswell) - 12 Jan 2008 00:29 GMT

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.

Signature

PeteCresswell

Reply to this Message

Tom van Stiphout - 12 Jan 2008 03:03 GMT

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.

>Per Tom van Stiphout:
>>You a teacher?
[quoted text clipped - 12 lines]
>
>Thanks.

Reply to this Message