how can i get previous record on a form

Hi all i need to build progression calculator for a record and for
this i need to have the possiblity to get the information for the
previous record.
is it posible to do it or i will need to use recordset for this?

Re: how can i get previous record on a form

Users can filter or sort forms at will, so the only way to get the "previous
record" would be to programmatically locate the current record in the form's
RecordsetClone, and MovePrevious.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"thread" wrote in message
news:18d8c3e8-8b5e-4b6f-a840-aafe50b5d62b@j20g2000hsi.google groups.com...
> Hi all i need to build progression calculator for a record and for
> this i need to have the possiblity to get the information for the
> previous record.
> is it posible to do it or i will need to use recordset for this?

Re: how can i get previous record on a form

is there a way to present on the screen previous record?
for all records
On 7 ינואר, 13:12, "Allen Browne" ig.Invalid> wrote:
> Users can filter or sort forms at will, so the only way to get the "previo=
us
> record" would be to programmatically locate the current record in the form=
's
> RecordsetClone, and MovePrevious.
>
> --
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "thread" wrote in message
>
> news:18d8c3e8-8b5e-4b6f-a840-aafe50b5d62b@j20g2000hsi.google groups.com...
>
>
>
> > Hi all i need to build progression calculator for a record and for
> > this i need to have the possiblity to get the information for the
> > previous record.
> > is it posible to do it or i will need to use recordset for this?-ה=
סתר טקסט מצוטט-
>
> -הראה טקסט מצו=D7=
˜×=98-

Re: how can i get previous record on a form

If you set the Default View of the form to:
Continuous Form
you can see more than one record at a time.
No code needed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"thread" wrote in message
news:9c687f79-4a1b-4a6f-9791-1126416c8ed1@m34g2000hsf.google groups.com...
is there a way to present on the screen previous record?
for all records

Re: how can i get previous record on a form

hi,
thank you for you quick replay,the issue is that i need to use the
previous record to make a progression calculation on the currect
for example
a 100
b 200
c 300
so on b i would like to have (b-a
c would be c-b
and so on,is this posible?

On 7 ינואר, 13:41, "Allen Browne" ig.Invalid> wrote:
> If you set the Default View of the form to:
>     Continuous Form
> you can see more than one record at a time.
> No code needed.
>
> --
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "thread" wrote in message
>
> news:9c687f79-4a1b-4a6f-9791-1126416c8ed1@m34g2000hsf.google groups.com...
> is there a way to present on the screen previous record?
> for all records

Re: how can i get previous record on a form

thread wrote:
> hi,
> thank you for you quick replay,the issue is that i need to use the
> previous record to make a progression calculation on the currect
> for example
> a 100
> b 200
> c 300
> so on b i would like to have (b-a
> c would be c-b
> and so on,is this posible?
>

In your case, for speed issues, I'd suggest you store that value in the
record to a field...call it LastValue...only if the current value will
never change for this record. If the value changes you'd need to update
the entire table and this suggestion would be better left ignored.

I'd suggest you have an autonumber in the table. As Alan noted,
filtering and sorting could throw you off.

Is the value will always be static then:
You could then do something like this in some event on the form, maybe
the BeforeUpdate event of the form.
Dim LastID As Long
LastID = DMax("ID","TableName","ID < " & ME.ID)
Me.LastValue = Dlookup("FieldName","TableName","Id = " & LastID)

If the values will/can change then
Use a query and make the field calculated. You could create a new query
with two columns; ID and LastID. ID would be the autonumber of the
table, LastID using something like
LastID : DMax("ID","TableName","ID < " & [ID])

Linking your table to this query in another query you could then get the
last value to use the Dlookup to get the LastValue. From there you can
do your subtraction.

Easy
http://www.youtube.com/watch?v=yygjwDAijVE

Re: how can i get previous record on a form

thank you for the quick repaly,it gave me some direction,now,is it
posible based on the result to preserve the previsous result or to get
the current one
for example
a 100
b 150
c 300
result will be

100 a
iif b-a<60 then 100 else current

meaning:
100 b
300 c

On 7 ינואר, 14:47, Salad wrote:
> thread wrote:
> > hi,
> > thank you for you quick replay,the issue is that i need to use the
> > previous record to make a progression calculation on the currect
> > for example
> > a 100
> > b 200
> > c 300
> > so on b i would like to have  (b-a
> > c would be c-b
> > and so on,is this posible?
>
> In your case, for speed issues, I'd suggest you store that value in the
> record to a field...call it LastValue...only if the current value will
> never change for this record.  If the value changes you'd need to upd=
ate
> the entire table and this suggestion would be better left ignored.
>
> I'd suggest you have an autonumber in the table.  As Alan noted,
> filtering and sorting could throw you off.
>
> Is the value will always be static then:
> You could then do something like this in some event on the form, maybe
> the BeforeUpdate event of the form.
>         Dim LastID As Long
>         LastID =3D DMax("ID","TableName","ID < " & ME.=
ID)
>         Me.LastValue =3D Dlookup("FieldName","TableNam=
e","Id =3D " & LastID)
>
> If the values will/can change then
> Use a query and make the field calculated.  You could create a new qu=
ery
> with two columns; ID and LastID. ID would be the autonumber of the
> table, LastID using something like
>         LastID : DMax("ID","TableName","ID < " & [ID])=

>
> Linking your table to this query in another query you could then get the
> last value to use the Dlookup to get the LastValue.  From there you c=
an
> do your subtraction.
>
> Easyhttp://www.youtube.com/watch?v=3DyygjwDAijVE

Re: how can i get previous record on a form

thread wrote:

> thank you for the quick repaly,it gave me some direction,now,is it
> posible based on the result to preserve the previsous result or to get
> the current one
> for example
> a 100
> b 150
> c 300
> result will be
>
> 100 a
> iif b-a<60 then 100 else current
>
> meaning:
> 100 b
> 300 c
>

I don't know as I'm not sure I understand the question. You could try
this out. Create a new database/mdb. Create a new table, called
Table1. Add 2 fields; ID (autonumber), Score (Number)

Add 5 records to table1. My values for Score are 100, 150, 300, 450, 500.

Create 2 queries; (Query/New/Design (close on Add Tables). From the
menu select View/SQL. Copy/paste the following queries and name them as
I did. Save them.

BTW, the first record will have no previous value.

QueryName = Table1Previous
SELECT Table1.ID, Table1.Score AS CurrentScore,
CLng(NZ(DMax("ID","Table1","ID <" & [ID]),0)) AS PreviousID
FROM Table1;

QueryName = Table1Final
SELECT Table1Previous.ID, Table1Previous.CurrentScore,
NZ([Table1_1]![Score],0) AS PreviousScore,
IIf([CurrentScore]-NZ([Table1_1]![Score],0)<60,NZ([Table1_1]![Score],0),[CurrentScore])
AS CalcedScore, [CurrentScore]-NZ([Table1_1]![Score],0) AS Expr1
FROM Table1Previous LEFT JOIN Table1 AS Table1_1 ON
Table1Previous.PreviousID = Table1_1.ID;

Now run Table1Final. Review the values in the columns and see if does
what you need.

White Wedding
http://www.youtube.com/watch?v=C0YGrj2A0Q4