Default values from a subform (Access 2003)

[Guest]

I have several forms that contain employee statistics. On each of these forms
if have several unbound controls that have formulas associated with them to
calculate different statistics based on the form data.

I now need to create a statistics summary form that is bound to a table
where I can store these calculated statistics. I have created the form
"frmMME" and the table "tblMME" and I am attempting to pass the values to the
individual controls by using defaults. For example, from the form
"frmProductivity" I want to capture the value of control [numAvgHours] and
pass it to my new form. If "frmProductivity" is open, I can use the default
value = [forms]![frmProductivity]![numAvgHours] and it works fine, but there
are several forms I need data from and I don't want to open them all. As a
result, I inserted the vital portions of these source forms as subforms on my
"frmMME", but the same default value does not work. I get #Name? or #Error
messages.

As I understand it, I cannot use DLookup because the values are unbound in a
table.
How can I pull these values into my form in order to save the values in a
table?

 

[Jeff Boyce]

Why? As in "why do you think you need to store the calculated values in a
table?"

It is rarely necessary (or desirable) to store calculated values in a
well-normalized relational database.

If you'll describe a bit more about what business need you plan to solve by
having these calculated values in a table, the volunteers here in the 'group
may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Because I work for a Federal Govt. program and I must be able to show all
data produced at a given point in time. If I don't store the value and
someone makes a change to one of the dependent values, then the calculated
figures will be different and my reports won't match if I need to reprint for
an audit.

Therefore, I need to capture the value of the calculated field within the
subform and apply it to the control in the master form so that it can be
saved.
--
Thanks!

Why? As in "why do you think you need to store the calculated values in a
table?"

It is rarely necessary (or desirable) to store calculated values in a
well-normalized relational database.

If you'll describe a bit more about what business need you plan to solve by
having these calculated values in a table, the volunteers here in the 'group
may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have several forms that contain employee statistics. On each of these
forms
if have several unbound controls that have formulas associated with them
to
calculate different statistics based on the form data.

I now need to create a statistics summary form that is bound to a table
where I can store these calculated statistics. I have created the form
"frmMME" and the table "tblMME" and I am attempting to pass the values to
the
individual controls by using defaults. For example, from the form
"frmProductivity" I want to capture the value of control [numAvgHours] and
pass it to my new form. If "frmProductivity" is open, I can use the
default
value = [forms]![frmProductivity]![numAvgHours] and it works fine, but
there
are several forms I need data from and I don't want to open them all. As a
result, I inserted the vital portions of these source forms as subforms on
my
"frmMME", but the same default value does not work. I get #Name? or #Error
messages.

As I understand it, I cannot use DLookup because the values are unbound in
a
table.
How can I pull these values into my form in order to save the values in a
table?

 

[Jeff Boyce]

Don

I seem to be missing something.

If the users change one/more of the underlying values, won't THOSE no longer
match the underlying/column values you have?

The issue of synchronization (change this, re-calc that) is one reason not
to store the calculated values.

If you are recording something to the effect of:

On this DATE, the following VALUES were provided

then you could still get away without storing the calculated total, because
you would always be able to go back to that date's data and calculate what
it totalled then.

If the users modify the underlying values, that would be a NEW record (new
date, new values). Is there a way to tie the data from DATE1 to the data
from DATE2? You could use this to SHOW when the underlying data was
changed. And if you capture who provides the data, you could document who
changed it...

Hopefully this hasn't wandered too far afield ... after all, you know your
situation much better than we can imagine it.

Regards

Jeff Boyce
Microsoft Ofiice/Access MVP

Because I work for a Federal Govt. program and I must be able to show all
data produced at a given point in time. If I don't store the value and
someone makes a change to one of the dependent values, then the calculated
figures will be different and my reports won't match if I need to reprint
for
an audit.

Therefore, I need to capture the value of the calculated field within the
subform and apply it to the control in the master form so that it can be
saved.
--
Thanks!

Why? As in "why do you think you need to store the calculated values in
a
table?"

It is rarely necessary (or desirable) to store calculated values in a
well-normalized relational database.

If you'll describe a bit more about what business need you plan to solve
by
having these calculated values in a table, the volunteers here in the
'group
may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have several forms that contain employee statistics. On each of these
forms
if have several unbound controls that have formulas associated with
them
to
calculate different statistics based on the form data.

I now need to create a statistics summary form that is bound to a table
where I can store these calculated statistics. I have created the form
"frmMME" and the table "tblMME" and I am attempting to pass the values
to
the
individual controls by using defaults. For example, from the form
"frmProductivity" I want to capture the value of control [numAvgHours]
and
pass it to my new form. If "frmProductivity" is open, I can use the
default
value = [forms]![frmProductivity]![numAvgHours] and it works fine, but
there
are several forms I need data from and I don't want to open them all.
As a
result, I inserted the vital portions of these source forms as subforms
on
my
"frmMME", but the same default value does not work. I get #Name? or
#Error
messages.

As I understand it, I cannot use DLookup because the values are unbound
in
a
table.
How can I pull these values into my form in order to save the values in
a
table?

 

[Guest]

Jeff, with all due respect, it doesn't really matter why I need it, I just do
because the government mandates it this way. I agree that it is a silly
request, but that is your government dollars at work

--
Thanks!

Don

I seem to be missing something.

If the users change one/more of the underlying values, won't THOSE no longer
match the underlying/column values you have?

The issue of synchronization (change this, re-calc that) is one reason not
to store the calculated values.

If you are recording something to the effect of:

On this DATE, the following VALUES were provided

then you could still get away without storing the calculated total, because
you would always be able to go back to that date's data and calculate what
it totalled then.

If the users modify the underlying values, that would be a NEW record (new
date, new values). Is there a way to tie the data from DATE1 to the data
from DATE2? You could use this to SHOW when the underlying data was
changed. And if you capture who provides the data, you could document who
changed it...

Hopefully this hasn't wandered too far afield ... after all, you know your
situation much better than we can imagine it.

Regards

Jeff Boyce
Microsoft Ofiice/Access MVP

Because I work for a Federal Govt. program and I must be able to show all
data produced at a given point in time. If I don't store the value and
someone makes a change to one of the dependent values, then the calculated
figures will be different and my reports won't match if I need to reprint
for
an audit.

Therefore, I need to capture the value of the calculated field within the
subform and apply it to the control in the master form so that it can be
saved.
--
Thanks!

Why? As in "why do you think you need to store the calculated values in
a
table?"

It is rarely necessary (or desirable) to store calculated values in a
well-normalized relational database.

If you'll describe a bit more about what business need you plan to solve
by
having these calculated values in a table, the volunteers here in the
'group
may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have several forms that contain employee statistics. On each of these
forms
if have several unbound controls that have formulas associated with
them
to
calculate different statistics based on the form data.

I now need to create a statistics summary form that is bound to a table
where I can store these calculated statistics. I have created the form
"frmMME" and the table "tblMME" and I am attempting to pass the values
to
the
individual controls by using defaults. For example, from the form
"frmProductivity" I want to capture the value of control [numAvgHours]
and
pass it to my new form. If "frmProductivity" is open, I can use the
default
value = [forms]![frmProductivity]![numAvgHours] and it works fine, but
there
are several forms I need data from and I don't want to open them all.
As a
result, I inserted the vital portions of these source forms as subforms
on
my
"frmMME", but the same default value does not work. I get #Name? or
#Error
messages.

As I understand it, I cannot use DLookup because the values are unbound
in
a
table.
How can I pull these values into my form in order to save the values in
a
table?

 

[Jeff Boyce]

Don

I truly wasn't arguing against a need, but I was poking at it to see if
we've been focusing on the "how" before the "what".

If you need to have a calculated value stored in a table, there's not any
easy way to do that at the table level in Access.

Using a form, though, lets you enter data and can calculate a total (which
could then go into a control that was bound to an underlying "total" field
in the table).

If you don't have the luxury of pushing all the data entry through a form,
you could still use an update query to calculate the total (from the table's
columns) and update (the total column).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff, with all due respect, it doesn't really matter why I need it, I just
do
because the government mandates it this way. I agree that it is a silly
request, but that is your government dollars at work

--
Thanks!

Don

I seem to be missing something.

If the users change one/more of the underlying values, won't THOSE no
longer
match the underlying/column values you have?

The issue of synchronization (change this, re-calc that) is one reason
not
to store the calculated values.

If you are recording something to the effect of:

On this DATE, the following VALUES were provided

then you could still get away without storing the calculated total,
because
you would always be able to go back to that date's data and calculate
what
it totalled then.

If the users modify the underlying values, that would be a NEW record
(new
date, new values). Is there a way to tie the data from DATE1 to the data
from DATE2? You could use this to SHOW when the underlying data was
changed. And if you capture who provides the data, you could document
who
changed it...

Hopefully this hasn't wandered too far afield ... after all, you know
your
situation much better than we can imagine it.

Regards

Jeff Boyce
Microsoft Ofiice/Access MVP

Because I work for a Federal Govt. program and I must be able to show
all
data produced at a given point in time. If I don't store the value and
someone makes a change to one of the dependent values, then the
calculated
figures will be different and my reports won't match if I need to
reprint
for
an audit.

Therefore, I need to capture the value of the calculated field within
the
subform and apply it to the control in the master form so that it can
be
saved.
--
Thanks!


:

Why? As in "why do you think you need to store the calculated values
in
a
table?"

It is rarely necessary (or desirable) to store calculated values in a
well-normalized relational database.

If you'll describe a bit more about what business need you plan to
solve
by
having these calculated values in a table, the volunteers here in the
'group
may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have several forms that contain employee statistics. On each of
these
forms
if have several unbound controls that have formulas associated with
them
to
calculate different statistics based on the form data.

I now need to create a statistics summary form that is bound to a
table
where I can store these calculated statistics. I have created the
form
"frmMME" and the table "tblMME" and I am attempting to pass the
values
to
the
individual controls by using defaults. For example, from the form
"frmProductivity" I want to capture the value of control
[numAvgHours]
and
pass it to my new form. If "frmProductivity" is open, I can use the
default
value = [forms]![frmProductivity]![numAvgHours] and it works fine,
but
there
are several forms I need data from and I don't want to open them
all.
As a
result, I inserted the vital portions of these source forms as
subforms
on
my
"frmMME", but the same default value does not work. I get #Name? or
#Error
messages.

As I understand it, I cannot use DLookup because the values are
unbound
in
a
table.
How can I pull these values into my form in order to save the values
in
a
table?

 

[Pieter Wijnen]

Back to the original question

When referencing subforms the syntax is

Forms!MainForm!SubFormControlName.Form

Note: SubFormControlName May not be the same as the Sub form's Name

Pieter

Don

I truly wasn't arguing against a need, but I was poking at it to see if
we've been focusing on the "how" before the "what".

If you need to have a calculated value stored in a table, there's not any
easy way to do that at the table level in Access.

Using a form, though, lets you enter data and can calculate a total (which
could then go into a control that was bound to an underlying "total" field
in the table).

If you don't have the luxury of pushing all the data entry through a form,
you could still use an update query to calculate the total (from the
table's columns) and update (the total column).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff, with all due respect, it doesn't really matter why I need it, I
just do
because the government mandates it this way. I agree that it is a silly
request, but that is your government dollars at work

--
Thanks!

Don

I seem to be missing something.

If the users change one/more of the underlying values, won't THOSE no
longer
match the underlying/column values you have?

The issue of synchronization (change this, re-calc that) is one reason
not
to store the calculated values.

If you are recording something to the effect of:

On this DATE, the following VALUES were provided

then you could still get away without storing the calculated total,
because
you would always be able to go back to that date's data and calculate
what
it totalled then.

If the users modify the underlying values, that would be a NEW record
(new
date, new values). Is there a way to tie the data from DATE1 to the
data
from DATE2? You could use this to SHOW when the underlying data was
changed. And if you capture who provides the data, you could document
who
changed it...

Hopefully this hasn't wandered too far afield ... after all, you know
your
situation much better than we can imagine it.

Regards

Jeff Boyce
Microsoft Ofiice/Access MVP

Because I work for a Federal Govt. program and I must be able to show
all
data produced at a given point in time. If I don't store the value and
someone makes a change to one of the dependent values, then the
calculated
figures will be different and my reports won't match if I need to
reprint
for
an audit.

Therefore, I need to capture the value of the calculated field within
the
subform and apply it to the control in the master form so that it can
be
saved.
--
Thanks!


:

Why? As in "why do you think you need to store the calculated values
in
a
table?"

It is rarely necessary (or desirable) to store calculated values in a
well-normalized relational database.

If you'll describe a bit more about what business need you plan to
solve
by
having these calculated values in a table, the volunteers here in the
'group
may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have several forms that contain employee statistics. On each of
these
forms
if have several unbound controls that have formulas associated with
them
to
calculate different statistics based on the form data.

I now need to create a statistics summary form that is bound to a
table
where I can store these calculated statistics. I have created the
form
"frmMME" and the table "tblMME" and I am attempting to pass the
values
to
the
individual controls by using defaults. For example, from the form
"frmProductivity" I want to capture the value of control
[numAvgHours]
and
pass it to my new form. If "frmProductivity" is open, I can use the
default
value = [forms]![frmProductivity]![numAvgHours] and it works fine,
but
there
are several forms I need data from and I don't want to open them
all.
As a
result, I inserted the vital portions of these source forms as
subforms
on
my
"frmMME", but the same default value does not work. I get #Name? or
#Error
messages.

As I understand it, I cannot use DLookup because the values are
unbound
in
a
table.
How can I pull these values into my form in order to save the
values in
a
table?

 

[Guest]

Pieter,
Thanks for the systax. I am not sure I understand how to define it though.
Here is an example of information:
Main form name = frmMME
Main form control where I want to store the data = [numclaims]
Subform name = fsubProdStats
Subform control containing calculated data = [sumClaims]
Using your instruction of Forms!MainForm!SubFormControlName.Form, how should
my syntax look?


--
Thanks!

Back to the original question

When referencing subforms the syntax is

Forms!MainForm!SubFormControlName.Form

Note: SubFormControlName May not be the same as the Sub form's Name

Pieter

Don

I truly wasn't arguing against a need, but I was poking at it to see if
we've been focusing on the "how" before the "what".

If you need to have a calculated value stored in a table, there's not any
easy way to do that at the table level in Access.

Using a form, though, lets you enter data and can calculate a total (which
could then go into a control that was bound to an underlying "total" field
in the table).

If you don't have the luxury of pushing all the data entry through a form,
you could still use an update query to calculate the total (from the
table's columns) and update (the total column).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff, with all due respect, it doesn't really matter why I need it, I
just do
because the government mandates it this way. I agree that it is a silly
request, but that is your government dollars at work

--
Thanks!


:

Don

I seem to be missing something.

If the users change one/more of the underlying values, won't THOSE no
longer
match the underlying/column values you have?

The issue of synchronization (change this, re-calc that) is one reason
not
to store the calculated values.

If you are recording something to the effect of:

On this DATE, the following VALUES were provided

then you could still get away without storing the calculated total,
because
you would always be able to go back to that date's data and calculate
what
it totalled then.

If the users modify the underlying values, that would be a NEW record
(new
date, new values). Is there a way to tie the data from DATE1 to the
data
from DATE2? You could use this to SHOW when the underlying data was
changed. And if you capture who provides the data, you could document
who
changed it...

Hopefully this hasn't wandered too far afield ... after all, you know
your
situation much better than we can imagine it.

Regards

Jeff Boyce
Microsoft Ofiice/Access MVP

Because I work for a Federal Govt. program and I must be able to show
all
data produced at a given point in time. If I don't store the value and
someone makes a change to one of the dependent values, then the
calculated
figures will be different and my reports won't match if I need to
reprint
for
an audit.

Therefore, I need to capture the value of the calculated field within
the
subform and apply it to the control in the master form so that it can
be
saved.
--
Thanks!


:

Why? As in "why do you think you need to store the calculated values
in
a
table?"

It is rarely necessary (or desirable) to store calculated values in a
well-normalized relational database.

If you'll describe a bit more about what business need you plan to
solve
by
having these calculated values in a table, the volunteers here in the
'group
may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have several forms that contain employee statistics. On each of
these
forms
if have several unbound controls that have formulas associated with
them
to
calculate different statistics based on the form data.

I now need to create a statistics summary form that is bound to a
table
where I can store these calculated statistics. I have created the
form
"frmMME" and the table "tblMME" and I am attempting to pass the
values
to
the
individual controls by using defaults. For example, from the form
"frmProductivity" I want to capture the value of control
[numAvgHours]
and
pass it to my new form. If "frmProductivity" is open, I can use the
default
value = [forms]![frmProductivity]![numAvgHours] and it works fine,
but
there
are several forms I need data from and I don't want to open them
all.
As a
result, I inserted the vital portions of these source forms as
subforms
on
my
"frmMME", but the same default value does not work. I get #Name? or
#Error
messages.

As I understand it, I cannot use DLookup because the values are
unbound
in
a
table.
How can I pull these values into my form in order to save the
values in
a
table?

 

[Pieter Wijnen]

NumClaims ControlSource: =Forms!frmMME!fsubProdStats!Form!sumClaims

HTH

Pieter

PS Make sure fsubProdStats is named as fsubProdStats in the main form - the
controlname might not be the same as the form's name

Pieter,
Thanks for the systax. I am not sure I understand how to define it though.
Here is an example of information:
Main form name = frmMME
Main form control where I want to store the data = [numclaims]
Subform name = fsubProdStats
Subform control containing calculated data = [sumClaims]
Using your instruction of Forms!MainForm!SubFormControlName.Form, how
should
my syntax look?


--
Thanks!

Back to the original question

When referencing subforms the syntax is

Forms!MainForm!SubFormControlName.Form

Note: SubFormControlName May not be the same as the Sub form's Name

Pieter

Don

I truly wasn't arguing against a need, but I was poking at it to see if
we've been focusing on the "how" before the "what".

If you need to have a calculated value stored in a table, there's not
any
easy way to do that at the table level in Access.

Using a form, though, lets you enter data and can calculate a total
(which
could then go into a control that was bound to an underlying "total"
field
in the table).

If you don't have the luxury of pushing all the data entry through a
form,
you could still use an update query to calculate the total (from the
table's columns) and update (the total column).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff, with all due respect, it doesn't really matter why I need it, I
just do
because the government mandates it this way. I agree that it is a
silly
request, but that is your government dollars at work

--
Thanks!


:

Don

I seem to be missing something.

If the users change one/more of the underlying values, won't THOSE no
longer
match the underlying/column values you have?

The issue of synchronization (change this, re-calc that) is one
reason
not
to store the calculated values.

If you are recording something to the effect of:

On this DATE, the following VALUES were provided

then you could still get away without storing the calculated total,
because
you would always be able to go back to that date's data and calculate
what
it totalled then.

If the users modify the underlying values, that would be a NEW record
(new
date, new values). Is there a way to tie the data from DATE1 to the
data
from DATE2? You could use this to SHOW when the underlying data was
changed. And if you capture who provides the data, you could
document
who
changed it...

Hopefully this hasn't wandered too far afield ... after all, you know
your
situation much better than we can imagine it.

Regards

Jeff Boyce
Microsoft Ofiice/Access MVP

Because I work for a Federal Govt. program and I must be able to
show
all
data produced at a given point in time. If I don't store the value
and
someone makes a change to one of the dependent values, then the
calculated
figures will be different and my reports won't match if I need to
reprint
for
an audit.

Therefore, I need to capture the value of the calculated field
within
the
subform and apply it to the control in the master form so that it
can
be
saved.
--
Thanks!


:

Why? As in "why do you think you need to store the calculated
values
in
a
table?"

It is rarely necessary (or desirable) to store calculated values
in a
well-normalized relational database.

If you'll describe a bit more about what business need you plan to
solve
by
having these calculated values in a table, the volunteers here in
the
'group
may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have several forms that contain employee statistics. On each of
these
forms
if have several unbound controls that have formulas associated
with
them
to
calculate different statistics based on the form data.

I now need to create a statistics summary form that is bound to
a
table
where I can store these calculated statistics. I have created
the
form
"frmMME" and the table "tblMME" and I am attempting to pass the
values
to
the
individual controls by using defaults. For example, from the
form
"frmProductivity" I want to capture the value of control
[numAvgHours]
and
pass it to my new form. If "frmProductivity" is open, I can use
the
default
value = [forms]![frmProductivity]![numAvgHours] and it works
fine,
but
there
are several forms I need data from and I don't want to open them
all.
As a
result, I inserted the vital portions of these source forms as
subforms
on
my
"frmMME", but the same default value does not work. I get #Name?
or
#Error
messages.

As I understand it, I cannot use DLookup because the values are
unbound
in
a
table.
How can I pull these values into my form in order to save the
values in
a
table?