When I reworked your database, I copied the DMax() function from your old form
=DMax("dpMeterCount","tblMachineMeters","fkMachineID= " & [Forms]![frmMachines]![pkMachineID])
The DMax() function as above finds the maximum value of dpMeterCount irrespective of the date on which it is entered. What you really want to do is find the maximum date for each meter reading pertaining to a particular machine and then whatever the meter reading is on that date.
To do this you will need some queries. First a query to find the max date for each machine in the meter table:
query name: qryMaxDateForEachMachine
SELECT tblMachineMeters.fkMachineID, Max(tblMachineMeters.dteMeter) AS MaxOfdteMeter
FROM tblMachineMeters
GROUP BY tblMachineMeters.fkMachineID;
Then create another query based on the above and join that back to the meter table via both the machineID and date fields
query name:qryMostRecentMeterReading
SELECT qryMaxDateForEachMachine.fkMachineID, qryMaxDateForEachMachine.MaxOfdteMeter, tblMachineMeters.dpMeterCount
FROM tblMachineMeters INNER JOIN qryMaxDateForEachMachine ON (tblMachineMeters.fkMachineID = qryMaxDateForEachMachine.fkMachineID) AND (tblMachineMeters.dteMeter = qryMaxDateForEachMachine.MaxOfdteMeter)
ORDER BY qryMaxDateForEachMachine.fkMachineID;
You will then need to reference the above query in the control on your form. Since the query only give the most recent, you can use the DLookup() function instead of the DMax() function.
Attached is the modified database.
=DMax("dpMeterCount","tblMachineMeters","fkMachineID= " & [Forms]![frmMachines]![pkMachineID])
The DMax() function as above finds the maximum value of dpMeterCount irrespective of the date on which it is entered. What you really want to do is find the maximum date for each meter reading pertaining to a particular machine and then whatever the meter reading is on that date.
To do this you will need some queries. First a query to find the max date for each machine in the meter table:
query name: qryMaxDateForEachMachine
SELECT tblMachineMeters.fkMachineID, Max(tblMachineMeters.dteMeter) AS MaxOfdteMeter
FROM tblMachineMeters
GROUP BY tblMachineMeters.fkMachineID;
Then create another query based on the above and join that back to the meter table via both the machineID and date fields
query name:qryMostRecentMeterReading
SELECT qryMaxDateForEachMachine.fkMachineID, qryMaxDateForEachMachine.MaxOfdteMeter, tblMachineMeters.dpMeterCount
FROM tblMachineMeters INNER JOIN qryMaxDateForEachMachine ON (tblMachineMeters.fkMachineID = qryMaxDateForEachMachine.fkMachineID) AND (tblMachineMeters.dteMeter = qryMaxDateForEachMachine.MaxOfdteMeter)
ORDER BY qryMaxDateForEachMachine.fkMachineID;
You will then need to reference the above query in the control on your form. Since the query only give the most recent, you can use the DLookup() function instead of the DMax() function.
Attached is the modified database.