Form / Subform Problem

[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 thsi may be difficult to explain easily as the calcs references
are getting a little complicated.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[Beetle]

You appear to be missing the reference to the Foms collection object. Try
this;

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

Notice the addition of [Forms]! at the beginning of each reference.

--
_________

Sean Bailey

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 thsi may be difficult to explain easily as the calcs references
are getting a little complicated.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[Andy Roberts]

Thanks Sean

I've looked at this and understand your answer. I've tried amending as you
describe and also cut/paste your equation but still get #Error in the txt
box.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

You appear to be missing the reference to the Foms collection object. Try
this;

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

Notice the addition of [Forms]! at the beginning of each reference.

--
_________

Sean Bailey

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 thsi may be difficult to explain easily as the calcs
references
are getting a little complicated.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[BruceM]

When I spoke about using sum on the fields rather than the controls I meant
that the Sum function in a text box in the footer won't give you the sum of
a calculated control in the form's detail section. For example, an unbound
text box txtExt on a continuous form (including subform) contains the
expression:
=[UnitPrice]*[Qty]
In the footer, this expression leads to an error:
=Sum(txtExt)
Instead you need:
=Sum([UnitPrice]*[Qty])

However, if a control contains a calculated value you can pass that value to
another calculation. For functions such as Sum, Avg, and a few others that
look at a group of records you need to work with field values. However,
once the calculation has been performed you can use that value. It's
probably just a question of syntax. In an unbound text box on the main form
try this Control Source expression:
=Forms![frmTenders]![sfrmWorkTypeForTender].Form!txtFieldTotal +
Forms![frmTenders]![sfrmWorkTypeForTender].Form!txtOfficeTotal

Note that Forms! precedes the name of the main form, and that Form follows
the name of the subform control. Also, note that a dot rather than an
exclamation point (bang, as it is known) precedes the word Form. This is
because you want to reference the Form property of the subform control, not
the subform control itself. The subform itself, not the box in which it
sits (the subform control) contains the text box txtOfficeTotal.

The expression in the unbound text box on the main form can reference only
one subform record at a time. Try the expression:
=Forms![frmTenders]![sfrmWorkTypeForTender].Form![FieldDays]
As you click on a subform record, the FieldDays value from that record
should show up in the text box. However, the text box cannot "see" the
whole group of records at once. For that you would need to use the DSum
function, something like:
=DSum("[FieldDays]","[tblWorkTypeFor Tender]","[TenderID] = " & [TenderID])

for a calculation:
=DSum("[FieldDays]","[tblWorkTypeFor Tender]","[TenderID] = " & [TenderID])
* [FieldRate]
I'm guessing a bit as to some of the field names. The first TenderID is the
field name in the subform's Link Child Fields property. The second TenderID
is from the LinkMaster property.

It may be simplest just to add the text boxes as I showed first, but DSum
and other Domain functions offer a lot of flexibility, even though they tend
to slow things down in a large group of records.

Thanks Sean

I've looked at this and understand your answer. I've tried amending as
you describe and also cut/paste your equation but still get #Error in the
txt box.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

You appear to be missing the reference to the Foms collection object. Try
this;

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

Notice the addition of [Forms]! at the beginning of each reference.

--
_________

Sean Bailey

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 thsi may be difficult to explain easily as the calcs
references
are getting a little complicated.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[Andy Roberts]

Bruce,

1 word : "Genius"

All sorted, thats baffled me for about 7 days - I think I couldn't see the
wood for the trees in the end. Thanks for all your help

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

When I spoke about using sum on the fields rather than the controls I
meant that the Sum function in a text box in the footer won't give you the
sum of a calculated control in the form's detail section. For example, an
unbound text box txtExt on a continuous form (including subform) contains
the expression:
=[UnitPrice]*[Qty]
In the footer, this expression leads to an error:
=Sum(txtExt)
Instead you need:
=Sum([UnitPrice]*[Qty])

However, if a control contains a calculated value you can pass that value
to another calculation. For functions such as Sum, Avg, and a few others
that look at a group of records you need to work with field values.
However, once the calculation has been performed you can use that value.
It's probably just a question of syntax. In an unbound text box on the
main form try this Control Source expression:
=Forms![frmTenders]![sfrmWorkTypeForTender].Form!txtFieldTotal +
Forms![frmTenders]![sfrmWorkTypeForTender].Form!txtOfficeTotal

Note that Forms! precedes the name of the main form, and that Form follows
the name of the subform control. Also, note that a dot rather than an
exclamation point (bang, as it is known) precedes the word Form. This is
because you want to reference the Form property of the subform control,
not the subform control itself. The subform itself, not the box in which
it sits (the subform control) contains the text box txtOfficeTotal.

The expression in the unbound text box on the main form can reference only
one subform record at a time. Try the expression:
=Forms![frmTenders]![sfrmWorkTypeForTender].Form![FieldDays]
As you click on a subform record, the FieldDays value from that record
should show up in the text box. However, the text box cannot "see" the
whole group of records at once. For that you would need to use the DSum
function, something like:
=DSum("[FieldDays]","[tblWorkTypeFor Tender]","[TenderID] = " &
[TenderID])

for a calculation:
=DSum("[FieldDays]","[tblWorkTypeFor Tender]","[TenderID] = " &
[TenderID]) * [FieldRate]
I'm guessing a bit as to some of the field names. The first TenderID is
the field name in the subform's Link Child Fields property. The second
TenderID is from the LinkMaster property.

It may be simplest just to add the text boxes as I showed first, but DSum
and other Domain functions offer a lot of flexibility, even though they
tend to slow things down in a large group of records.

Thanks Sean

I've looked at this and understand your answer. I've tried amending as
you describe and also cut/paste your equation but still get #Error in the
txt box.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

You appear to be missing the reference to the Foms collection object.
Try
this;

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

Notice the addition of [Forms]! at the beginning of each reference.

--
_________

Sean Bailey


:

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 thsi may be difficult to explain easily as the calcs
references
are getting a little complicated.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[BruceM]

You're very welcome. Glad to hear you got it working. I expect it will
come much more easily the next time you deal with a similar situation.

Bruce,

1 word : "Genius"

All sorted, thats baffled me for about 7 days - I think I couldn't see the
wood for the trees in the end. Thanks for all your help

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

When I spoke about using sum on the fields rather than the controls I
meant that the Sum function in a text box in the footer won't give you
the sum of a calculated control in the form's detail section. For
example, an unbound text box txtExt on a continuous form (including
subform) contains the expression:
=[UnitPrice]*[Qty]
In the footer, this expression leads to an error:
=Sum(txtExt)
Instead you need:
=Sum([UnitPrice]*[Qty])

However, if a control contains a calculated value you can pass that value
to another calculation. For functions such as Sum, Avg, and a few others
that look at a group of records you need to work with field values.
However, once the calculation has been performed you can use that value.
It's probably just a question of syntax. In an unbound text box on the
main form try this Control Source expression:
=Forms![frmTenders]![sfrmWorkTypeForTender].Form!txtFieldTotal +
Forms![frmTenders]![sfrmWorkTypeForTender].Form!txtOfficeTotal

Note that Forms! precedes the name of the main form, and that Form
follows the name of the subform control. Also, note that a dot rather
than an exclamation point (bang, as it is known) precedes the word Form.
This is because you want to reference the Form property of the subform
control, not the subform control itself. The subform itself, not the box
in which it sits (the subform control) contains the text box
txtOfficeTotal.

The expression in the unbound text box on the main form can reference
only one subform record at a time. Try the expression:
=Forms![frmTenders]![sfrmWorkTypeForTender].Form![FieldDays]
As you click on a subform record, the FieldDays value from that record
should show up in the text box. However, the text box cannot "see" the
whole group of records at once. For that you would need to use the DSum
function, something like:
=DSum("[FieldDays]","[tblWorkTypeFor Tender]","[TenderID] = " &
[TenderID])

for a calculation:
=DSum("[FieldDays]","[tblWorkTypeFor Tender]","[TenderID] = " &
[TenderID]) * [FieldRate]
I'm guessing a bit as to some of the field names. The first TenderID is
the field name in the subform's Link Child Fields property. The second
TenderID is from the LinkMaster property.

It may be simplest just to add the text boxes as I showed first, but DSum
and other Domain functions offer a lot of flexibility, even though they
tend to slow things down in a large group of records.

Thanks Sean

I've looked at this and understand your answer. I've tried amending as
you describe and also cut/paste your equation but still get #Error in
the txt box.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
You appear to be missing the reference to the Foms collection object.
Try
this;

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

Notice the addition of [Forms]! at the beginning of each reference.

--
_________

Sean Bailey


:

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 thsi may be difficult to explain easily as the calcs
references
are getting a little complicated.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007