If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sub form Calc Problem
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 |
#2
|
|||
|
|||
Sub form Calc Problem
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 Roberts wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200806/1 |
#3
|
|||
|
|||
Sub form Calc Problem
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" u44327@uwe wrote in message news:8628e399e6cb5@uwe... 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 Roberts wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200806/1 |
Thread Tools | |
Display Modes | |
|
|