how to automatically update a table field from a form?

[mike_inn]

hi there
I am having some difficulty with using Access (aren't we all?) ;-(
I have a table with a the following fields: START_DATE, END_DATE, RENTAL_PERIOD, DAILY_COST & TOTAL_COST.

What I am tring to achieve is that when someone fills in the START_DATE, END_DATE & DAILY_COST fields (which update the table behind the form) that the form will take the END from the START and put the result into the RENTAL_PERIOD field and then multiply this by the DAILY_COST value to automatically fill in and display the TOTAL_COST field.

It must be simple but I just can't get it to work and it's stopping me from sleeping I have found myself doing Access in my dreams which is not good!!

Any tips much appreciated - Thanks
Mike

PS I have written 2 queries which will calculate this for me but dont seem to be able to action the queries from within the form (if that helps!)

 

[Rickster57]

Query

If your total Cost field's calculation is set in a query and your form has it's record source as that query the result should be automatic. If not, you may have to fire off that calculation in the After Update field of a couple of those text boxes...but the calculation in the query itself would be better.

 

[mike_inn]

still no joy :-(

Hi and thanks for the advice but the Form will not allow me to use the Query as it is an "Update Query" - any ideas how I get around this please... answers will be much appreciated.
Thanks

 

[Pat Hartman]

RENTAL_PERIOD and TOTAL_COST do NOT belong in the table. Remove the columns. Add the calculations to the query. Use the query as the recordSource for the form. As soon as you type something into either the start or end dates, the period is automatically recalculated and an entry in the cost field will cause the total cost to be recalculated.

PS use the DateDiff() function to calculate the period.