Sub form Calc Problem

A

Andy Roberts

My sub form (sfrmWorkTypeForTender) sits on a main form (frmTenders) and the
record source for the subform is tblWorkTypeForTender. Ive tried using a
query instead of a table as the record source but it won't allow me to add
records to the subform, whereas a table does.

The frmTenders has 2 txtboxes which have the format of currency called
txtFieldDayRate and txtOfficeDayRate where I enter rates for field work and
office work

The sub form is a continuous form and displays cboWorkType, txtNotes,
txtFieldDays, txtFieldFee, txtOfficeDays, txtOfficeFee, txtTotal. The
txtFieldDays is fixed format with 2dp and this is where I enter the number
of days I think a WorkType will take in the field and the txtFieldFee
multiplies the txtFieldDayRate on the frmTenders with the txtFieldDays to
display the cost for the field work using the record source of
=[FieldDays]*Forms!frmTenders!FieldDayRate. The same happens with the
office controls. The txtTotal box adds together the txtFieldFee and
txtOfficeFee to display a total cost for the WorkType using
=[FieldFee]+[OfficeFee]. The idea is that I can add services on each row of
the continuous sub form. This actually all works fine for each row and I
get a total for each WorkType.

I also have total txt boxes for all the txtFieldDays, txtFieldFee,
txtOfficeDays, txtOfficeFee and txtTotal at the bottom so I can see the
total number of field days for the whole tender etc. I have managed to get
the calcs working with some great help from BruceM and I now want to add a
txt box back on the main form which takes the calculated Grand Total and
then multiplies it by another value in a box (txtVAT). I keep getting an
error.

Bruce's great advice stated "Sum the fields, not the controls. If a control
on the continuous subform contains a calculation, sum that calculation in
the footer:" which sorted my original problem but I'm struggling again.

I've tried:-

=(Sum([frmTenders]![sfrmWorkTypeForTender].[Form]![FieldDays])*[FieldRate])+(Sum([frmTenders]![sfrmWorkTypeForTender].[Form]![OfficeDays])*[OfficeRate])

and

=(Sum([frmTenders]![sfrmWorkTypeForTender]![FieldDays])*[FieldRate])+(Sum([frmTenders]![sfrmWorkTypeForTender]![OfficeDays])*[OfficeRate])

and neither work

I appreciate this may be difficult to explain easily as the calcs references
are getting a little complicated.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
 
B

bhicks11 via AccessMonster.com

Hi Andy,

Looked quick at your explanation so this is a quick thought: make sure the
query is updateable. Open the query outside the form and try to add/edit.

Bonnie

http://www.dataplus-svc.com

Andy said:
My sub form (sfrmWorkTypeForTender) sits on a main form (frmTenders) and the
record source for the subform is tblWorkTypeForTender. Ive tried using a
query instead of a table as the record source but it won't allow me to add
records to the subform, whereas a table does.

The frmTenders has 2 txtboxes which have the format of currency called
txtFieldDayRate and txtOfficeDayRate where I enter rates for field work and
office work

The sub form is a continuous form and displays cboWorkType, txtNotes,
txtFieldDays, txtFieldFee, txtOfficeDays, txtOfficeFee, txtTotal. The
txtFieldDays is fixed format with 2dp and this is where I enter the number
of days I think a WorkType will take in the field and the txtFieldFee
multiplies the txtFieldDayRate on the frmTenders with the txtFieldDays to
display the cost for the field work using the record source of
=[FieldDays]*Forms!frmTenders!FieldDayRate. The same happens with the
office controls. The txtTotal box adds together the txtFieldFee and
txtOfficeFee to display a total cost for the WorkType using
=[FieldFee]+[OfficeFee]. The idea is that I can add services on each row of
the continuous sub form. This actually all works fine for each row and I
get a total for each WorkType.

I also have total txt boxes for all the txtFieldDays, txtFieldFee,
txtOfficeDays, txtOfficeFee and txtTotal at the bottom so I can see the
total number of field days for the whole tender etc. I have managed to get
the calcs working with some great help from BruceM and I now want to add a
txt box back on the main form which takes the calculated Grand Total and
then multiplies it by another value in a box (txtVAT). I keep getting an
error.

Bruce's great advice stated "Sum the fields, not the controls. If a control
on the continuous subform contains a calculation, sum that calculation in
the footer:" which sorted my original problem but I'm struggling again.

I've tried:-

=(Sum([frmTenders]![sfrmWorkTypeForTender].[Form]![FieldDays])*[FieldRate])+(Sum([frmTenders]![sfrmWorkTypeForTender].[Form]![OfficeDays])*[OfficeRate])

and

=(Sum([frmTenders]![sfrmWorkTypeForTender]![FieldDays])*[FieldRate])+(Sum([frmTenders]![sfrmWorkTypeForTender]![OfficeDays])*[OfficeRate])

and neither work

I appreciate this may be difficult to explain easily as the calcs references
are getting a little complicated.
Click to expand...
 
A

Andy Roberts

Bonnie

The subform is based on a table and not a query asI can't seem to find out
why my query cant be updated, so I stuck with a table for ease.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
bhicks11 via AccessMonster.com said:
Hi Andy,

Looked quick at your explanation so this is a quick thought: make sure
the
query is updateable. Open the query outside the form and try to add/edit.

Bonnie

http://www.dataplus-svc.com

Andy said:
My sub form (sfrmWorkTypeForTender) sits on a main form (frmTenders) and
the
record source for the subform is tblWorkTypeForTender. Ive tried using a
query instead of a table as the record source but it won't allow me to add
records to the subform, whereas a table does.

The frmTenders has 2 txtboxes which have the format of currency called
txtFieldDayRate and txtOfficeDayRate where I enter rates for field work
and
office work

The sub form is a continuous form and displays cboWorkType, txtNotes,
txtFieldDays, txtFieldFee, txtOfficeDays, txtOfficeFee, txtTotal. The
txtFieldDays is fixed format with 2dp and this is where I enter the number
of days I think a WorkType will take in the field and the txtFieldFee
multiplies the txtFieldDayRate on the frmTenders with the txtFieldDays to
display the cost for the field work using the record source of
=[FieldDays]*Forms!frmTenders!FieldDayRate. The same happens with the
office controls. The txtTotal box adds together the txtFieldFee and
txtOfficeFee to display a total cost for the WorkType using
=[FieldFee]+[OfficeFee]. The idea is that I can add services on each row
of
the continuous sub form. This actually all works fine for each row and I
get a total for each WorkType.

I also have total txt boxes for all the txtFieldDays, txtFieldFee,
txtOfficeDays, txtOfficeFee and txtTotal at the bottom so I can see the
total number of field days for the whole tender etc. I have managed to
get
the calcs working with some great help from BruceM and I now want to add a
txt box back on the main form which takes the calculated Grand Total and
then multiplies it by another value in a box (txtVAT). I keep getting an
error.

Bruce's great advice stated "Sum the fields, not the controls. If a
control
on the continuous subform contains a calculation, sum that calculation in
the footer:" which sorted my original problem but I'm struggling again.

I've tried:-

=(Sum([frmTenders]![sfrmWorkTypeForTender].[Form]![FieldDays])*[FieldRate])+(Sum([frmTenders]![sfrmWorkTypeForTender].[Form]![OfficeDays])*[OfficeRate])

and

=(Sum([frmTenders]![sfrmWorkTypeForTender]![FieldDays])*[FieldRate])+(Sum([frmTenders]![sfrmWorkTypeForTender]![OfficeDays])*[OfficeRate])

and neither work

I appreciate this may be difficult to explain easily as the calcs
references
are getting a little complicated.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Form / Subform Problem 5
Calc Problem 12
Complicated Form Calc 4
Total Detail on Continuous Form 5
Populate main form from subform record 0
referring to Subform Total in main form 1
Calculated fields in main form based on criteria in sub form 3
Multiple sub form problem 1

Top