Updating a table from a form

[Pack]

I have created a form based on my table. I want to be able to enter values in the form for a Rate and Footage. I have a expression written to then calculate the Total (=Rate*Footage). How can I update the Total field in the table?

 

[Ally]

The way I understand it from your description, I would put in an unbound text box on your form. Then type in it, (or via the Control Source in the Properties box)

=[rate]*[footage]

 

[David R]

Or put the field in your query, and base everything off of that instead of the table. If you can calculate a value from other stored values, then you don't need to store that value at all.

Note, if [Rate] is something that could change over time and you need historical data on it, then you will have to take other steps. If that's the case let us know.

 

[Pack]

I entered the control source (=[Footage]*[Rate])
and it is calculating it in the form, but is not entering the result in the Table.
I am not using any queries for this. The Rate will not change because I will be creating a seperate table each year.

 

[David R]

You really need to read up on database normalization. You are proposing to store something that can be calculated at any time. Why? This wastes space and can lead to data errors if you get the rate wrong. You can also store every year's data in the same table and just sort them by year, but that's a slightly different question.

Check the archives, or the MS Knowledge Base, or the web, or a book, for 'database normalization'. Access is not the same as Excel.

 

[Pack]

The reason I want to store the Total is because I am using that information in a mail merge document in Word. Which is also the reason I don't want to store multiple years data in one table.

[This message has been edited by Pack (edited 05-01-2002).]

 

[vangogh228]

Actually, an easier way of doing this is to create the calculated field in the table itself, and leave it off the form altogether.

 

[Pack]

OK.. How do I do that?

 

[David R]

I think/hope what vangogh means is you can create the field in a query, and then use that query for your mailmerge. Access views tables and queries almost interchangably, except that queries aren't permanent.

IF [Rate] will not change, or you don't care about outdated data if it does, then put this in a blank field of your query:
Total: [Rate]*[Footage]

 

[Pack]

Not exactly what I was looking for... But I guess there is no easy way

 

[Idjit]

Put all three fields on your form. You can easily trigger the computation in one of 2 ways. 1) put a command button on the form labeled something like "compute total", set the on click event to set the value of your total field to rate*footage. 2) omit the command button and link the calculation code to the on change or after update event property of one or both of the rate or footage fields. The first gives the user more control, the second computes the product automatically without the user having to click a command button.