Complicated Form Calc

[Andy Roberts]

I have a form which calculates a total price for various services I provide
based on a day rate. I have a bound day rate box on the main form and a
subform (datasheet view), based on a table, which allows me to select a
service and state number of days. It then has an unbound field which
multiplies the no of days with the day rate on the main form. I can then
add a different service on the next lineof the subform and so on. This
works fine.

The problem occurs when I want to total all the unbound calculated fields in
the subform in an unbound text box on the main form. Can someone help?

I could also do with then having an extra field on the subform which tells
me what percentage of the total cost each service is

Regards

Andy

 

[Wayne-I-M]

The problem occurs

Whats the problem ??

 

[Andy Roberts]

Wayne

There isn't a specific problem as such, just don't know where to start to
put the calc together

Andy

 

[Wayne-I-M]

Hmmmmm - not really much I can offer on this (nthing to go on). So just the
genral then.

1st why are you using a datasheet as a subform. They are really useful for
some things but I think (may be wrong) that may be better in this case with a
form set to continous form.

1st - make a make a copy of your database and work on that.

So open the subform in design view a make it a continous form - you will
have to send a little time getting it to look like you want.

Next (once again may be wrong) but it seems to me that all your "stuff" in
on the sub form other than than a field on the main form [your_day_rate].

In design view - Create an unbound text box on the subform - as you set it
to contious this will be shown on each line

I assume you have a box on the sub form called [number_of_days].

Put this as the control source for the new unbound box you have just made
=(Forms![MainFormName]![your_day_rate]*
[Forms]![MainFormName]![SubFormName].[Form]![number_of_days]

That will bring the [your_day_rate] from the main form to the calculation on
the subform.

Other than that I can't be much help as I do have any other information to
go on. You can save the form(s) then click the new text box and go to the
source row and select build (...). The use the calculation builder to help
you with your calculation.

Hope this helps a little

Good luck

 

[Andy Roberts]

Wayne

Thanks for your suggestion and apologies for the lack of detail. Heres what
I've done so far...
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

Hmmmmm - not really much I can offer on this (nthing to go on). So just
the
genral then.

1st why are you using a datasheet as a subform. They are really useful
for
some things but I think (may be wrong) that may be better in this case
with a
form set to continous form.

1st - make a make a copy of your database and work on that.

So open the subform in design view a make it a continous form - you will
have to send a little time getting it to look like you want.

Next (once again may be wrong) but it seems to me that all your "stuff" in
on the sub form other than than a field on the main form [your_day_rate].

In design view - Create an unbound text box on the subform - as you set it
to contious this will be shown on each line

I assume you have a box on the sub form called [number_of_days].

Put this as the control source for the new unbound box you have just made
=(Forms![MainFormName]![your_day_rate]*
[Forms]![MainFormName]![SubFormName].[Form]![number_of_days]

That will bring the [your_day_rate] from the main form to the calculation
on
the subform.

Other than that I can't be much help as I do have any other information
to
go on. You can save the form(s) then click the new text box and go to the
source row and select build (...). The use the calculation builder to
help
you with your calculation.

Hope this helps a little

Good luck

--
Wayne
Manchester, England.

Wayne

There isn't a specific problem as such, just don't know where to start to
put the calc together

Andy