How do I refer back to the last record in a table

[Jurrasicway]

Hi,

I want to refer back to the last record in a table that has a number in it
and perform a calculation based on that value in the form which will be
looking at the current record.
The table is to record revenue taken through a cash register. A cash
register has a number that increments every time a till button is pressed
giving a grand total (GT). I wish to use the GT in the last record of the
table that has an entry in the GT field and subtract the value from the
current GT to obtain an amount due. How do I refer to and call this record
value to a form that is looking at the current record?

Any help would be greatfully appreciated.

 

[Douglas J. Steele]

Realistically, there's no reliable way to refer to the last record in a
table unless you have some way of identifying what the last row is (such as
a timestamp when it was inserted). In relational databases, there's no
guarantee that the data is stored in any specific physical order in the
table.

 

[Alp Bekisoglu]

No expert but I think this could at least get you going;
Since you indicate that there is an autonumber field (supposing this is the
PK and like the record id) you can use something like this in your code:
Dim prvGt as Long
prvGtno=DMax("theautonumberfieldname","yourtablename","") will give you the
highest (last entry)

then
DLookup("theGTfieldname","yourtablename","[theautonumberfieldname]=" &
prvGtno)

should give you the GT value that you are looking for. The catch here is:
this will work just before the GT field for the current entry is taken (or
recorded in the database). But if you want to figure it out afterwards then
you can use the last part as (prvGtno - 1) to get the one before last.

i.e.:
DLookup("theGTfieldname","yourtablename","[theautonumberfieldname]=" &
prvGtno) -
DLookup("theGTfieldname","yourtablename","[theautonumberfieldname]=" &
(prvGtno - 1))
should give you the difference.

And please wait for other answers as well since there will be an expert
giving you a much better solution (shorter most probably) than this.

Alp

 

[Douglas J. Steele]

DMax of an Autonumber field will only give you the "last" record if you have
sequential autonumbers.

If you've got a replicated database, the Autonumber field will be changed to
random.

For this reason, it's generally not a good idea to attach any meaning to the
value of Autonumber fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

No expert but I think this could at least get you going;
Since you indicate that there is an autonumber field (supposing this is
the PK and like the record id) you can use something like this in your
code:
Dim prvGt as Long
prvGtno=DMax("theautonumberfieldname","yourtablename","") will give you
the highest (last entry)

then
DLookup("theGTfieldname","yourtablename","[theautonumberfieldname]=" &
prvGtno)

should give you the GT value that you are looking for. The catch here is:
this will work just before the GT field for the current entry is taken (or
recorded in the database). But if you want to figure it out afterwards
then you can use the last part as (prvGtno - 1) to get the one before
last.

i.e.:
DLookup("theGTfieldname","yourtablename","[theautonumberfieldname]=" &
prvGtno) -
DLookup("theGTfieldname","yourtablename","[theautonumberfieldname]=" &
(prvGtno - 1))
should give you the difference.

And please wait for other answers as well since there will be an expert
giving you a much better solution (shorter most probably) than this.

Alp

Hi,

I want to refer back to the last record in a table that has a number in
it
and perform a calculation based on that value in the form which will be
looking at the current record.
The table is to record revenue taken through a cash register. A cash
register has a number that increments every time a till button is pressed
giving a grand total (GT). I wish to use the GT in the last record of the
table that has an entry in the GT field and subtract the value from the
current GT to obtain an amount due. How do I refer to and call this
record
value to a form that is looking at the current record?

Any help would be greatfully appreciated.

 

[DavidAtCaspian]

Can you not, in code, open another copy of the recordset behing the form.
Go to the last record, and move previous till you find a value in the GT
field, and save it to a sutiably wide usage variable?

I know DAO isn't trendy any more, but it does thia sort of thing totally
painlessly.
Maybe a public variable on the form called LatestGT

A sub called GetLastGT

dim rs as dao.recordset
set rs = currentdb.openrecordet "Whatever" ('your form is based on)
rs.index = ........ 'whatever it is
rs.movelast
do until rs![gt] > 0
rs.moveprevious
loop

LatestGT = rs![GT]

Or something like that??

 

[Jurrasicway]

Thanks David,

Looks good to me.

Can you not, in code, open another copy of the recordset behing the form.
Go to the last record, and move previous till you find a value in the GT
field, and save it to a sutiably wide usage variable?

I know DAO isn't trendy any more, but it does thia sort of thing totally
painlessly.
Maybe a public variable on the form called LatestGT

A sub called GetLastGT

dim rs as dao.recordset
set rs = currentdb.openrecordet "Whatever" ('your form is based on)
rs.index = ........ 'whatever it is
rs.movelast
do until rs![gt] > 0
rs.moveprevious
loop

LatestGT = rs![GT]

Or something like that??

Hi,

I want to refer back to the last record in a table that has a number in it
and perform a calculation based on that value in the form which will be
looking at the current record.
The table is to record revenue taken through a cash register. A cash
register has a number that increments every time a till button is pressed
giving a grand total (GT). I wish to use the GT in the last record of the
table that has an entry in the GT field and subtract the value from the
current GT to obtain an amount due. How do I refer to and call this record
value to a form that is looking at the current record?

Any help would be greatfully appreciated.