Using previous record for calculation in form

[Uni03]

Hi - I have a database that has data for 10 machines which is updated twice a day, at the end of each work shift. The record layout is mach#, serial #, date, shift and meter reading. A form is used to update the table. I need to be able to get the meter reading from the previous record for each machine in order to calculate the volume of work done on that shift for each machine (ex. for machine 101, current meter count minus last meter count for 101 from the table). What is the method used to get a previous record from the table?

Thank You.

 

[spikepl]

http://allenbrowne.com/subquery-01.html

 

[Uni03]

Thanks SpikePl, that document will be very helpful. My reason to go back to a previous record is mainly to catch a data entry mistype. The math will show a negative result if a digit or 2 is left out when entering, or the resulting value will be too large, if an extra digit or 2 is entered. I want to display an error message if this occurs so the correct number will be re-entered.

 

[spikepl]

For checking of input I think you should do the checking in the form's BeforeUpdate event. There you can use DLookup to find the value to compare with,check the field and react accordingly

 

[Uni03]

Using DLOOKUP, if I understand correctly,the [ID] listed is the record ID, the first field in the record generated by Access. There are 10 machines are updated each time, and each has a unique machine # (101-110). Using [ID-1] goes back to the previous record in the table, but I have to get each of these 10 records. How do I get the previous record of say, machine 102?

Thank You again.

 

[spikepl]

use Dmax to find record id containing the latest record for a given machine
Use DLookup to get the desired value in that record

 

[Uni03]

I think I need a walk thru on this. My form is set up to select a machine # (ex. 101) from a combo box. There is a Begin Cycle record and End Cycle record on the table. So, on the form, when the machine # is selected, I would like the Begin Cycle field on the form to be automatically filled with the value for End Cycle from the previous record for that machine#. How is the DMax and DLookUp statement suposed to look to do this?

Thanks.