Calculate total amount on continuous form line in sub-form

[Dennis]

Hi,

I’m on Access in Office 2003.

I have a sub-form which has a default view of “Continuous Formsâ€. This form
is comprised of a single line which has the fields:

Work Date Hours Miles Other Expense Total

The work date, hours, miles, and Other expenses are data entry fields. The
Total field is equal to (Hours * HourlyRate) + (Miles * MilageRate) + Other
Expenses. The rates are retrieved from the main form. That part is working
fine.

The Work Date, Hours, Miles, and Other Expenses are bound to fields on my
table. The Total field is unbound.

The problem I am having is the total for each line is the same. I know
that the Total control is really only one control that is display multiple
times so I understand why this is happening.

My question is how do I work around this? I would like to have a total
amount per line. I understand that I can do this by binding the data entry
field to a field on the record, but I don’t want to store the total on the
record unless I absolutely have to.

Thank you for your assistance.


Dennis

 

[KARL DEWEY]

The Total field is equal to (Hours * HourlyRate) + (Miles * MilageRate) +
Other
Expenses.
Where is this computation performed? In the subform or in the query?
Try doing it in the query for the subform.

 

[Dennis]

Karl,

The calculation is done in the sub-form as I am using the sub-form for data
entry. Having the calculation in the query will only take care of the
existing records. As I add new records, I want the sub-form to display the
values for those new rows

 

[KARL DEWEY]

Looks like it is pulling data for math from record that has focus.
I think you could use the query calculation by having refresh AfterUpdate on
each subform text box that you are entering data.