how can i get previous record on a form

[thread]

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?

[Allen Browne]

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" <ya******@gmail.comwrote in message
news:18**********************************@j20g2000 hsi.googlegroups.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?

[thread]

is there a way to present on the screen previous record?
for all records
On 7 ×™×*ו×�ר, 13:12, "Allen Browne" <[e-mail]:

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" <[e-mail] in message

news:18**********************************@j20g2000 hsi.googlegroups.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?-הסתר טקסט מצוטט-

-הר�ה טקסט מצוטט-

[Allen Browne]

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" <ya******@gmail.comwrote in message
news:9c**********************************@m34g2000 hsf.googlegroups.com...
is there a way to present on the screen previous record?
for all records

[thread]

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" <[e-mail]:

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" <[e-mail] in message

news:9c**********************************@m34g2000 hsf.googlegroups.com...
is there a way to present on the screen previous record?
for all records

[Salad]

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

[thread]

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 <[e-mail]:

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.

Easyhttp://www.youtube.com/watch?v=yygjwDAijVE

[Salad]

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