Calc Problem

[Andy Roberts]

Ive got a txtbox called [dayrate] on my main form. I have a subform which
is now set to continuous which contains number of days txtbox, price txtbox
and a cbobox containing all our services.

The idea is to set the day rate on the main form and then select various
services (one on each line of the continuous form) and allocate number of
days to each service. The price txtbox will then calculate each service
cost by multiplying the day rate by number of days - I've got all that
working fine.

The problem is that I have added another unbound txt box to the subform
footer (not sure if it should be on the main form) which will total up all
the price txtboxes on the sub form to give a total overall price. The
control source of the unbound txtbox is currently set to :

=Sum([txtPrice])

but this returns an error. What am I doing wrong?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[Rui]

you need to refer which form you are accessing the info from.

ie. sum (form1![txtfld])

 

[Andy Roberts]

Rui

The txtbox performing the calc is on the footer of the same form. I've
tried to reference the form the control is on but still get an error.
Didn't think you needed to reference a form if the controls were on the same
one?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

you need to refer which form you are accessing the info from.

ie. sum (form1![txtfld])

Ive got a txtbox called [dayrate] on my main form. I have a subform
which
is now set to continuous which contains number of days txtbox, price
txtbox
and a cbobox containing all our services.

The idea is to set the day rate on the main form and then select various
services (one on each line of the continuous form) and allocate number of
days to each service. The price txtbox will then calculate each service
cost by multiplying the day rate by number of days - I've got all that
working fine.

The problem is that I have added another unbound txt box to the subform
footer (not sure if it should be on the main form) which will total up
all
the price txtboxes on the sub form to give a total overall price. The
control source of the unbound txtbox is currently set to :

=Sum([txtPrice])

but this returns an error. What am I doing wrong?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[BruceM]

Repeat the calculation in the text box in the footer:
=Sum([DayRate]*[NumberOfDays])

Rui

The txtbox performing the calc is on the footer of the same form. I've
tried to reference the form the control is on but still get an error.
Didn't think you needed to reference a form if the controls were on the
same one?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

you need to refer which form you are accessing the info from.

ie. sum (form1![txtfld])

Ive got a txtbox called [dayrate] on my main form. I have a subform
which
is now set to continuous which contains number of days txtbox, price
txtbox
and a cbobox containing all our services.

The idea is to set the day rate on the main form and then select various
services (one on each line of the continuous form) and allocate number
of
days to each service. The price txtbox will then calculate each service
cost by multiplying the day rate by number of days - I've got all that
working fine.

The problem is that I have added another unbound txt box to the subform
footer (not sure if it should be on the main form) which will total up
all
the price txtboxes on the sub form to give a total overall price. The
control source of the unbound txtbox is currently set to :

=Sum([txtPrice])

but this returns an error. What am I doing wrong?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[Andy Roberts]

Thanks everyone, but I'm not sure I've explained myself particularly well,
so i'll give it a better shot...

I have a main form which displays quotation records. I have on that form a
text box into which I enter a day rate for my time. Also on the form I have
a subform which is in datasheet view which allows me to list on each row
each service I wish to include in the quotation. Each record of the subform
allows me to state the services (from a dropdowncombo), the number of days
that service will take and there is a third box whichdisplays the result of
multiplying the number of days by the day rate (which is on the main form).
This all works fine.

My problem is that I wish to have totals boxes below the number of days and
the service cost, so for example if I set a day rate of £200 and then in my
subform select the following:-

Service No of Days Total
-------- ------------ ------
A 2 400
B 3 600
C 2 400

Then in my footer of the subform (or back on the main form - not sure which
is best) I want total boxes for each column. e.g

------------ ------
7 1400

Some questions...

Should the subform be based on a table or on a query?
Should the total boxes be in the subform footer or back on the main form?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

Repeat the calculation in the text box in the footer:
=Sum([DayRate]*[NumberOfDays])

Rui

The txtbox performing the calc is on the footer of the same form. I've
tried to reference the form the control is on but still get an error.
Didn't think you needed to reference a form if the controls were on the
same one?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

you need to refer which form you are accessing the info from.

ie. sum (form1![txtfld])

:

Ive got a txtbox called [dayrate] on my main form. I have a subform
which
is now set to continuous which contains number of days txtbox, price
txtbox
and a cbobox containing all our services.

The idea is to set the day rate on the main form and then select
various
services (one on each line of the continuous form) and allocate number
of
days to each service. The price txtbox will then calculate each
service
cost by multiplying the day rate by number of days - I've got all that
working fine.

The problem is that I have added another unbound txt box to the subform
footer (not sure if it should be on the main form) which will total up
all
the price txtboxes on the sub form to give a total overall price. The
control source of the unbound txtbox is currently set to :

=Sum([txtPrice])

but this returns an error. What am I doing wrong?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[BruceM]

Basing the subform on a query rather than a table lets you sort, calculate,
concatenate, and otherwise manipulate the data, but does not affect the data
differently than if you base the subform on a table.
The totals boxes should be in the subform. You could put them anywhere, but
since the records are in the subform it is simplest to put the totals text
boxes there. If the No of Days field is NoOfDays, a text box in the subform
footer could have:
=Sum(NoOfDays)
The Totals text box presumably contains a calculation such as:
=Forms![frmMain]![txtDayRate] * NoOfDays

In the subform footer, the text box that is to contain the number 1400 would
have something such as:
=Sum(NoOfDays) * Forms![frmMain]![txtDayRate]

You could perform calculations in a query, but keep in mind that the subform
query would need to reference a field on the main form, so would not be able
to run on its own. If you try to open the query by itself (when frmMain is
not open) you will receive a parameter prompt.

Thanks everyone, but I'm not sure I've explained myself particularly well,
so i'll give it a better shot...

I have a main form which displays quotation records. I have on that form
a text box into which I enter a day rate for my time. Also on the form I
have a subform which is in datasheet view which allows me to list on each
row each service I wish to include in the quotation. Each record of the
subform allows me to state the services (from a dropdowncombo), the number
of days that service will take and there is a third box whichdisplays the
result of multiplying the number of days by the day rate (which is on the
main form). This all works fine.

My problem is that I wish to have totals boxes below the number of days
and the service cost, so for example if I set a day rate of £200 and then
in my subform select the following:-

Service No of Days Total
-------- ------------ ------
A 2 400
B 3 600
C 2 400

Then in my footer of the subform (or back on the main form - not sure
which is best) I want total boxes for each column. e.g

------------ ------
7 1400

Some questions...

Should the subform be based on a table or on a query?
Should the total boxes be in the subform footer or back on the main form?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

Repeat the calculation in the text box in the footer:
=Sum([DayRate]*[NumberOfDays])

Rui

The txtbox performing the calc is on the footer of the same form. I've
tried to reference the form the control is on but still get an error.
Didn't think you needed to reference a form if the controls were on the
same one?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
you need to refer which form you are accessing the info from.

ie. sum (form1![txtfld])

:

Ive got a txtbox called [dayrate] on my main form. I have a subform
which
is now set to continuous which contains number of days txtbox, price
txtbox
and a cbobox containing all our services.

The idea is to set the day rate on the main form and then select
various
services (one on each line of the continuous form) and allocate number
of
days to each service. The price txtbox will then calculate each
service
cost by multiplying the day rate by number of days - I've got all that
working fine.

The problem is that I have added another unbound txt box to the
subform
footer (not sure if it should be on the main form) which will total up
all
the price txtboxes on the sub form to give a total overall price. The
control source of the unbound txtbox is currently set to :

=Sum([txtPrice])

but this returns an error. What am I doing wrong?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[Andy Roberts]

Bruce

Ok this is driving me insane! Everything you have said makes perfect sense
to me and thats how I thought it would work, but it doesn't!

I tried to simplify my problem with the example I set out before but it may
be more prudent to explain using the real names etc as this is driving me
crazy.

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
=[txtFieldDays]*Forms!frmTenders!txtFieldDayRate. 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
=[txtFieldFee]+[txtOfficeFee]. 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.

The problem is that I would like a a total 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 placed
txt boxes in the footer of the subform with the following control sources:-

txtTotalFieldDays =Sum([txtFieldDays])
txtTotalFieldFee =Sum([txtFieldFee])
txtTotalOfficeDays =Sum([txtOfficeDays])
txtTotalOfficeFee =Sum([txtOfficeFee])
txtTotalFee =Sum([txtTotal])

....and each displays #Error in the form.

4 questions...

Why does using a query as the record source of the subform not allow me to
add records to the subform, whereas a table does?
Is the problem because I'm asking fields to calculate values from fields
which are also calculating values?
If the answer to Q2 is No then where am I going wrong?
If the answer to Q2 is Yes then how do you get around such a problem.

I appreciate this is a pretty long explanation of what looks like a simple
problem but I'm fast going bald over this!
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

Basing the subform on a query rather than a table lets you sort,
calculate, concatenate, and otherwise manipulate the data, but does not
affect the data differently than if you base the subform on a table.
The totals boxes should be in the subform. You could put them anywhere,
but since the records are in the subform it is simplest to put the totals
text boxes there. If the No of Days field is NoOfDays, a text box in the
subform footer could have:
=Sum(NoOfDays)
The Totals text box presumably contains a calculation such as:
=Forms![frmMain]![txtDayRate] * NoOfDays

In the subform footer, the text box that is to contain the number 1400
would have something such as:
=Sum(NoOfDays) * Forms![frmMain]![txtDayRate]

You could perform calculations in a query, but keep in mind that the
subform query would need to reference a field on the main form, so would
not be able to run on its own. If you try to open the query by itself
(when frmMain is not open) you will receive a parameter prompt.

Thanks everyone, but I'm not sure I've explained myself particularly
well, so i'll give it a better shot...

I have a main form which displays quotation records. I have on that form
a text box into which I enter a day rate for my time. Also on the form I
have a subform which is in datasheet view which allows me to list on each
row each service I wish to include in the quotation. Each record of the
subform allows me to state the services (from a dropdowncombo), the
number of days that service will take and there is a third box
whichdisplays the result of multiplying the number of days by the day
rate (which is on the main form). This all works fine.

My problem is that I wish to have totals boxes below the number of days
and the service cost, so for example if I set a day rate of £200 and then
in my subform select the following:-

Service No of Days Total
-------- ------------ ------
A 2 400
B 3 600
C 2 400

Then in my footer of the subform (or back on the main form - not sure
which is best) I want total boxes for each column. e.g

------------ ------
7 1400

Some questions...

Should the subform be based on a table or on a query?
Should the total boxes be in the subform footer or back on the main form?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

Repeat the calculation in the text box in the footer:
=Sum([DayRate]*[NumberOfDays])

Rui

The txtbox performing the calc is on the footer of the same form. I've
tried to reference the form the control is on but still get an error.
Didn't think you needed to reference a form if the controls were on the
same one?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
you need to refer which form you are accessing the info from.

ie. sum (form1![txtfld])

:

Ive got a txtbox called [dayrate] on my main form. I have a subform
which
is now set to continuous which contains number of days txtbox, price
txtbox
and a cbobox containing all our services.

The idea is to set the day rate on the main form and then select
various
services (one on each line of the continuous form) and allocate
number of
days to each service. The price txtbox will then calculate each
service
cost by multiplying the day rate by number of days - I've got all
that
working fine.

The problem is that I have added another unbound txt box to the
subform
footer (not sure if it should be on the main form) which will total
up all
the price txtboxes on the sub form to give a total overall price.
The
control source of the unbound txtbox is currently set to :

=Sum([txtPrice])

but this returns an error. What am I doing wrong?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[BruceM]

Sum the fields, not the controls. If a control on the continuous subform
contains a calculation, sum that calculation in the footer:

=Sum([FieldDays])*Forms![frmTenders]![txtFieldDayRate]
Similarly for the office charges:
=Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate]

I will call these two text boxes in the footer txtSumFieldDays and
txtSumOfficeDays. Another unbound text box in the footer could contain:
=[txtSumFieldDays]+[txtSumOfficeDays]

If you want only one GrandTotal text box in the footer it could have:

=(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate]) +
(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate])

The line break here is an attempt at clarity. The expression will be all on
one line.

Again, note carefully that the Sum is used only on fields, not on controls.
I'm not usre what will happen if the field and the control have the same
name, but in any case the field and the control should have different names.

Bruce

Ok this is driving me insane! Everything you have said makes perfect
sense to me and thats how I thought it would work, but it doesn't!

I tried to simplify my problem with the example I set out before but it
may be more prudent to explain using the real names etc as this is driving
me crazy.

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
=[txtFieldDays]*Forms!frmTenders!txtFieldDayRate. 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
=[txtFieldFee]+[txtOfficeFee]. 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.

The problem is that I would like a a total 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
placed txt boxes in the footer of the subform with the following control
sources:-

txtTotalFieldDays =Sum([txtFieldDays])
txtTotalFieldFee =Sum([txtFieldFee])
txtTotalOfficeDays =Sum([txtOfficeDays])
txtTotalOfficeFee =Sum([txtOfficeFee])
txtTotalFee =Sum([txtTotal])

...and each displays #Error in the form.

4 questions...

Why does using a query as the record source of the subform not allow me to
add records to the subform, whereas a table does?
Is the problem because I'm asking fields to calculate values from fields
which are also calculating values?
If the answer to Q2 is No then where am I going wrong?
If the answer to Q2 is Yes then how do you get around such a problem.

I appreciate this is a pretty long explanation of what looks like a simple
problem but I'm fast going bald over this!
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

Basing the subform on a query rather than a table lets you sort,
calculate, concatenate, and otherwise manipulate the data, but does not
affect the data differently than if you base the subform on a table.
The totals boxes should be in the subform. You could put them anywhere,
but since the records are in the subform it is simplest to put the totals
text boxes there. If the No of Days field is NoOfDays, a text box in the
subform footer could have:
=Sum(NoOfDays)
The Totals text box presumably contains a calculation such as:
=Forms![frmMain]![txtDayRate] * NoOfDays

In the subform footer, the text box that is to contain the number 1400
would have something such as:
=Sum(NoOfDays) * Forms![frmMain]![txtDayRate]

You could perform calculations in a query, but keep in mind that the
subform query would need to reference a field on the main form, so would
not be able to run on its own. If you try to open the query by itself
(when frmMain is not open) you will receive a parameter prompt.

Thanks everyone, but I'm not sure I've explained myself particularly
well, so i'll give it a better shot...

I have a main form which displays quotation records. I have on that
form a text box into which I enter a day rate for my time. Also on the
form I have a subform which is in datasheet view which allows me to list
on each row each service I wish to include in the quotation. Each
record of the subform allows me to state the services (from a
dropdowncombo), the number of days that service will take and there is a
third box whichdisplays the result of multiplying the number of days by
the day rate (which is on the main form). This all works fine.

My problem is that I wish to have totals boxes below the number of days
and the service cost, so for example if I set a day rate of £200 and
then in my subform select the following:-

Service No of Days Total
-------- ------------ ------
A 2 400
B 3 600
C 2 400

Then in my footer of the subform (or back on the main form - not sure
which is best) I want total boxes for each column. e.g

------------ ------
7 1400

Some questions...

Should the subform be based on a table or on a query?
Should the total boxes be in the subform footer or back on the main
form?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007


Repeat the calculation in the text box in the footer:
=Sum([DayRate]*[NumberOfDays])

Rui

The txtbox performing the calc is on the footer of the same form.
I've tried to reference the form the control is on but still get an
error. Didn't think you needed to reference a form if the controls
were on the same one?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
you need to refer which form you are accessing the info from.

ie. sum (form1![txtfld])

:

Ive got a txtbox called [dayrate] on my main form. I have a subform
which
is now set to continuous which contains number of days txtbox, price
txtbox
and a cbobox containing all our services.

The idea is to set the day rate on the main form and then select
various
services (one on each line of the continuous form) and allocate
number of
days to each service. The price txtbox will then calculate each
service
cost by multiplying the day rate by number of days - I've got all
that
working fine.

The problem is that I have added another unbound txt box to the
subform
footer (not sure if it should be on the main form) which will total
up all
the price txtboxes on the sub form to give a total overall price.
The
control source of the unbound txtbox is currently set to :

=Sum([txtPrice])

but this returns an error. What am I doing wrong?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[BruceM]

I didn't express something very well. If a control on the continuous
subform contains the calculation:
=[FieldDays]*Forms![frmTenders]![txtFieldDayRate]
then the text box in the footer will sum the subform field and multiply that
result by the main form value. I showed it as I intended, but did not
describe it well.
Also I note a mistake in the GrandTotal formula. I added the Office Days
calculation to itself rather than to the Field Days calculation. It should
have been:

=(Sum([FieldDays])*Forms![frmTenders]![txtFieldDayRate]) +
(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate])

Sum the fields, not the controls. If a control on the continuous subform
contains a calculation, sum that calculation in the footer:

=Sum([FieldDays])*Forms![frmTenders]![txtFieldDayRate]
Similarly for the office charges:
=Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate]

I will call these two text boxes in the footer txtSumFieldDays and
txtSumOfficeDays. Another unbound text box in the footer could contain:
=[txtSumFieldDays]+[txtSumOfficeDays]

If you want only one GrandTotal text box in the footer it could have:

=(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate]) +
(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate])

The line break here is an attempt at clarity. The expression will be all
on one line.

Again, note carefully that the Sum is used only on fields, not on
controls. I'm not usre what will happen if the field and the control have
the same name, but in any case the field and the control should have
different names.

Bruce

Ok this is driving me insane! Everything you have said makes perfect
sense to me and thats how I thought it would work, but it doesn't!

I tried to simplify my problem with the example I set out before but it
may be more prudent to explain using the real names etc as this is
driving me crazy.

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 =[txtFieldDays]*Forms!frmTenders!txtFieldDayRate. 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 =[txtFieldFee]+[txtOfficeFee]. 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.

The problem is that I would like a a total 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
placed txt boxes in the footer of the subform with the following control
sources:-

txtTotalFieldDays =Sum([txtFieldDays])
txtTotalFieldFee =Sum([txtFieldFee])
txtTotalOfficeDays =Sum([txtOfficeDays])
txtTotalOfficeFee =Sum([txtOfficeFee])
txtTotalFee =Sum([txtTotal])

...and each displays #Error in the form.

4 questions...

Why does using a query as the record source of the subform not allow me
to add records to the subform, whereas a table does?
Is the problem because I'm asking fields to calculate values from fields
which are also calculating values?
If the answer to Q2 is No then where am I going wrong?
If the answer to Q2 is Yes then how do you get around such a problem.

I appreciate this is a pretty long explanation of what looks like a
simple problem but I'm fast going bald over this!
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

Basing the subform on a query rather than a table lets you sort,
calculate, concatenate, and otherwise manipulate the data, but does not
affect the data differently than if you base the subform on a table.
The totals boxes should be in the subform. You could put them anywhere,
but since the records are in the subform it is simplest to put the
totals text boxes there. If the No of Days field is NoOfDays, a text
box in the subform footer could have:
=Sum(NoOfDays)
The Totals text box presumably contains a calculation such as:
=Forms![frmMain]![txtDayRate] * NoOfDays

In the subform footer, the text box that is to contain the number 1400
would have something such as:
=Sum(NoOfDays) * Forms![frmMain]![txtDayRate]

You could perform calculations in a query, but keep in mind that the
subform query would need to reference a field on the main form, so would
not be able to run on its own. If you try to open the query by itself
(when frmMain is not open) you will receive a parameter prompt.


Thanks everyone, but I'm not sure I've explained myself particularly
well, so i'll give it a better shot...

I have a main form which displays quotation records. I have on that
form a text box into which I enter a day rate for my time. Also on the
form I have a subform which is in datasheet view which allows me to
list on each row each service I wish to include in the quotation. Each
record of the subform allows me to state the services (from a
dropdowncombo), the number of days that service will take and there is
a third box whichdisplays the result of multiplying the number of days
by the day rate (which is on the main form). This all works fine.

My problem is that I wish to have totals boxes below the number of days
and the service cost, so for example if I set a day rate of £200 and
then in my subform select the following:-

Service No of Days Total
-------- ------------ ------
A 2 400
B 3 600
C 2 400

Then in my footer of the subform (or back on the main form - not sure
which is best) I want total boxes for each column. e.g

------------ ------
7 1400

Some questions...

Should the subform be based on a table or on a query?
Should the total boxes be in the subform footer or back on the main
form?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007


Repeat the calculation in the text box in the footer:
=Sum([DayRate]*[NumberOfDays])

Rui

The txtbox performing the calc is on the footer of the same form.
I've tried to reference the form the control is on but still get an
error. Didn't think you needed to reference a form if the controls
were on the same one?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
you need to refer which form you are accessing the info from.

ie. sum (form1![txtfld])

:

Ive got a txtbox called [dayrate] on my main form. I have a
subform which
is now set to continuous which contains number of days txtbox,
price txtbox
and a cbobox containing all our services.

The idea is to set the day rate on the main form and then select
various
services (one on each line of the continuous form) and allocate
number of
days to each service. The price txtbox will then calculate each
service
cost by multiplying the day rate by number of days - I've got all
that
working fine.

The problem is that I have added another unbound txt box to the
subform
footer (not sure if it should be on the main form) which will total
up all
the price txtboxes on the sub form to give a total overall price.
The
control source of the unbound txtbox is currently set to :

=Sum([txtPrice])

but this returns an error. What am I doing wrong?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[Andy Roberts]

Bruce

Thanks for this I've finally got it working with your help. Two final
questions (I promise).

1. When I change a value in any of the input boxes for the calculations
everything updates automatically except the fields in the footer of the
subform - I have to click in each footer control for it to update. How do I
get them to update automatically?

2. If I add several services to the subform (one after the other) and then
decide I don't want one of them, how do I delete one I don't want?

Thanks for all your help on this.
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

I didn't express something very well. If a control on the continuous
subform contains the calculation:
=[FieldDays]*Forms![frmTenders]![txtFieldDayRate]
then the text box in the footer will sum the subform field and multiply
that result by the main form value. I showed it as I intended, but did
not describe it well.
Also I note a mistake in the GrandTotal formula. I added the Office Days
calculation to itself rather than to the Field Days calculation. It
should have been:

=(Sum([FieldDays])*Forms![frmTenders]![txtFieldDayRate]) +
(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate])

Sum the fields, not the controls. If a control on the continuous subform
contains a calculation, sum that calculation in the footer:

=Sum([FieldDays])*Forms![frmTenders]![txtFieldDayRate]
Similarly for the office charges:
=Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate]

I will call these two text boxes in the footer txtSumFieldDays and
txtSumOfficeDays. Another unbound text box in the footer could contain:
=[txtSumFieldDays]+[txtSumOfficeDays]

If you want only one GrandTotal text box in the footer it could have:

=(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate]) +
(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate])

The line break here is an attempt at clarity. The expression will be all
on one line.

Again, note carefully that the Sum is used only on fields, not on
controls. I'm not usre what will happen if the field and the control have
the same name, but in any case the field and the control should have
different names.

Bruce

Ok this is driving me insane! Everything you have said makes perfect
sense to me and thats how I thought it would work, but it doesn't!

I tried to simplify my problem with the example I set out before but it
may be more prudent to explain using the real names etc as this is
driving me crazy.

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 =[txtFieldDays]*Forms!frmTenders!txtFieldDayRate. 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 =[txtFieldFee]+[txtOfficeFee]. 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.

The problem is that I would like a a total 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
placed txt boxes in the footer of the subform with the following control
sources:-

txtTotalFieldDays =Sum([txtFieldDays])
txtTotalFieldFee =Sum([txtFieldFee])
txtTotalOfficeDays =Sum([txtOfficeDays])
txtTotalOfficeFee =Sum([txtOfficeFee])
txtTotalFee =Sum([txtTotal])

...and each displays #Error in the form.

4 questions...

Why does using a query as the record source of the subform not allow me
to add records to the subform, whereas a table does?
Is the problem because I'm asking fields to calculate values from
fields which are also calculating values?
If the answer to Q2 is No then where am I going wrong?
If the answer to Q2 is Yes then how do you get around such a problem.

I appreciate this is a pretty long explanation of what looks like a
simple problem but I'm fast going bald over this!
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

Basing the subform on a query rather than a table lets you sort,
calculate, concatenate, and otherwise manipulate the data, but does not
affect the data differently than if you base the subform on a table.
The totals boxes should be in the subform. You could put them
anywhere, but since the records are in the subform it is simplest to
put the totals text boxes there. If the No of Days field is NoOfDays,
a text box in the subform footer could have:
=Sum(NoOfDays)
The Totals text box presumably contains a calculation such as:
=Forms![frmMain]![txtDayRate] * NoOfDays

In the subform footer, the text box that is to contain the number 1400
would have something such as:
=Sum(NoOfDays) * Forms![frmMain]![txtDayRate]

You could perform calculations in a query, but keep in mind that the
subform query would need to reference a field on the main form, so
would not be able to run on its own. If you try to open the query by
itself (when frmMain is not open) you will receive a parameter prompt.


Thanks everyone, but I'm not sure I've explained myself particularly
well, so i'll give it a better shot...

I have a main form which displays quotation records. I have on that
form a text box into which I enter a day rate for my time. Also on the
form I have a subform which is in datasheet view which allows me to
list on each row each service I wish to include in the quotation.
Each record of the subform allows me to state the services (from a
dropdowncombo), the number of days that service will take and there is
a third box whichdisplays the result of multiplying the number of days
by the day rate (which is on the main form). This all works fine.

My problem is that I wish to have totals boxes below the number of
days and the service cost, so for example if I set a day rate of £200
and then in my subform select the following:-

Service No of Days Total
-------- ------------ ------
A 2 400
B 3 600
C 2 400

Then in my footer of the subform (or back on the main form - not sure
which is best) I want total boxes for each column. e.g

------------ ------
7 1400

Some questions...

Should the subform be based on a table or on a query?
Should the total boxes be in the subform footer or back on the main
form?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007


Repeat the calculation in the text box in the footer:
=Sum([DayRate]*[NumberOfDays])

Rui

The txtbox performing the calc is on the footer of the same form.
I've tried to reference the form the control is on but still get an
error. Didn't think you needed to reference a form if the controls
were on the same one?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
you need to refer which form you are accessing the info from.

ie. sum (form1![txtfld])

:

Ive got a txtbox called [dayrate] on my main form. I have a
subform which
is now set to continuous which contains number of days txtbox,
price txtbox
and a cbobox containing all our services.

The idea is to set the day rate on the main form and then select
various
services (one on each line of the continuous form) and allocate
number of
days to each service. The price txtbox will then calculate each
service
cost by multiplying the day rate by number of days - I've got all
that
working fine.

The problem is that I have added another unbound txt box to the
subform
footer (not sure if it should be on the main form) which will
total up all
the price txtboxes on the sub form to give a total overall price.
The
control source of the unbound txtbox is currently set to :

=Sum([txtPrice])

but this returns an error. What am I doing wrong?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[BruceM]

I expect the recalculation problem occurs when you update the main form
information that is included in the subform calculation. I see you are
using access 2007. I am not very familiar with that version, so maybe the
problem occurs when you change the subform controls too. In any case, I
expect you can solve the problem by adding Me.Refresh to the After Update
event for the text box or combo box containing the changed value.

To delete the subform record, click into it, then use the Delete Record
command from the Edit menu or the Delete Record icon on the toolbar. If you
have Record Selectors (the option is set on the Format tab of the Property
Sheet for the form), click the selector next to the record, then press the
Delte button on the keyboard (or use the Delete Record command as
described). Another option is to use a command button with the following
code in its Click event:
DoCmd.RunCommand acCmdDeleteRecord

In any case you probably want to guard against accidental or unauthorized
deletions, depending on how many people will be using the database. The
details of how you would do that depend on the situation.

Bruce

Thanks for this I've finally got it working with your help. Two final
questions (I promise).

1. When I change a value in any of the input boxes for the calculations
everything updates automatically except the fields in the footer of the
subform - I have to click in each footer control for it to update. How do
I get them to update automatically?

2. If I add several services to the subform (one after the other) and then
decide I don't want one of them, how do I delete one I don't want?

Thanks for all your help on this.
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

I didn't express something very well. If a control on the continuous
subform contains the calculation:
=[FieldDays]*Forms![frmTenders]![txtFieldDayRate]
then the text box in the footer will sum the subform field and multiply
that result by the main form value. I showed it as I intended, but did
not describe it well.
Also I note a mistake in the GrandTotal formula. I added the Office Days
calculation to itself rather than to the Field Days calculation. It
should have been:

=(Sum([FieldDays])*Forms![frmTenders]![txtFieldDayRate]) +
(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate])

Sum the fields, not the controls. If a control on the continuous
subform contains a calculation, sum that calculation in the footer:

=Sum([FieldDays])*Forms![frmTenders]![txtFieldDayRate]
Similarly for the office charges:
=Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate]

I will call these two text boxes in the footer txtSumFieldDays and
txtSumOfficeDays. Another unbound text box in the footer could contain:
=[txtSumFieldDays]+[txtSumOfficeDays]

If you want only one GrandTotal text box in the footer it could have:

=(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate]) +
(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate])

The line break here is an attempt at clarity. The expression will be
all on one line.

Again, note carefully that the Sum is used only on fields, not on
controls. I'm not usre what will happen if the field and the control
have the same name, but in any case the field and the control should
have different names.

Bruce

Ok this is driving me insane! Everything you have said makes perfect
sense to me and thats how I thought it would work, but it doesn't!

I tried to simplify my problem with the example I set out before but it
may be more prudent to explain using the real names etc as this is
driving me crazy.

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 =[txtFieldDays]*Forms!frmTenders!txtFieldDayRate. 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 =[txtFieldFee]+[txtOfficeFee]. 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.

The problem is that I would like a a total 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 placed txt boxes in the footer of the subform with the following
control sources:-

txtTotalFieldDays =Sum([txtFieldDays])
txtTotalFieldFee =Sum([txtFieldFee])
txtTotalOfficeDays =Sum([txtOfficeDays])
txtTotalOfficeFee =Sum([txtOfficeFee])
txtTotalFee =Sum([txtTotal])

...and each displays #Error in the form.

4 questions...

Why does using a query as the record source of the subform not allow me
to add records to the subform, whereas a table does?
Is the problem because I'm asking fields to calculate values from
fields which are also calculating values?
If the answer to Q2 is No then where am I going wrong?
If the answer to Q2 is Yes then how do you get around such a problem.

I appreciate this is a pretty long explanation of what looks like a
simple problem but I'm fast going bald over this!
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

Basing the subform on a query rather than a table lets you sort,
calculate, concatenate, and otherwise manipulate the data, but does
not affect the data differently than if you base the subform on a
table.
The totals boxes should be in the subform. You could put them
anywhere, but since the records are in the subform it is simplest to
put the totals text boxes there. If the No of Days field is NoOfDays,
a text box in the subform footer could have:
=Sum(NoOfDays)
The Totals text box presumably contains a calculation such as:
=Forms![frmMain]![txtDayRate] * NoOfDays

In the subform footer, the text box that is to contain the number 1400
would have something such as:
=Sum(NoOfDays) * Forms![frmMain]![txtDayRate]

You could perform calculations in a query, but keep in mind that the
subform query would need to reference a field on the main form, so
would not be able to run on its own. If you try to open the query by
itself (when frmMain is not open) you will receive a parameter prompt.


Thanks everyone, but I'm not sure I've explained myself particularly
well, so i'll give it a better shot...

I have a main form which displays quotation records. I have on that
form a text box into which I enter a day rate for my time. Also on
the form I have a subform which is in datasheet view which allows me
to list on each row each service I wish to include in the quotation.
Each record of the subform allows me to state the services (from a
dropdowncombo), the number of days that service will take and there
is a third box whichdisplays the result of multiplying the number of
days by the day rate (which is on the main form). This all works
fine.

My problem is that I wish to have totals boxes below the number of
days and the service cost, so for example if I set a day rate of £200
and then in my subform select the following:-

Service No of Days Total
-------- ------------ ------
A 2 400
B 3 600
C 2 400

Then in my footer of the subform (or back on the main form - not sure
which is best) I want total boxes for each column. e.g

------------ ------
7 1400

Some questions...

Should the subform be based on a table or on a query?
Should the total boxes be in the subform footer or back on the main
form?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007


Repeat the calculation in the text box in the footer:
=Sum([DayRate]*[NumberOfDays])

Rui

The txtbox performing the calc is on the footer of the same form.
I've tried to reference the form the control is on but still get an
error. Didn't think you needed to reference a form if the controls
were on the same one?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
you need to refer which form you are accessing the info from.

ie. sum (form1![txtfld])

:

Ive got a txtbox called [dayrate] on my main form. I have a
subform which
is now set to continuous which contains number of days txtbox,
price txtbox
and a cbobox containing all our services.

The idea is to set the day rate on the main form and then select
various
services (one on each line of the continuous form) and allocate
number of
days to each service. The price txtbox will then calculate each
service
cost by multiplying the day rate by number of days - I've got all
that
working fine.

The problem is that I have added another unbound txt box to the
subform
footer (not sure if it should be on the main form) which will
total up all
the price txtboxes on the sub form to give a total overall price.
The
control source of the unbound txtbox is currently set to :

=Sum([txtPrice])

but this returns an error. What am I doing wrong?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[BruceM]

On another point, you should be able to use a query as the record source,
If you can't, try editing the query directly. If you can't do so, see here:
http://allenbrowne.com/ser-61.html

Bruce

Thanks for this I've finally got it working with your help. Two final
questions (I promise).

1. When I change a value in any of the input boxes for the calculations
everything updates automatically except the fields in the footer of the
subform - I have to click in each footer control for it to update. How do
I get them to update automatically?

2. If I add several services to the subform (one after the other) and then
decide I don't want one of them, how do I delete one I don't want?

Thanks for all your help on this.
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

I didn't express something very well. If a control on the continuous
subform contains the calculation:
=[FieldDays]*Forms![frmTenders]![txtFieldDayRate]
then the text box in the footer will sum the subform field and multiply
that result by the main form value. I showed it as I intended, but did
not describe it well.
Also I note a mistake in the GrandTotal formula. I added the Office Days
calculation to itself rather than to the Field Days calculation. It
should have been:

=(Sum([FieldDays])*Forms![frmTenders]![txtFieldDayRate]) +
(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate])

Sum the fields, not the controls. If a control on the continuous
subform contains a calculation, sum that calculation in the footer:

=Sum([FieldDays])*Forms![frmTenders]![txtFieldDayRate]
Similarly for the office charges:
=Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate]

I will call these two text boxes in the footer txtSumFieldDays and
txtSumOfficeDays. Another unbound text box in the footer could contain:
=[txtSumFieldDays]+[txtSumOfficeDays]

If you want only one GrandTotal text box in the footer it could have:

=(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate]) +
(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate])

The line break here is an attempt at clarity. The expression will be
all on one line.

Again, note carefully that the Sum is used only on fields, not on
controls. I'm not usre what will happen if the field and the control
have the same name, but in any case the field and the control should
have different names.

Bruce

Ok this is driving me insane! Everything you have said makes perfect
sense to me and thats how I thought it would work, but it doesn't!

I tried to simplify my problem with the example I set out before but it
may be more prudent to explain using the real names etc as this is
driving me crazy.

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 =[txtFieldDays]*Forms!frmTenders!txtFieldDayRate. 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 =[txtFieldFee]+[txtOfficeFee]. 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.

The problem is that I would like a a total 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 placed txt boxes in the footer of the subform with the following
control sources:-

txtTotalFieldDays =Sum([txtFieldDays])
txtTotalFieldFee =Sum([txtFieldFee])
txtTotalOfficeDays =Sum([txtOfficeDays])
txtTotalOfficeFee =Sum([txtOfficeFee])
txtTotalFee =Sum([txtTotal])

...and each displays #Error in the form.

4 questions...

Why does using a query as the record source of the subform not allow me
to add records to the subform, whereas a table does?
Is the problem because I'm asking fields to calculate values from
fields which are also calculating values?
If the answer to Q2 is No then where am I going wrong?
If the answer to Q2 is Yes then how do you get around such a problem.

I appreciate this is a pretty long explanation of what looks like a
simple problem but I'm fast going bald over this!
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

Basing the subform on a query rather than a table lets you sort,
calculate, concatenate, and otherwise manipulate the data, but does
not affect the data differently than if you base the subform on a
table.
The totals boxes should be in the subform. You could put them
anywhere, but since the records are in the subform it is simplest to
put the totals text boxes there. If the No of Days field is NoOfDays,
a text box in the subform footer could have:
=Sum(NoOfDays)
The Totals text box presumably contains a calculation such as:
=Forms![frmMain]![txtDayRate] * NoOfDays

In the subform footer, the text box that is to contain the number 1400
would have something such as:
=Sum(NoOfDays) * Forms![frmMain]![txtDayRate]

You could perform calculations in a query, but keep in mind that the
subform query would need to reference a field on the main form, so
would not be able to run on its own. If you try to open the query by
itself (when frmMain is not open) you will receive a parameter prompt.


Thanks everyone, but I'm not sure I've explained myself particularly
well, so i'll give it a better shot...

I have a main form which displays quotation records. I have on that
form a text box into which I enter a day rate for my time. Also on
the form I have a subform which is in datasheet view which allows me
to list on each row each service I wish to include in the quotation.
Each record of the subform allows me to state the services (from a
dropdowncombo), the number of days that service will take and there
is a third box whichdisplays the result of multiplying the number of
days by the day rate (which is on the main form). This all works
fine.

My problem is that I wish to have totals boxes below the number of
days and the service cost, so for example if I set a day rate of £200
and then in my subform select the following:-

Service No of Days Total
-------- ------------ ------
A 2 400
B 3 600
C 2 400

Then in my footer of the subform (or back on the main form - not sure
which is best) I want total boxes for each column. e.g

------------ ------
7 1400

Some questions...

Should the subform be based on a table or on a query?
Should the total boxes be in the subform footer or back on the main
form?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007


Repeat the calculation in the text box in the footer:
=Sum([DayRate]*[NumberOfDays])

Rui

The txtbox performing the calc is on the footer of the same form.
I've tried to reference the form the control is on but still get an
error. Didn't think you needed to reference a form if the controls
were on the same one?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
you need to refer which form you are accessing the info from.

ie. sum (form1![txtfld])

:

Ive got a txtbox called [dayrate] on my main form. I have a
subform which
is now set to continuous which contains number of days txtbox,
price txtbox
and a cbobox containing all our services.

The idea is to set the day rate on the main form and then select
various
services (one on each line of the continuous form) and allocate
number of
days to each service. The price txtbox will then calculate each
service
cost by multiplying the day rate by number of days - I've got all
that
working fine.

The problem is that I have added another unbound txt box to the
subform
footer (not sure if it should be on the main form) which will
total up all
the price txtboxes on the sub form to give a total overall price.
The
control source of the unbound txtbox is currently set to :

=Sum([txtPrice])

but this returns an error. What am I doing wrong?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[Andy Roberts]

Bruce

Thanks for all your help. Everything I've asked so far works now (i'll look
into the query). However, I have a related problem which I started a new
thread about (Form/Subform Problem) on 21st. Its directly related but I
started a new thread as I didn't want to take the mikey with you.

The new thread has a response but it hasn't solved the problem, I'm hoping
you can shed a little light as youundertsand my main problem.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

I expect the recalculation problem occurs when you update the main form
information that is included in the subform calculation. I see you are
using access 2007. I am not very familiar with that version, so maybe the
problem occurs when you change the subform controls too. In any case, I
expect you can solve the problem by adding Me.Refresh to the After Update
event for the text box or combo box containing the changed value.

To delete the subform record, click into it, then use the Delete Record
command from the Edit menu or the Delete Record icon on the toolbar. If
you have Record Selectors (the option is set on the Format tab of the
Property Sheet for the form), click the selector next to the record, then
press the Delte button on the keyboard (or use the Delete Record command
as described). Another option is to use a command button with the
following code in its Click event:
DoCmd.RunCommand acCmdDeleteRecord

In any case you probably want to guard against accidental or unauthorized
deletions, depending on how many people will be using the database. The
details of how you would do that depend on the situation.

Bruce

Thanks for this I've finally got it working with your help. Two final
questions (I promise).

1. When I change a value in any of the input boxes for the calculations
everything updates automatically except the fields in the footer of the
subform - I have to click in each footer control for it to update. How
do I get them to update automatically?

2. If I add several services to the subform (one after the other) and
then decide I don't want one of them, how do I delete one I don't want?

Thanks for all your help on this.
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

I didn't express something very well. If a control on the continuous
subform contains the calculation:
=[FieldDays]*Forms![frmTenders]![txtFieldDayRate]
then the text box in the footer will sum the subform field and multiply
that result by the main form value. I showed it as I intended, but did
not describe it well.
Also I note a mistake in the GrandTotal formula. I added the Office
Days calculation to itself rather than to the Field Days calculation.
It should have been:

=(Sum([FieldDays])*Forms![frmTenders]![txtFieldDayRate]) +
(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate])

Sum the fields, not the controls. If a control on the continuous
subform contains a calculation, sum that calculation in the footer:

=Sum([FieldDays])*Forms![frmTenders]![txtFieldDayRate]
Similarly for the office charges:
=Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate]

I will call these two text boxes in the footer txtSumFieldDays and
txtSumOfficeDays. Another unbound text box in the footer could
contain:
=[txtSumFieldDays]+[txtSumOfficeDays]

If you want only one GrandTotal text box in the footer it could have:

=(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate]) +
(Sum([OfficeDays])*Forms![frmTenders]![txtOfficeDayRate])

The line break here is an attempt at clarity. The expression will be
all on one line.

Again, note carefully that the Sum is used only on fields, not on
controls. I'm not usre what will happen if the field and the control
have the same name, but in any case the field and the control should
have different names.

Bruce

Ok this is driving me insane! Everything you have said makes perfect
sense to me and thats how I thought it would work, but it doesn't!

I tried to simplify my problem with the example I set out before but
it may be more prudent to explain using the real names etc as this is
driving me crazy.

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 =[txtFieldDays]*Forms!frmTenders!txtFieldDayRate. 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 =[txtFieldFee]+[txtOfficeFee]. 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.

The problem is that I would like a a total 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 placed txt boxes in the footer of the subform with the following
control sources:-

txtTotalFieldDays =Sum([txtFieldDays])
txtTotalFieldFee =Sum([txtFieldFee])
txtTotalOfficeDays =Sum([txtOfficeDays])
txtTotalOfficeFee =Sum([txtOfficeFee])
txtTotalFee =Sum([txtTotal])

...and each displays #Error in the form.

4 questions...

Why does using a query as the record source of the subform not allow
me to add records to the subform, whereas a table does?
Is the problem because I'm asking fields to calculate values from
fields which are also calculating values?
If the answer to Q2 is No then where am I going wrong?
If the answer to Q2 is Yes then how do you get around such a problem.

I appreciate this is a pretty long explanation of what looks like a
simple problem but I'm fast going bald over this!
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

Basing the subform on a query rather than a table lets you sort,
calculate, concatenate, and otherwise manipulate the data, but does
not affect the data differently than if you base the subform on a
table.
The totals boxes should be in the subform. You could put them
anywhere, but since the records are in the subform it is simplest to
put the totals text boxes there. If the No of Days field is
NoOfDays, a text box in the subform footer could have:
=Sum(NoOfDays)
The Totals text box presumably contains a calculation such as:
=Forms![frmMain]![txtDayRate] * NoOfDays

In the subform footer, the text box that is to contain the number
1400 would have something such as:
=Sum(NoOfDays) * Forms![frmMain]![txtDayRate]

You could perform calculations in a query, but keep in mind that the
subform query would need to reference a field on the main form, so
would not be able to run on its own. If you try to open the query by
itself (when frmMain is not open) you will receive a parameter
prompt.


Thanks everyone, but I'm not sure I've explained myself particularly
well, so i'll give it a better shot...

I have a main form which displays quotation records. I have on that
form a text box into which I enter a day rate for my time. Also on
the form I have a subform which is in datasheet view which allows me
to list on each row each service I wish to include in the quotation.
Each record of the subform allows me to state the services (from a
dropdowncombo), the number of days that service will take and there
is a third box whichdisplays the result of multiplying the number of
days by the day rate (which is on the main form). This all works
fine.

My problem is that I wish to have totals boxes below the number of
days and the service cost, so for example if I set a day rate of
£200 and then in my subform select the following:-

Service No of Days Total
-------- ------------ ------
A 2 400
B 3 600
C 2 400

Then in my footer of the subform (or back on the main form - not
sure which is best) I want total boxes for each column. e.g

------------ ------
7 1400

Some questions...

Should the subform be based on a table or on a query?
Should the total boxes be in the subform footer or back on the main
form?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007


Repeat the calculation in the text box in the footer:
=Sum([DayRate]*[NumberOfDays])

Rui

The txtbox performing the calc is on the footer of the same form.
I've tried to reference the form the control is on but still get
an error. Didn't think you needed to reference a form if the
controls were on the same one?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
you need to refer which form you are accessing the info from.

ie. sum (form1![txtfld])

:

Ive got a txtbox called [dayrate] on my main form. I have a
subform which
is now set to continuous which contains number of days txtbox,
price txtbox
and a cbobox containing all our services.

The idea is to set the day rate on the main form and then select
various
services (one on each line of the continuous form) and allocate
number of
days to each service. The price txtbox will then calculate each
service
cost by multiplying the day rate by number of days - I've got
all that
working fine.

The problem is that I have added another unbound txt box to the
subform
footer (not sure if it should be on the main form) which will
total up all
the price txtboxes on the sub form to give a total overall
price. The
control source of the unbound txtbox is currently set to :

=Sum([txtPrice])

but this returns an error. What am I doing wrong?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007