Checking data against previous record

[Hastingwood]

Hi All

I would really appreciate some help, am rather lost atm.:banghead:

I am creating a small database that will keep track of some utilities meters and the readings taken from them.

When entering a new reading I would like the system to check the reading against the previous one and if it is outside of a certain range, say 100 either way, to highlight this in some way. A msgbox or conditional formatting change for example. This is just to cut down on incorrect readings.
The form is a datasheet, getting its data from a query called meters_readings_query. The readings field is called Reading.

Any advice gratefully received,
Many thanks!

 

[RuralGuy]

How would you go about finding the previous reading?

 

[Hastingwood]

How would you go about finding the previous reading?

The form is in a datasheet, its just the record above the new one being entered...the last one in the query behind the form?

Its Access 2003 btw

 

[RuralGuy]

Easy for a human maybe but to so much for a computer. What is the Record Source for the Data Sheet? Do the records have a Primary Key? Unlike a spread sheet like Excel, a record in a RDBMS is not necessarily right next to the others except for display purposes.

 

[Hastingwood]

Recordsource is a query that filters readings to only show the ones for the selected meter. Sorted to have the latest one at the bottom.
So the main form shows the selected meter' information, and a subform in a datasheet view shows all readings for that meter. New readings are added into this datasheet view.

 

[RuralGuy]

Does each record have a PrimaryKey?

 

[isladogs]

In case its of any use to you, Allen Browne has an example of creating a query to find the previous meter reading ...
http://allenbrowne.com/subquery-01.html

Hopefully that will get you started.