calculated field

[tennis]

Hello,

I have a form that has 3 fields, Mile_In, Mile_Out and TotalMiles, where TotalMiles = Mile_Out - Mile_In. Unfortunately the database was set up with TotalMiles as a field in the database.

On the Properties, TotalMiles is a Control Source linked directly to the field in the database.

Is there a way that I can still have TotalMiles as a Control Source and have it automatically calculate? For instance, under default value have

=[MILE_Out]-[MILE_In]

When I have this it won't update the field. The user has to manually enter in Total Miles. How do I get TotalMiles to automatically update once Mile_In and Mile_Out have been entered?

Thanks

 

[BobJacksonNCI]

Hi Tennis,

Your situation is unclear.
I.E., if you already have total miles, why do you need to add it?

And, if you're going to make a change, why not remove the field from your table and then just calculate it when needed. That would allow changes to either Mile_In or Mile_Out to be reflected in TotalMiles without having to worry whether the table is up to date.



HTH,
Bob

 

[tennis]

Thanks for your response.

Sorry for the confusion. I do not have the total for TotalMiles. Right now the user is manually subtracting the amount and then entering it in. I would like for the program to automatically fill in the field once Mile_In and Mile_Out is entered.

I was hoping to not change the database (I didn't write this) because that would make for a huge change.

 

[randy700]

How about....

In the after update event procedure for both MilesIn and MilesOut put MilesTotal = MilesOut - MilesIn


Randy

 

[BobJacksonNCI]

Randy's got it.

I would also verify a bit:

After Update for Mile_In:

If Nz(Me.[Mile_In]) > 0 And Nz(Me.[Mile_Out]) > 0 then
Me.[TotalMiles] = Me.[Mile_Out] - Me.[Mile_In]
End If

After Update for Mile_Out (same code):

If Nz(Me.[Mile_In]) > 0 And Nz(Me.[Mile_Out]) > 0 then
Me.[TotalMiles] = Me.[Mile_Out] - Me.[Mile_In]
End If

Form Event can vary more:
I.E., you could have null not allowed in the underlying table. If you don't want to do that, you could add validation code. It might be better to put the form level code under a Before Update event. That way, if a User has left a field (control) blank, you could halt before data is written to the table.

Something like:

If Nz(Me.[Mile_In]) = 0 or Nz(Me.[Mile_Out]) = 0 or Nz(Me.[TotalMiles]) = 0 then
MsgBox "You haven't entered required data", vbOkOnly, "MILES IN AND MILES OUT BOTH REQURED"
DoCmd.CancelEvent
Me.[Mile_In].SetFocus
End If



HTH,
Bob

 

[tennis]

Thanks so Much!!!