Product in main form into subform

[Guest]

Hi, I'm using Access 2000. I printed Allen Browne's answer to Richard
Horne's sum function and tried to use it in my situation which is: I want to
multiply BalanceDue(a text box on main form) by 10% and put it in my subform.
I have a textbox in the footer of my main form:
name: MaterialCost
control source: =[BalanceDue]*0.1
format: Currency
and above part is working perfect.
Then in my subform I have a textbox:
name: DenverMaterial
control source: =[Invoices Form].[Form].[MaterialCost]
As soon as it's saved it's converted to [Invoices Form].Form.MaterialCost
and displayed in field is"#Name?" Thanks

 

[Marshall Barton]

Hi, I'm using Access 2000. I printed Allen Browne's answer to Richard
Horne's sum function and tried to use it in my situation which is: I want to
multiply BalanceDue(a text box on main form) by 10% and put it in my subform.
I have a textbox in the footer of my main form:
name: MaterialCost
control source: =[BalanceDue]*0.1
format: Currency
and above part is working perfect.
Then in my subform I have a textbox:
name: DenverMaterial
control source: =[Invoices Form].[Form].[MaterialCost]
As soon as it's saved it's converted to [Invoices Form].Form.MaterialCost
and displayed in field is"#Name?" Thanks

The syntax for a full reference would be:
=Forms![Invoices Form].[MaterialCost]

Or a shorter form of that for use in a subform:
=Parent.MaterialCost

 

[Guest]

Hi, I'm using Access 2000. I printed Allen Browne's answer to Richard
Horne's sum function and tried to use it in my situation which is: I want to
multiply BalanceDue(a text box on main form) by 10% and put it in my subform.
I have a textbox in the footer of my main form:
name: MaterialCost
control source: =[BalanceDue]*0.1
format: Currency
and above part is working perfect.
Then in my subform I have a textbox:
name: DenverMaterial
control source: =[Invoices Form].[Form].[MaterialCost]
As soon as it's saved it's converted to [Invoices Form].Form.MaterialCost
and displayed in field is"#Name?" Thanks

The syntax for a full reference would be:
=Forms![Invoices Form].[MaterialCost]

Or a shorter form of that for use in a subform:
=Parent.MaterialCost

JIM

 

[Guest]

Hi, I'm using Access 2000. I printed Allen Browne's answer to Richard
Horne's sum function and tried to use it in my situation which is: I want to
multiply BalanceDue(a text box on main form) by 10% and put it in my subform.
I have a textbox in the footer of my main form:
name: MaterialCost
control source: =[BalanceDue]*0.1
format: Currency
and above part is working perfect.
Then in my subform I have a textbox:
name: DenverMaterial
control source: =[Invoices Form].[Form].[MaterialCost]
As soon as it's saved it's converted to [Invoices Form].Form.MaterialCost
and displayed in field is"#Name?" Thanks

The syntax for a full reference would be:
=Forms![Invoices Form].[MaterialCost]

Or a shorter form of that for use in a subform:
=Parent.MaterialCost

 

[Marshall Barton]

Hi, I'm using Access 2000. I printed Allen Browne's answer to Richard
Horne's sum function and tried to use it in my situation which is: I want to
multiply BalanceDue(a text box on main form) by 10% and put it in my subform.
I have a textbox in the footer of my main form:
name: MaterialCost
control source: =[BalanceDue]*0.1
format: Currency
and above part is working perfect.
Then in my subform I have a textbox:
name: DenverMaterial
control source: =[Invoices Form].[Form].[MaterialCost]
As soon as it's saved it's converted to [Invoices Form].Form.MaterialCost
and displayed in field is"#Name?" Thanks

The syntax for a full reference would be:
=Forms![Invoices Form].[MaterialCost]

Or a shorter form of that for use in a subform:
=Parent.MaterialCost

The second suggestion with Parent.MaterialCost ,
seemingly, worked but couldn't get the first one to work
at all. On further checking my data though, only the first
record in a one to many relationship is updated with
MaterialCost. In other words, there is only one record in
subform file that is updated with MaterialCost even though
many records are created with other info that is typed in.
Some subforms have no input at all and a subform record
is not created so that might have some bearing.

You have completely lost me here. The expression we have
been discussing has nothing to do with saving any data to a
table.

I do not have enough information to fully understand your
one to many relationship. How a main form material cost can
relate to every record in the subform makes no sense to me.
Actually, this kind of calculated value should only rarely
be saved to a table and I just can't tell if this is one of
those rare cases.

 

[Guest]

Here's my situation: this is an invoicing module. If work was done in Denver
a check box is checked. If check box is true then balance due is multiplied
by 10% which total is put into a textbox, MaterialCost, in invisible footer.
The subform is bound to a table called DenverTax. If data entry person
enters a check in check box and after all info for invoice is entered then
employee, hours and 10% of balance due is entered in a subform. The reason
it's designed this way is because there are comparatively few invoices on
which we pay the Denver tax. The data entry person doesn't have to enter
subform for most invoices and it doesn't seem that data entry should figure
the 10% either or have to go thru the invoices 2 times to get the info, thus
the separate file. I'd like to design subform so that only employee(a drop
down combo box) and hours be entered and material will be filled in from
MaterialCost from parent form. When invoices form is completed it appears to
be working. On the first continuous form of subform of every Denver invoice
the 10% of balancedue appears correctly as it also appears in the footer. So
when this was tested it looked like everything was working. But when I
looked at my table, DenverTax, the only record updated with material cost(or
10% of balance due) was the first record of subform from first Denver invoice
done in batch.

JIM wrote:

Hi, I'm using Access 2000. I printed Allen Browne's answer to Richard
Horne's sum function and tried to use it in my situation which is: I want to
multiply BalanceDue(a text box on main form) by 10% and put it in my subform.
I have a textbox in the footer of my main form:
name: MaterialCost
control source: =[BalanceDue]*0.1
format: Currency
and above part is working perfect.
Then in my subform I have a textbox:
name: DenverMaterial
control source: =[Invoices Form].[Form].[MaterialCost]
As soon as it's saved it's converted to [Invoices Form].Form.MaterialCost
and displayed in field is"#Name?" Thanks


The syntax for a full reference would be:
=Forms![Invoices Form].[MaterialCost]

Or a shorter form of that for use in a subform:
=Parent.MaterialCost

The second suggestion with Parent.MaterialCost ,
seemingly, worked but couldn't get the first one to work
at all. On further checking my data though, only the first
record in a one to many relationship is updated with
MaterialCost. In other words, there is only one record in
subform file that is updated with MaterialCost even though
many records are created with other info that is typed in.
Some subforms have no input at all and a subform record
is not created so that might have some bearing.

You have completely lost me here. The expression we have
been discussing has nothing to do with saving any data to a
table.

I do not have enough information to fully understand your
one to many relationship. How a main form material cost can
relate to every record in the subform makes no sense to me.
Actually, this kind of calculated value should only rarely
be saved to a table and I just can't tell if this is one of
those rare cases.

 

[Marshall Barton]

I still disagree about saving that calculated value in the
subform's table, especially since it would be saved to every
record. If it needs to be saved anywhere, it would be in
the main form's table so there would only be one copy of the
value.

The general rule is that you should recalculate the value
anytime you want to display it for users to see.

In any case, if you want to save a calculated value to a
table, you can not use control expressions to do the
calculation. Instead you need to do it in VBA code in the
form's BeforeUpdate event.
--
Marsh
MVP [MS Access]

Here's my situation: this is an invoicing module. If work was done in Denver
a check box is checked. If check box is true then balance due is multiplied
by 10% which total is put into a textbox, MaterialCost, in invisible footer.
The subform is bound to a table called DenverTax. If data entry person
enters a check in check box and after all info for invoice is entered then
employee, hours and 10% of balance due is entered in a subform. The reason
it's designed this way is because there are comparatively few invoices on
which we pay the Denver tax. The data entry person doesn't have to enter
subform for most invoices and it doesn't seem that data entry should figure
the 10% either or have to go thru the invoices 2 times to get the info, thus
the separate file. I'd like to design subform so that only employee(a drop
down combo box) and hours be entered and material will be filled in from
MaterialCost from parent form. When invoices form is completed it appears to
be working. On the first continuous form of subform of every Denver invoice
the 10% of balancedue appears correctly as it also appears in the footer. So
when this was tested it looked like everything was working. But when I
looked at my table, DenverTax, the only record updated with material cost(or
10% of balance due) was the first record of subform from first Denver invoice
done in batch.

:

JIM wrote:

Hi, I'm using Access 2000. I printed Allen Browne's answer to Richard
Horne's sum function and tried to use it in my situation which is: I want to
multiply BalanceDue(a text box on main form) by 10% and put it in my subform.
I have a textbox in the footer of my main form:
name: MaterialCost
control source: =[BalanceDue]*0.1
format: Currency
and above part is working perfect.
Then in my subform I have a textbox:
name: DenverMaterial
control source: =[Invoices Form].[Form].[MaterialCost]
As soon as it's saved it's converted to [Invoices Form].Form.MaterialCost
and displayed in field is"#Name?" Thanks


The syntax for a full reference would be:
=Forms![Invoices Form].[MaterialCost]

Or a shorter form of that for use in a subform:
=Parent.MaterialCost


The second suggestion with Parent.MaterialCost ,
seemingly, worked but couldn't get the first one to work
at all. On further checking my data though, only the first
record in a one to many relationship is updated with
MaterialCost. In other words, there is only one record in
subform file that is updated with MaterialCost even though
many records are created with other info that is typed in.
Some subforms have no input at all and a subform record
is not created so that might have some bearing.

You have completely lost me here. The expression we have
been discussing has nothing to do with saving any data to a
table.

I do not have enough information to fully understand your
one to many relationship. How a main form material cost can
relate to every record in the subform makes no sense to me.
Actually, this kind of calculated value should only rarely
be saved to a table and I just can't tell if this is one of
those rare cases.

 

[Guest]

Hi Marsh, ok I'm trying another approach but can't seem to understand how to
do it. Using Access 2000, I wrote a query based on an InvoicePrint query and
DenverTax table. The problem is I want to summarize the invoice file(from
InvoicePrint query) and also a DenverTax table. For my new query fields
"Denver" and "Baldue" are taken from InvoicePrint query and "EmployeeName"
and "Sum of EmployeeHours" are taken from DenverTax table. I specify summary
in the wizard and result is correct summary of EmployeeHours("Sum of
EmployeeHours") and correct summary of "Baldue". The problem is it also
gives something called "First of EmployeeName" and when query is inspected
just prints first of names of employee for each invoice. I need hours
summarized by employee a total baldue for each invoice summarized.
Relationship is one invoice to many employees. JIM

I still disagree about saving that calculated value in the
subform's table, especially since it would be saved to every
record. If it needs to be saved anywhere, it would be in
the main form's table so there would only be one copy of the
value.

The general rule is that you should recalculate the value
anytime you want to display it for users to see.

In any case, if you want to save a calculated value to a
table, you can not use control expressions to do the
calculation. Instead you need to do it in VBA code in the
form's BeforeUpdate event.
--
Marsh
MVP [MS Access]

Here's my situation: this is an invoicing module. If work was done in Denver
a check box is checked. If check box is true then balance due is multiplied
by 10% which total is put into a textbox, MaterialCost, in invisible footer.
The subform is bound to a table called DenverTax. If data entry person
enters a check in check box and after all info for invoice is entered then
employee, hours and 10% of balance due is entered in a subform. The reason
it's designed this way is because there are comparatively few invoices on
which we pay the Denver tax. The data entry person doesn't have to enter
subform for most invoices and it doesn't seem that data entry should figure
the 10% either or have to go thru the invoices 2 times to get the info, thus
the separate file. I'd like to design subform so that only employee(a drop
down combo box) and hours be entered and material will be filled in from
MaterialCost from parent form. When invoices form is completed it appears to
be working. On the first continuous form of subform of every Denver invoice
the 10% of balancedue appears correctly as it also appears in the footer. So
when this was tested it looked like everything was working. But when I
looked at my table, DenverTax, the only record updated with material cost(or
10% of balance due) was the first record of subform from first Denver invoice
done in batch.

JIM wrote:

:

JIM wrote:

Hi, I'm using Access 2000. I printed Allen Browne's answer to Richard
Horne's sum function and tried to use it in my situation which is: I want to
multiply BalanceDue(a text box on main form) by 10% and put it in my subform.
I have a textbox in the footer of my main form:
name: MaterialCost
control source: =[BalanceDue]*0.1
format: Currency
and above part is working perfect.
Then in my subform I have a textbox:
name: DenverMaterial
control source: =[Invoices Form].[Form].[MaterialCost]
As soon as it's saved it's converted to [Invoices Form].Form.MaterialCost
and displayed in field is"#Name?" Thanks


The syntax for a full reference would be:
=Forms![Invoices Form].[MaterialCost]

Or a shorter form of that for use in a subform:
=Parent.MaterialCost


The second suggestion with Parent.MaterialCost ,
seemingly, worked but couldn't get the first one to work
at all. On further checking my data though, only the first
record in a one to many relationship is updated with
MaterialCost. In other words, there is only one record in
subform file that is updated with MaterialCost even though
many records are created with other info that is typed in.
Some subforms have no input at all and a subform record
is not created so that might have some bearing.


You have completely lost me here. The expression we have
been discussing has nothing to do with saving any data to a
table.

I do not have enough information to fully understand your
one to many relationship. How a main form material cost can
relate to every record in the subform makes no sense to me.
Actually, this kind of calculated value should only rarely
be saved to a table and I just can't tell if this is one of
those rare cases.

 

[Marshall Barton]

You will have to fine tune the query using the query design
window or SQL view. Clearly the wizard didn't get it quite
right. I suspect that you want to set the Total line for
employee to Group By instead of first. This will allow you
to sum the hours for each employee (assuming an employee may
have multiple entries per invoice). You will have to
calculate the total for the invoince another way, perhaps in
a subquery or in the form as you were trying to do before.

If you need further assistance, be sure to post a Copy/Paste
of the query's SQL view so I can see what you are talking
about.
--
Marsh
MVP [MS Access]

Hi Marsh, ok I'm trying another approach but can't seem to understand how to
do it. Using Access 2000, I wrote a query based on an InvoicePrint query and
DenverTax table. The problem is I want to summarize the invoice file(from
InvoicePrint query) and also a DenverTax table. For my new query fields
"Denver" and "Baldue" are taken from InvoicePrint query and "EmployeeName"
and "Sum of EmployeeHours" are taken from DenverTax table. I specify summary
in the wizard and result is correct summary of EmployeeHours("Sum of
EmployeeHours") and correct summary of "Baldue". The problem is it also
gives something called "First of EmployeeName" and when query is inspected
just prints first of names of employee for each invoice. I need hours
summarized by employee a total baldue for each invoice summarized.
Relationship is one invoice to many employees.

I still disagree about saving that calculated value in the
subform's table, especially since it would be saved to every
record. If it needs to be saved anywhere, it would be in
the main form's table so there would only be one copy of the
value.

The general rule is that you should recalculate the value
anytime you want to display it for users to see.

In any case, if you want to save a calculated value to a
table, you can not use control expressions to do the
calculation. Instead you need to do it in VBA code in the
form's BeforeUpdate event.

Here's my situation: this is an invoicing module. If work was done in Denver
a check box is checked. If check box is true then balance due is multiplied
by 10% which total is put into a textbox, MaterialCost, in invisible footer.
The subform is bound to a table called DenverTax. If data entry person
enters a check in check box and after all info for invoice is entered then
employee, hours and 10% of balance due is entered in a subform. The reason
it's designed this way is because there are comparatively few invoices on
which we pay the Denver tax. The data entry person doesn't have to enter
subform for most invoices and it doesn't seem that data entry should figure
the 10% either or have to go thru the invoices 2 times to get the info, thus
the separate file. I'd like to design subform so that only employee(a drop
down combo box) and hours be entered and material will be filled in from
MaterialCost from parent form. When invoices form is completed it appears to
be working. On the first continuous form of subform of every Denver invoice
the 10% of balancedue appears correctly as it also appears in the footer. So
when this was tested it looked like everything was working. But when I
looked at my table, DenverTax, the only record updated with material cost(or
10% of balance due) was the first record of subform from first Denver invoice
done in batch.

:

JIM wrote:

:

JIM wrote:

Hi, I'm using Access 2000. I printed Allen Browne's answer to Richard
Horne's sum function and tried to use it in my situation which is: I want to
multiply BalanceDue(a text box on main form) by 10% and put it in my subform.
I have a textbox in the footer of my main form:
name: MaterialCost
control source: =[BalanceDue]*0.1
format: Currency
and above part is working perfect.
Then in my subform I have a textbox:
name: DenverMaterial
control source: =[Invoices Form].[Form].[MaterialCost]
As soon as it's saved it's converted to [Invoices Form].Form.MaterialCost
and displayed in field is"#Name?" Thanks


The syntax for a full reference would be:
=Forms![Invoices Form].[MaterialCost]

Or a shorter form of that for use in a subform:
=Parent.MaterialCost


The second suggestion with Parent.MaterialCost ,
seemingly, worked but couldn't get the first one to work
at all. On further checking my data though, only the first
record in a one to many relationship is updated with
MaterialCost. In other words, there is only one record in
subform file that is updated with MaterialCost even though
many records are created with other info that is typed in.
Some subforms have no input at all and a subform record
is not created so that might have some bearing.


You have completely lost me here. The expression we have
been discussing has nothing to do with saving any data to a
table.

I do not have enough information to fully understand your
one to many relationship. How a main form material cost can
relate to every record in the subform makes no sense to me.
Actually, this kind of calculated value should only rarely
be saved to a table and I just can't tell if this is one of
those rare cases.

 

[Guest]

Hi Marshall, here is what I have so far for a subquery:

SELECT DISTINCTROW GetInvoicesToPrint.Denver,
GetInvoicesToPrint.InvoiceDate, Sum(GetInvoicesToPrint.BalDue) AS [Sum Of
BalDue]
FROM GetInvoicesToPrint
GROUP BY GetInvoicesToPrint.Denver, GetInvoicesToPrint.InvoiceDate;

I would like to include only those records that Denver = Yes (a yes/no
field) and InvoiceDate = 4/1/06-4/30/06. Can you show me how to change SQL
statement to include only those records? Thanks, JIM

You will have to fine tune the query using the query design
window or SQL view. Clearly the wizard didn't get it quite
right. I suspect that you want to set the Total line for
employee to Group By instead of first. This will allow you
to sum the hours for each employee (assuming an employee may
have multiple entries per invoice). You will have to
calculate the total for the invoince another way, perhaps in
a subquery or in the form as you were trying to do before.

If you need further assistance, be sure to post a Copy/Paste
of the query's SQL view so I can see what you are talking
about.
--
Marsh
MVP [MS Access]

Hi Marsh, ok I'm trying another approach but can't seem to understand how to
do it. Using Access 2000, I wrote a query based on an InvoicePrint query and
DenverTax table. The problem is I want to summarize the invoice file(from
InvoicePrint query) and also a DenverTax table. For my new query fields
"Denver" and "Baldue" are taken from InvoicePrint query and "EmployeeName"
and "Sum of EmployeeHours" are taken from DenverTax table. I specify summary
in the wizard and result is correct summary of EmployeeHours("Sum of
EmployeeHours") and correct summary of "Baldue". The problem is it also
gives something called "First of EmployeeName" and when query is inspected
just prints first of names of employee for each invoice. I need hours
summarized by employee a total baldue for each invoice summarized.
Relationship is one invoice to many employees.

I still disagree about saving that calculated value in the
subform's table, especially since it would be saved to every
record. If it needs to be saved anywhere, it would be in
the main form's table so there would only be one copy of the
value.

The general rule is that you should recalculate the value
anytime you want to display it for users to see.

In any case, if you want to save a calculated value to a
table, you can not use control expressions to do the
calculation. Instead you need to do it in VBA code in the
form's BeforeUpdate event.


JIM wrote:
Here's my situation: this is an invoicing module. If work was done in Denver
a check box is checked. If check box is true then balance due is multiplied
by 10% which total is put into a textbox, MaterialCost, in invisible footer.
The subform is bound to a table called DenverTax. If data entry person
enters a check in check box and after all info for invoice is entered then
employee, hours and 10% of balance due is entered in a subform. The reason
it's designed this way is because there are comparatively few invoices on
which we pay the Denver tax. The data entry person doesn't have to enter
subform for most invoices and it doesn't seem that data entry should figure
the 10% either or have to go thru the invoices 2 times to get the info, thus
the separate file. I'd like to design subform so that only employee(a drop
down combo box) and hours be entered and material will be filled in from
MaterialCost from parent form. When invoices form is completed it appears to
be working. On the first continuous form of subform of every Denver invoice
the 10% of balancedue appears correctly as it also appears in the footer. So
when this was tested it looked like everything was working. But when I
looked at my table, DenverTax, the only record updated with material cost(or
10% of balance due) was the first record of subform from first Denver invoice
done in batch.

:

JIM wrote:

:

JIM wrote:

Hi, I'm using Access 2000. I printed Allen Browne's answer to Richard
Horne's sum function and tried to use it in my situation which is: I want to
multiply BalanceDue(a text box on main form) by 10% and put it in my subform.
I have a textbox in the footer of my main form:
name: MaterialCost
control source: =[BalanceDue]*0.1
format: Currency
and above part is working perfect.
Then in my subform I have a textbox:
name: DenverMaterial
control source: =[Invoices Form].[Form].[MaterialCost]
As soon as it's saved it's converted to [Invoices Form].Form.MaterialCost
and displayed in field is"#Name?" Thanks


The syntax for a full reference would be:
=Forms![Invoices Form].[MaterialCost]

Or a shorter form of that for use in a subform:
=Parent.MaterialCost


The second suggestion with Parent.MaterialCost ,
seemingly, worked but couldn't get the first one to work
at all. On further checking my data though, only the first
record in a one to many relationship is updated with
MaterialCost. In other words, there is only one record in
subform file that is updated with MaterialCost even though
many records are created with other info that is typed in.
Some subforms have no input at all and a subform record
is not created so that might have some bearing.


You have completely lost me here. The expression we have
been discussing has nothing to do with saving any data to a
table.

I do not have enough information to fully understand your
one to many relationship. How a main form material cost can
relate to every record in the subform makes no sense to me.
Actually, this kind of calculated value should only rarely
be saved to a table and I just can't tell if this is one of
those rare cases.

 

[Guest]

Actually, I only need one record that summarizes baldue in the whole file:
only records that are Denver=true and dates 04/01/06-04/30/06.
Thanks, JIM

Hi Marshall, here is what I have so far for a subquery:

SELECT DISTINCTROW GetInvoicesToPrint.Denver,
GetInvoicesToPrint.InvoiceDate, Sum(GetInvoicesToPrint.BalDue) AS [Sum Of
BalDue]
FROM GetInvoicesToPrint
GROUP BY GetInvoicesToPrint.Denver, GetInvoicesToPrint.InvoiceDate;

I would like to include only those records that Denver = Yes (a yes/no
field) and InvoiceDate = 4/1/06-4/30/06. Can you show me how to change SQL
statement to include only those records? Thanks, JIM

You will have to fine tune the query using the query design
window or SQL view. Clearly the wizard didn't get it quite
right. I suspect that you want to set the Total line for
employee to Group By instead of first. This will allow you
to sum the hours for each employee (assuming an employee may
have multiple entries per invoice). You will have to
calculate the total for the invoince another way, perhaps in
a subquery or in the form as you were trying to do before.

If you need further assistance, be sure to post a Copy/Paste
of the query's SQL view so I can see what you are talking
about.
--
Marsh
MVP [MS Access]

Hi Marsh, ok I'm trying another approach but can't seem to understand how to
do it. Using Access 2000, I wrote a query based on an InvoicePrint query and
DenverTax table. The problem is I want to summarize the invoice file(from
InvoicePrint query) and also a DenverTax table. For my new query fields
"Denver" and "Baldue" are taken from InvoicePrint query and "EmployeeName"
and "Sum of EmployeeHours" are taken from DenverTax table. I specify summary
in the wizard and result is correct summary of EmployeeHours("Sum of
EmployeeHours") and correct summary of "Baldue". The problem is it also
gives something called "First of EmployeeName" and when query is inspected
just prints first of names of employee for each invoice. I need hours
summarized by employee a total baldue for each invoice summarized.
Relationship is one invoice to many employees.

:
I still disagree about saving that calculated value in the
subform's table, especially since it would be saved to every
record. If it needs to be saved anywhere, it would be in
the main form's table so there would only be one copy of the
value.

The general rule is that you should recalculate the value
anytime you want to display it for users to see.

In any case, if you want to save a calculated value to a
table, you can not use control expressions to do the
calculation. Instead you need to do it in VBA code in the
form's BeforeUpdate event.


JIM wrote:
Here's my situation: this is an invoicing module. If work was done in Denver
a check box is checked. If check box is true then balance due is multiplied
by 10% which total is put into a textbox, MaterialCost, in invisible footer.
The subform is bound to a table called DenverTax. If data entry person
enters a check in check box and after all info for invoice is entered then
employee, hours and 10% of balance due is entered in a subform. The reason
it's designed this way is because there are comparatively few invoices on
which we pay the Denver tax. The data entry person doesn't have to enter
subform for most invoices and it doesn't seem that data entry should figure
the 10% either or have to go thru the invoices 2 times to get the info, thus
the separate file. I'd like to design subform so that only employee(a drop
down combo box) and hours be entered and material will be filled in from
MaterialCost from parent form. When invoices form is completed it appears to
be working. On the first continuous form of subform of every Denver invoice
the 10% of balancedue appears correctly as it also appears in the footer. So
when this was tested it looked like everything was working. But when I
looked at my table, DenverTax, the only record updated with material cost(or
10% of balance due) was the first record of subform from first Denver invoice
done in batch.

:

JIM wrote:

:

JIM wrote:

Hi, I'm using Access 2000. I printed Allen Browne's answer to Richard
Horne's sum function and tried to use it in my situation which is: I want to
multiply BalanceDue(a text box on main form) by 10% and put it in my subform.
I have a textbox in the footer of my main form:
name: MaterialCost
control source: =[BalanceDue]*0.1
format: Currency
and above part is working perfect.
Then in my subform I have a textbox:
name: DenverMaterial
control source: =[Invoices Form].[Form].[MaterialCost]
As soon as it's saved it's converted to [Invoices Form].Form.MaterialCost
and displayed in field is"#Name?" Thanks


The syntax for a full reference would be:
=Forms![Invoices Form].[MaterialCost]

Or a shorter form of that for use in a subform:
=Parent.MaterialCost


The second suggestion with Parent.MaterialCost ,
seemingly, worked but couldn't get the first one to work
at all. On further checking my data though, only the first
record in a one to many relationship is updated with
MaterialCost. In other words, there is only one record in
subform file that is updated with MaterialCost even though
many records are created with other info that is typed in.
Some subforms have no input at all and a subform record
is not created so that might have some bearing.


You have completely lost me here. The expression we have
been discussing has nothing to do with saving any data to a
table.

I do not have enough information to fully understand your
one to many relationship. How a main form material cost can
relate to every record in the subform makes no sense to me.
Actually, this kind of calculated value should only rarely
be saved to a table and I just can't tell if this is one of
those rare cases.

 

[Guest]

I think I figured it out. Is this good or is there a better way? Thanks, JM

SELECT DISTINCTROW GetInvoicesToPrint.Denver, Sum(GetInvoicesToPrint.BalDue)
AS [Sum Of BalDue]
FROM GetInvoicesToPrint
WHERE (((GetInvoicesToPrint.InvoiceDate) Between #4/1/2006# And #4/30/2006#))
GROUP BY GetInvoicesToPrint.Denver
HAVING (((GetInvoicesToPrint.Denver)=True));

Actually, I only need one record that summarizes baldue in the whole file:
only records that are Denver=true and dates 04/01/06-04/30/06.
Thanks, JIM

Hi Marshall, here is what I have so far for a subquery:

SELECT DISTINCTROW GetInvoicesToPrint.Denver,
GetInvoicesToPrint.InvoiceDate, Sum(GetInvoicesToPrint.BalDue) AS [Sum Of
BalDue]
FROM GetInvoicesToPrint
GROUP BY GetInvoicesToPrint.Denver, GetInvoicesToPrint.InvoiceDate;

I would like to include only those records that Denver = Yes (a yes/no
field) and InvoiceDate = 4/1/06-4/30/06. Can you show me how to change SQL
statement to include only those records? Thanks, JIM

You will have to fine tune the query using the query design
window or SQL view. Clearly the wizard didn't get it quite
right. I suspect that you want to set the Total line for
employee to Group By instead of first. This will allow you
to sum the hours for each employee (assuming an employee may
have multiple entries per invoice). You will have to
calculate the total for the invoince another way, perhaps in
a subquery or in the form as you were trying to do before.

If you need further assistance, be sure to post a Copy/Paste
of the query's SQL view so I can see what you are talking
about.
--
Marsh
MVP [MS Access]


JIM wrote:
Hi Marsh, ok I'm trying another approach but can't seem to understand how to
do it. Using Access 2000, I wrote a query based on an InvoicePrint query and
DenverTax table. The problem is I want to summarize the invoice file(from
InvoicePrint query) and also a DenverTax table. For my new query fields
"Denver" and "Baldue" are taken from InvoicePrint query and "EmployeeName"
and "Sum of EmployeeHours" are taken from DenverTax table. I specify summary
in the wizard and result is correct summary of EmployeeHours("Sum of
EmployeeHours") and correct summary of "Baldue". The problem is it also
gives something called "First of EmployeeName" and when query is inspected
just prints first of names of employee for each invoice. I need hours
summarized by employee a total baldue for each invoice summarized.
Relationship is one invoice to many employees.


:
I still disagree about saving that calculated value in the
subform's table, especially since it would be saved to every
record. If it needs to be saved anywhere, it would be in
the main form's table so there would only be one copy of the
value.

The general rule is that you should recalculate the value
anytime you want to display it for users to see.

In any case, if you want to save a calculated value to a
table, you can not use control expressions to do the
calculation. Instead you need to do it in VBA code in the
form's BeforeUpdate event.


JIM wrote:
Here's my situation: this is an invoicing module. If work was done in Denver
a check box is checked. If check box is true then balance due is multiplied
by 10% which total is put into a textbox, MaterialCost, in invisible footer.
The subform is bound to a table called DenverTax. If data entry person
enters a check in check box and after all info for invoice is entered then
employee, hours and 10% of balance due is entered in a subform. The reason
it's designed this way is because there are comparatively few invoices on
which we pay the Denver tax. The data entry person doesn't have to enter
subform for most invoices and it doesn't seem that data entry should figure
the 10% either or have to go thru the invoices 2 times to get the info, thus
the separate file. I'd like to design subform so that only employee(a drop
down combo box) and hours be entered and material will be filled in from
MaterialCost from parent form. When invoices form is completed it appears to
be working. On the first continuous form of subform of every Denver invoice
the 10% of balancedue appears correctly as it also appears in the footer. So
when this was tested it looked like everything was working. But when I
looked at my table, DenverTax, the only record updated with material cost(or
10% of balance due) was the first record of subform from first Denver invoice
done in batch.

:

JIM wrote:

:

JIM wrote:

Hi, I'm using Access 2000. I printed Allen Browne's answer to Richard
Horne's sum function and tried to use it in my situation which is: I want to
multiply BalanceDue(a text box on main form) by 10% and put it in my subform.
I have a textbox in the footer of my main form:
name: MaterialCost
control source: =[BalanceDue]*0.1
format: Currency
and above part is working perfect.
Then in my subform I have a textbox:
name: DenverMaterial
control source: =[Invoices Form].[Form].[MaterialCost]
As soon as it's saved it's converted to [Invoices Form].Form.MaterialCost
and displayed in field is"#Name?" Thanks


The syntax for a full reference would be:
=Forms![Invoices Form].[MaterialCost]

Or a shorter form of that for use in a subform:
=Parent.MaterialCost


The second suggestion with Parent.MaterialCost ,
seemingly, worked but couldn't get the first one to work
at all. On further checking my data though, only the first
record in a one to many relationship is updated with
MaterialCost. In other words, there is only one record in
subform file that is updated with MaterialCost even though
many records are created with other info that is typed in.
Some subforms have no input at all and a subform record
is not created so that might have some bearing.


You have completely lost me here. The expression we have
been discussing has nothing to do with saving any data to a
table.

I do not have enough information to fully understand your
one to many relationship. How a main form material cost can
relate to every record in the subform makes no sense to me.
Actually, this kind of calculated value should only rarely
be saved to a table and I just can't tell if this is one of
those rare cases.

 

[Marshall Barton]

Drop the DISTINCTROW as I don't thing your table has
duplicated rows.

The HAVING clause should be a WHERE clause.

This will total the BalDue over all invoices, not for each
invoice.

If that's what you want, then there is no need to include
the Denver field and that means you do not need the GROUP BY
clause.

SELECT Sum(GetInvoicesToPrint.BalDue) AS [Sum Of BalDue]
FROM GetInvoicesToPrint
WHEREGetInvoicesToPrint.Denver)=True
GetInvoicesToPrint.InvoiceDate) Between #4/1/2006# And
#4/30/2006#))
GROUP BY GetInvoicesToPrint.Denver
HAVING (((GetInvoicesToPrint.Denver)=True));

I think I figured it out. Is this good or is there a better way? Thanks, JM

SELECT DISTINCTROW GetInvoicesToPrint.Denver, Sum(GetInvoicesToPrint.BalDue)
AS [Sum Of BalDue]
FROM GetInvoicesToPrint
WHERE (((GetInvoicesToPrint.InvoiceDate) Between #4/1/2006# And #4/30/2006#))
GROUP BY GetInvoicesToPrint.Denver
HAVING (((GetInvoicesToPrint.Denver)=True));

Actually, I only need one record that summarizes baldue in the whole file:
only records that are Denver=true and dates 04/01/06-04/30/06.

 

[Marshall Barton]

Whoops, clumsy fingers strike while I was editing the SQL
statement. Let's try again.

Drop the DISTINCTROW as I don't thing your table has
duplicated rows.

The HAVING clause should be a WHERE clause.

This will total the BalDue over all invoices, not for each
invoice.

If that's what you want, then there is no need to include
the Denver field and that means you do not need the GROUP BY
clause.

SELECT Sum(BalDue) AS [Sum Of BalDue]
FROM GetInvoicesToPrint
WHERE GetInvoicesToPrint.Denver=True
AND InvoiceDate Between #4/1/2006# And #4/30/2006#

But that is just a grand total, so I don't see how you are
going to use the result. I thought this was supposed to do
something related to each invoice???
--
Marsh
MVP [MS Access]

I think I figured it out. Is this good or is there a better way? Thanks, JM

SELECT DISTINCTROW GetInvoicesToPrint.Denver, Sum(GetInvoicesToPrint.BalDue)
AS [Sum Of BalDue]
FROM GetInvoicesToPrint
WHERE (((GetInvoicesToPrint.InvoiceDate) Between #4/1/2006# And #4/30/2006#))
GROUP BY GetInvoicesToPrint.Denver
HAVING (((GetInvoicesToPrint.Denver)=True));

Actually, I only need one record that summarizes baldue in the whole file:
only records that are Denver=true and dates 04/01/06-04/30/06.