Carry forward value from previous record

[Guest]

Along a track each record has a Start (km) and an End(km). The Start of the
next section is the same value in km as the End of the previous record. How
could i code this to be displayed and recorded on the form for adding new
records.? I have the code to carry forward a value for date, where there is a
date box on the form. I have tried but can't get it to work for start and end
km. If I try to do the same for Start and End I end up setting the value of
the Start field to the value of the End field for the current record, when I
want the start to be the value of the end km for the previous record.

 

[Guest]

As control source for your start text box use
SELECT LAST(EndKMField) AS Start FROM yourtable;
Untested but i did do this once i this is the way i did
a small query selecting the last end record as the control
sourse for the next start record.

 

[John Vinson]

As control source for your start text box use
SELECT LAST(EndKMField) AS Start FROM yourtable;
Untested but i did do this once i this is the way i did
a small query selecting the last end record as the control
sourse for the next start record.

I'd strongly suggest using Max(EndKMField]) instead of Last. The
First() and Last() functions return the first and last records *IN
DISK STORAGE ORDER* - and Jet will put new records wherever there is
room! The Last() operator will *often* return the most recently
entered record but you cannot count on it!

Note also that this will *display* the maximum value, but will not
*store* it in the table as the OP requested. See my answer in this
thread for an alternative suggestion.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Along a track each record has a Start (km) and an End(km). The Start of the
next section is the same value in km as the End of the previous record. How
could i code this to be displayed and recorded on the form for adding new
records.? I have the code to carry forward a value for date, where there is a
date box on the form. I have tried but can't get it to work for start and end
km. If I try to do the same for Start and End I end up setting the value of
the Start field to the value of the End field for the current record, when I
want the start to be the value of the end km for the previous record.

It may not actually be necessary to store this number. It is, strictly
speaking, redundant to store the Start if it can always be determined
from the End in the previous record.

If you do need to do so, however, I'd suggest using a Form to enter
the data; table datasheets don't have usable events. In the
AfterUpdate event of the [End] textbox put code like:

Private Sub End_AfterUpdate()
Me![Start].DefaultValue = "'" & Me!End & "'"
End Sub

This will set the Start textbox to default to whatever was most
recently entered in End; it can be overridden if need be. I'd suggest
setting the Tab Stop property of the Start textbox to False so the
user will automatically be taken to the End textbox; they can mouse
into Start if it needs changing.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps