Data entry of one field calculates value of other field and vice-versa 1

[jpkeller55]

Not sure if this is possible. I have a simple database that is tracking number of miles a person walks each day. I would like the user to be able to enter either the distance in miles or the number of strides (from a pedometer)they have walked each day. The part I would like the form to do is calculate the number of strides(StrideNum) if they enter the miles(DistanceWalked) or calculate the distance in miles if they enter the strides.

Here is the query I am using for the form:

SELECT WalkData.WalkID, WalkData.EmplID, WalkData.Date, WalkData.DistanceWalked, Employees.StrideLth, [DistanceWalked]*63360/[StrideLth] AS StrideNum
FROM WalkData INNER JOIN Employees ON WalkData.EmplID = Employees.EmplID;

The way my query works now, the user can only enter DistanceWalked and the strides are calculated. Thanks for any help you can lend. Jim

 

[dweis]

I would put a "onlostfocus" event on both fields to check the status of the other. If you enter the miles onlostfocus
if isnull(me.stridenum) then
me.stridenum = me.distancewalked * 63360/[StrideLth]
endif
And on the stridenum field put the opposite to update distancewalked.

 

[jpkeller55]

Thanks dweis...It Works!

JK

 

[dweis]

You're very welcome!

 

[jpkeller55]

One question...It works if there is not data in either field. But, if after you enter the data in one of the fields and it autopopulates the other field, the user can go in and change either of the fields and it does not update the other field. Do you follow?

 

[dweis]

That's the if/then statement - I assumed (my fault) that if a user entered data in 1 field you would want the other field overwritten only if there wasn't already a value there.

Just take out the if...then & end if lines.
So, distancewalked onlostfocus would simply read:
me.stridenum = me.distancewalked * 63360/[StrideLth]
and vice/versa for the stridenum onlostfocus event.

 

[jpkeller55]

Beautiful...that makes it perfect. dweis, Thanks so much for your help! Jim