Calculated Field in a Subform

[Theresa]

I have a subform (based on a query) which contains a bound combobox (linked
by an autonumber ID field) to look up the value in another table. I need to
create an unbound field that using the value selected in the combobox. When
I select the ID field in my subform, the calculation uses the ID number
rather than the related value. How can I it to use the value?

 

[Ken Snell [MVP]]

See this article:
http://www.mvps.org/access/forms/frm0058.htm

 

[Al Campagna]

Theresa,
You don't indicate what your combo name is, or what the combo columns
are, so I'll assume that in the cboID combo, the ID value is in Column 0,
and the "related data" (say ClientName) is Column 1.
(Combobox columns are numbered 0, 1, 2, 3,...etc)

In an unbound text control, use this ControlSource...
= cboID.Column(1)
This will display the ClientName for any cboID selection.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Theresa]

The name of my cbo is Annual_Operating_Hours_ID. Cbo consists of 2 fields,
Annual_Operating_Hours_ID and Annual_Operating_Hours. In the subform it
currently displays the correct value however when I use the
Annual_Operating_Hours_ID field in a calculation with other fields on the
form (Qty * Watts) it is using the the number in the ID field rather than the
actual operating hours.

 

[Theresa]

OK....I got it working. Thanks.

Now I want to create a subtotal of that calculated field in the form footer.
I have an unbound textbox.

When I recreate the calculation and refer to column 1, I get an error. The
control source of the unbound subtotal field is:

=Sum([Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000)

The control source of the unbound textbox in the detail section of the
subform is (this calculates correctly):

=[Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate KWH]/1000

 

[Ken Snell [MVP]]

You can only use field names from the form's RecordSource query/table in a
Sum expression. You cannot reference a control on the form, which is what
you're trying to use:
[Annual_Operating_Hours_ID].[Column](1)

I assume that the form is in Continuous Forms view?

Only way I can think of right now to do what you want would be to write a
VBA function that would read the form's Recordset data, look up the related
value from Column(1) property of the combobox, do the calculation of the
summed value, and then use that function in an expression for the footer's
textbox's Control Source.

Tell us more about the form's setup/design.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

OK....I got it working. Thanks.

Now I want to create a subtotal of that calculated field in the form
footer.
I have an unbound textbox.

When I recreate the calculation and refer to column 1, I get an error.
The
control source of the unbound subtotal field is:

=Sum([Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000)

The control source of the unbound textbox in the detail section of the
subform is (this calculates correctly):

=[Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000

See this article:
http://www.mvps.org/access/forms/frm0058.htm

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Theresa]

My subform is based on the query "Electrical_Cost_qry" which contains fields
from 2 tables (Existing_Fixtures_tbl and Electrical_Costs_tbl) and is in
datasheet view.

It contains the fields Project ID (tied to the main form) Fixture Qty,
Watts, Total Watts (unbound txt containing Fixture Qty * Watts),
Annual_Operating Hours (the cbo), Rate KWH, and Annual Cost (unbound txt
containing Fixture_Qty * Watts * Annual_Operating_Hours_ID.Column(1) * Rate
KWH /1000

Although I haven't typed the control sources exactly here, both of these
calculations are working correctly.

I need to create a subtotal of all Annual Costs tied to a project.

You can only use field names from the form's RecordSource query/table in a
Sum expression. You cannot reference a control on the form, which is what
you're trying to use:
[Annual_Operating_Hours_ID].[Column](1)

I assume that the form is in Continuous Forms view?

Only way I can think of right now to do what you want would be to write a
VBA function that would read the form's Recordset data, look up the related
value from Column(1) property of the combobox, do the calculation of the
summed value, and then use that function in an expression for the footer's
textbox's Control Source.

Tell us more about the form's setup/design.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

OK....I got it working. Thanks.

Now I want to create a subtotal of that calculated field in the form
footer.
I have an unbound textbox.

When I recreate the calculation and refer to column 1, I get an error.
The
control source of the unbound subtotal field is:

=Sum([Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000)

The control source of the unbound textbox in the detail section of the
subform is (this calculates correctly):

=[Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000

See this article:
http://www.mvps.org/access/forms/frm0058.htm

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a subform (based on a query) which contains a bound combobox
(linked
by an autonumber ID field) to look up the value in another table. I
need
to
create an unbound field that using the value selected in the combobox.
When
I select the ID field in my subform, the calculation uses the ID number
rather than the related value. How can I it to use the value?

 

[Ken Snell [MVP]]

I've had a different thought, one that should avoid the need for VBA
function.

To which field in your form's query is the combobox
"Annual_Operating_Hours_ID" bound? What is the RowSource query for this
combo box?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

My subform is based on the query "Electrical_Cost_qry" which contains
fields
from 2 tables (Existing_Fixtures_tbl and Electrical_Costs_tbl) and is in
datasheet view.

It contains the fields Project ID (tied to the main form) Fixture Qty,
Watts, Total Watts (unbound txt containing Fixture Qty * Watts),
Annual_Operating Hours (the cbo), Rate KWH, and Annual Cost (unbound txt
containing Fixture_Qty * Watts * Annual_Operating_Hours_ID.Column(1) *
Rate
KWH /1000

Although I haven't typed the control sources exactly here, both of these
calculations are working correctly.

I need to create a subtotal of all Annual Costs tied to a project.

You can only use field names from the form's RecordSource query/table in
a
Sum expression. You cannot reference a control on the form, which is what
you're trying to use:
[Annual_Operating_Hours_ID].[Column](1)

I assume that the form is in Continuous Forms view?

Only way I can think of right now to do what you want would be to write a
VBA function that would read the form's Recordset data, look up the
related
value from Column(1) property of the combobox, do the calculation of the
summed value, and then use that function in an expression for the
footer's
textbox's Control Source.

Tell us more about the form's setup/design.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

OK....I got it working. Thanks.

Now I want to create a subtotal of that calculated field in the form
footer.
I have an unbound textbox.

When I recreate the calculation and refer to column 1, I get an error.
The
control source of the unbound subtotal field is:

=Sum([Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000)

The control source of the unbound textbox in the detail section of the
subform is (this calculates correctly):

=[Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000



:

See this article:
http://www.mvps.org/access/forms/frm0058.htm

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a subform (based on a query) which contains a bound combobox
(linked
by an autonumber ID field) to look up the value in another table. I
need
to
create an unbound field that using the value selected in the
combobox.
When
I select the ID field in my subform, the calculation uses the ID
number
rather than the related value. How can I it to use the value?

 

[Theresa]

Hi Ken:

The row source in the table is: SELECT
[Annual_Operating_Hours_tbl].[Annual_Operating_Hours ID],
[Annual_Operating_Hours_tbl].[Annual_Operating_Hours] FROM
Annual_Operating_Hours_tbl ORDER BY [Annual_Operating_Hours];

Thanks,

Theresa

I've had a different thought, one that should avoid the need for VBA
function.

To which field in your form's query is the combobox
"Annual_Operating_Hours_ID" bound? What is the RowSource query for this
combo box?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

My subform is based on the query "Electrical_Cost_qry" which contains
fields
from 2 tables (Existing_Fixtures_tbl and Electrical_Costs_tbl) and is in
datasheet view.

It contains the fields Project ID (tied to the main form) Fixture Qty,
Watts, Total Watts (unbound txt containing Fixture Qty * Watts),
Annual_Operating Hours (the cbo), Rate KWH, and Annual Cost (unbound txt
containing Fixture_Qty * Watts * Annual_Operating_Hours_ID.Column(1) *
Rate
KWH /1000

Although I haven't typed the control sources exactly here, both of these
calculations are working correctly.

I need to create a subtotal of all Annual Costs tied to a project.

You can only use field names from the form's RecordSource query/table in
a
Sum expression. You cannot reference a control on the form, which is what
you're trying to use:
[Annual_Operating_Hours_ID].[Column](1)

I assume that the form is in Continuous Forms view?

Only way I can think of right now to do what you want would be to write a
VBA function that would read the form's Recordset data, look up the
related
value from Column(1) property of the combobox, do the calculation of the
summed value, and then use that function in an expression for the
footer's
textbox's Control Source.

Tell us more about the form's setup/design.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




OK....I got it working. Thanks.

Now I want to create a subtotal of that calculated field in the form
footer.
I have an unbound textbox.

When I recreate the calculation and refer to column 1, I get an error.
The
control source of the unbound subtotal field is:

=Sum([Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000)

The control source of the unbound textbox in the detail section of the
subform is (this calculates correctly):

=[Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000



:

See this article:
http://www.mvps.org/access/forms/frm0058.htm

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a subform (based on a query) which contains a bound combobox
(linked
by an autonumber ID field) to look up the value in another table. I
need
to
create an unbound field that using the value selected in the
combobox.
When
I select the ID field in my subform, the calculation uses the ID
number
rather than the related value. How can I it to use the value?

 

[Ken Snell [MVP]]

OK. Add a calculated field to the report's RecordSource query:

AnnualOperatingHours: DLookup("Annual_Operating_Hours",
"Annual_Operating_Hours_tbl", "[Annual_Operating_Hours ID] = " &
[PutActualNameOfFieldHereToWhichComboBoxIsBound])



Then, put a hidden (invisible) textbox on your report, and bind it to the
AnnualOperatingHours field in the report's query.

Then, use this field in your report's footer's Sum expression in place of
the [Annual_Operating_Hours_ID].[Column](1) reference.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken:

The row source in the table is: SELECT
[Annual_Operating_Hours_tbl].[Annual_Operating_Hours ID],
[Annual_Operating_Hours_tbl].[Annual_Operating_Hours] FROM
Annual_Operating_Hours_tbl ORDER BY [Annual_Operating_Hours];

Thanks,

Theresa

I've had a different thought, one that should avoid the need for VBA
function.

To which field in your form's query is the combobox
"Annual_Operating_Hours_ID" bound? What is the RowSource query for this
combo box?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

My subform is based on the query "Electrical_Cost_qry" which contains
fields
from 2 tables (Existing_Fixtures_tbl and Electrical_Costs_tbl) and is
in
datasheet view.

It contains the fields Project ID (tied to the main form) Fixture Qty,
Watts, Total Watts (unbound txt containing Fixture Qty * Watts),
Annual_Operating Hours (the cbo), Rate KWH, and Annual Cost (unbound
txt
containing Fixture_Qty * Watts * Annual_Operating_Hours_ID.Column(1) *
Rate
KWH /1000

Although I haven't typed the control sources exactly here, both of
these
calculations are working correctly.

I need to create a subtotal of all Annual Costs tied to a project.

:

You can only use field names from the form's RecordSource query/table
in
a
Sum expression. You cannot reference a control on the form, which is
what
you're trying to use:
[Annual_Operating_Hours_ID].[Column](1)

I assume that the form is in Continuous Forms view?

Only way I can think of right now to do what you want would be to
write a
VBA function that would read the form's Recordset data, look up the
related
value from Column(1) property of the combobox, do the calculation of
the
summed value, and then use that function in an expression for the
footer's
textbox's Control Source.

Tell us more about the form's setup/design.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




OK....I got it working. Thanks.

Now I want to create a subtotal of that calculated field in the form
footer.
I have an unbound textbox.

When I recreate the calculation and refer to column 1, I get an
error.
The
control source of the unbound subtotal field is:

=Sum([Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000)

The control source of the unbound textbox in the detail section of
the
subform is (this calculates correctly):

=[Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000



:

See this article:
http://www.mvps.org/access/forms/frm0058.htm

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a subform (based on a query) which contains a bound
combobox
(linked
by an autonumber ID field) to look up the value in another table.
I
need
to
create an unbound field that using the value selected in the
combobox.
When
I select the ID field in my subform, the calculation uses the ID
number
rather than the related value. How can I it to use the value?

 

[Ken Snell [MVP]]

< sigh > Long workdays make me a bit foggy in the brain.... where I typed
report, replace it with form.....
Sorry.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

OK. Add a calculated field to the report's RecordSource query:

AnnualOperatingHours: DLookup("Annual_Operating_Hours",
"Annual_Operating_Hours_tbl", "[Annual_Operating_Hours ID] = " &
[PutActualNameOfFieldHereToWhichComboBoxIsBound])



Then, put a hidden (invisible) textbox on your report, and bind it to the
AnnualOperatingHours field in the report's query.

Then, use this field in your report's footer's Sum expression in place of
the [Annual_Operating_Hours_ID].[Column](1) reference.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken:

The row source in the table is: SELECT
[Annual_Operating_Hours_tbl].[Annual_Operating_Hours ID],
[Annual_Operating_Hours_tbl].[Annual_Operating_Hours] FROM
Annual_Operating_Hours_tbl ORDER BY [Annual_Operating_Hours];

Thanks,

Theresa

I've had a different thought, one that should avoid the need for VBA
function.

To which field in your form's query is the combobox
"Annual_Operating_Hours_ID" bound? What is the RowSource query for this
combo box?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


My subform is based on the query "Electrical_Cost_qry" which contains
fields
from 2 tables (Existing_Fixtures_tbl and Electrical_Costs_tbl) and is
in
datasheet view.

It contains the fields Project ID (tied to the main form) Fixture Qty,
Watts, Total Watts (unbound txt containing Fixture Qty * Watts),
Annual_Operating Hours (the cbo), Rate KWH, and Annual Cost (unbound
txt
containing Fixture_Qty * Watts * Annual_Operating_Hours_ID.Column(1) *
Rate
KWH /1000

Although I haven't typed the control sources exactly here, both of
these
calculations are working correctly.

I need to create a subtotal of all Annual Costs tied to a project.

:

You can only use field names from the form's RecordSource query/table
in
a
Sum expression. You cannot reference a control on the form, which is
what
you're trying to use:
[Annual_Operating_Hours_ID].[Column](1)

I assume that the form is in Continuous Forms view?

Only way I can think of right now to do what you want would be to
write a
VBA function that would read the form's Recordset data, look up the
related
value from Column(1) property of the combobox, do the calculation of
the
summed value, and then use that function in an expression for the
footer's
textbox's Control Source.

Tell us more about the form's setup/design.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




OK....I got it working. Thanks.

Now I want to create a subtotal of that calculated field in the
form
footer.
I have an unbound textbox.

When I recreate the calculation and refer to column 1, I get an
error.
The
control source of the unbound subtotal field is:

=Sum([Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000)

The control source of the unbound textbox in the detail section of
the
subform is (this calculates correctly):

=[Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000



:

See this article:
http://www.mvps.org/access/forms/frm0058.htm

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a subform (based on a query) which contains a bound
combobox
(linked
by an autonumber ID field) to look up the value in another
table. I
need
to
create an unbound field that using the value selected in the
combobox.
When
I select the ID field in my subform, the calculation uses the ID
number
rather than the related value. How can I it to use the value?