Auto fill calculation in textbox based on entry in another textbox

[carpstar]

I have a two fields(actually textboxes on subform) "Mileage" and TravelCost"
When data is entered in "Mileage", "TravelCost" should auto fill.
I have tried "=[Mileage]*.31" in the TravelCost Control Source property, this auto fills the textbox but doesn't update data to travelCost field on source table . What is the best way to accomplish this.
I also need to know if it's possible to set the properties on a text box so that the user can not start a new record unless a entry is made.
Please Help.
Thanks...

[This message has been edited by carpstar (edited 04-25-2002).]

[This message has been edited by carpstar (edited 04-25-2002).]

 

[David R]

Please only put one question per topic, as someone may scan your topic, see that a question has been answered, and move on, never seeing the second part.

You can place that calculation into your table from the AfterUpdate event of the Mileage field. This is a case where storing the TravelCost is probably a good thing, because the rate (31 cents/mile, now) is likely to change in the future (ours just went up).

Change the recordsource of the TravelCost field from =... to just the name of the field you want to store in. Then in the AfterUpdate event of Mileage, place this line of code: Me.TravelCost = Me.Mileage*MileageRate

Where MileageRate is a global variable = 0.31

However, LOCK the TravelCost field since users should not be able to update the amount they get!

To require a text box to be filled before the record can be changed/exited, set the table field property "Required" to Yes.

 

[carpstar]

David,
Thank you for the reply.
When I put the code Me.TravelCost = Me.Mileage*0.31 I get an error "macro or macro grooup doesn't exist or macro is new but hasn't been saved. When you enter the macrogroupname.macroname syntax in an argument,yoou must specify the name the macro group was last saved under".
Is this because the form is a subform and I need to identify it in the code???

 

[David R]

Nope, when you build code it takes more than one line even if there's just one line of code. Let me explain:

Code runs faster, is more flexible, and provides for error checking better than Macros. The trade-off is it takes a little more time to input.

Go back to the Properties>Event screen for your text box. In the AfterUpdate event, click on the three dots at the end of the line [...] then choose Code Builder. Access will start the event with the opening and closing lines:
Private Sub Mileage_AfterUpdate()

End Sub

All you have to do is put the equation line in between those two, and close the window. So it will look like:
Private Sub Mileage_AfterUpdate()
Me.TravelCost = Me.Mileage*0.31
End Sub

When you go back to the Properties window you'll see [Event Procedure] in the AfterUpdate event. If you try to put things directly on the line, Acces thinks it's the name of a Macro and tries to look for that Macro. That's why you got the error you did.

Hope that was more clear.

[This message has been edited by David R (edited 04-26-2002).]

 

[carpstar]

David,
Again Thanks, It works perfect,it's easy when you know how. I'll make sure to limit one question per post. Any suggestions for a good VB book for beginners.