Update Field while scrolling thru records

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I have two tables.

Customers
Sales

In the customer table I have a field called OneWayMileage

in the sales table I have a field called TotlaRoundTripMiles

I have a Sales form with an Event in A lookup field that looks like below.

Sales_FirstName = Customers_FirstName
Sales_LastName = Customers_LastName
PhoneHome = HomePhone
TotalRoundTripMiles = OneWayMileage * 2

Problem is that sometimes I do not have the OneWayMileage info in the
customer file. So when I enter in a sale from a form, the two way mileage
is not calculated since the onewaymileage is blank (or zero).

I want to be able to scroll thru the records in the sales form and by
clicking in or past the TotalRoundTripMiles field and have it re-calculated
based on the OneWayMileage. The concept is that if I go into the customer
table and update the proper OneWayMileage, I want the sales record in the
sales table to be updated as I go thru each record.

I tried putting the calculation as an expresion and as code in a few events
for the TotalRoundTripMiles field but it did not work.

Thanks..... in advance.... Bob
 
Bob said:
I have two tables.

Customers
Sales

In the customer table I have a field called OneWayMileage

in the sales table I have a field called TotlaRoundTripMiles

I have a Sales form with an Event in A lookup field that looks like below.

Sales_FirstName = Customers_FirstName
Sales_LastName = Customers_LastName
PhoneHome = HomePhone
TotalRoundTripMiles = OneWayMileage * 2

Problem is that sometimes I do not have the OneWayMileage info in the
customer file. So when I enter in a sale from a form, the two way mileage
is not calculated since the onewaymileage is blank (or zero).

I want to be able to scroll thru the records in the sales form and by
clicking in or past the TotalRoundTripMiles field and have it re-calculated
based on the OneWayMileage. The concept is that if I go into the customer
table and update the proper OneWayMileage, I want the sales record in the
sales table to be updated as I go thru each record.

I tried putting the calculation as an expresion and as code in a few events
for the TotalRoundTripMiles field but it did not work.

Thanks..... in advance.... Bob
Click to expand...

You could do this with an update query, checking if TotalRoundTripMiles
is Null, and then setting its value. This would do it all at once,
rather than having to scroll through each record and have it recalculated.

However, I wouldn't recommend this approach at all. You shouldn't be
storing calculated values in your tables (with a few exceptions). Since
you know that Total Miles always equals One Way *2, you do that
calculation any time you want to display the result -- on a form, a
report, etc. You can even create a function which returns the value for you.
 
Back
Top