I want subtrack a value on the current record from the previous record and display the result in text box in subform datasheet. This is done during data entry. The calculation works most of the time and randomly it returns the wrong value. I have the code in an after update event after I enter a number. Here is what my code looks like:
sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysBaseNumber]=Forms![frmTempestCoordination]![frmSubSystem]![sysBaseNumber] - 1 "))
This is how I generate the sysBaseNumber
sysBaseNumber = Nz(DMax("sysBaseNumber", "tblSystemConfiguration", "sysAccountID=" & Forms!frmTempestCoordination!accAccountID), 0) + 1
I am using DMax so I can keep the subform records with the parent form record.
My results are inconsistant:
This is what I want the subform to do when subtracking from the previous record
syshop1 sysHopSpacing
5
20 15
30 10
Instead I get a result that does not make sense like this:
sysHop1 sysHopSpacing
5
20 15
30 4
I cant trace it down, any suggestions???
Thanks
sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysBaseNumber]=Forms![frmTempestCoordination]![frmSubSystem]![sysBaseNumber] - 1 "))
This is how I generate the sysBaseNumber
sysBaseNumber = Nz(DMax("sysBaseNumber", "tblSystemConfiguration", "sysAccountID=" & Forms!frmTempestCoordination!accAccountID), 0) + 1
I am using DMax so I can keep the subform records with the parent form record.
My results are inconsistant:
This is what I want the subform to do when subtracking from the previous record
syshop1 sysHopSpacing
5
20 15
30 10
Instead I get a result that does not make sense like this:
sysHop1 sysHopSpacing
5
20 15
30 4
I cant trace it down, any suggestions???
Thanks