How to subtract values in the same field from the first record

[evanark]

Hi all,

I am having trouble with or dont know if its possible to accomplish. I have a table with a field called Hop 1 that house a numeric value. I have created a textbox control called hop spacing. I wan to subtrack the hop 1 from the hop 1 in the first record and display it in the text box control hop spacing.

example of what I want to accomplish

hop Hop spacing
20 0
10 10
15 5

Is it possible to perform this operation? I am novice at vba program. The calculation is to be perform on an afterupdate event. If the value is > 4 then flag the hop spacing field with a red background. Please help

Thank you in advance!

 

[missinglinq]

First off, your Record has to have a Field that can be used to identify a given Record it as the 'first' Record, such as a Date/Time Stamp Field; Records in Access Tables have no inherent order. Do you have such a Field?

Linq ;0)>

 

[evanark]

I am sorry I forgot to mention the primary key, its sysSystemConfigID in the tblSystemConfiguration table.

 

[evanark]

Account information and system configuration are two seperate tables that are linked by Account ID. System configuration has its own autonumber primary key called sysSystemConfigID. The goal is when the user enters a network number in the system configuations subform, hop 1 geneaters a number based of the network number. I used a reference table that is not linked (DLookup) I have that part working. Account can have up to 5 systems meaning at most, each account can have 5 records in the system configuration. I want to take the first Hop 1 number and subtract if from the next Hop 1 number then display the difference in the textbox Hop Spacing(this box is not in the table it is a calculation text box) but each hop 1 number needs to be subtract from the first hop 1 number in the system configuration and If the hop spacing is <4 then it turns the hop spacing box Red and displays a message in a text box control, however if the number happens be negative, it need to be formatted to a positive. The value of the hop spacing just represent the spaces between hop 1 numbers. Is it possible to perform this calculation?

I have attached a screen shot of my form

 

[evanark]

I got the calculation to work by subtracting from the previous record using DLookUp, is there a way to subtract from the first record?

This is my code

sysHopSpacing = [sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysSystemConfigID]=Forms![frmTempestCoordination]![frmSubSystem]![sysSystemConfigID] - 1")