Total from calculated control using combo box column

[hughess7]

Hi all

I have a combo box on a subform that a user chooses an Issue Code from. The
errorlevel is displayed by referencing the 5th column of the combo box, this
works fine. But, I want to calculate a running total of this and I get an
error. I know you can't reference calculated controls from unbound text
boxes, but I'm not sure how to do this since its from a combo box column. Do
I need to do it another way somehow?

Thanks in advance for any help.
Sue

 

[Wayne-I-M]

Not really sure what it is you are looking so .....

To get a running total of a column of a combo (one method is) create a
hidden text box on your form =[ComboName3].Column(5)
Do your running total on this text box.

 

[hughess7]

Hi Wayne

I don't think there is a Running Sum option on a form like there is a
report? The problem is if you try to reference the text box
=[ComboName3].Column(4) it doesn't recognise it because it is a calculated
control and prompts you for a parameter. You can't ref this as
=sum([ComboName3].Column(5)) as it doesn't work.

I've decided to store the data now anyway in the table which allows me to
then do a running total. Initially I didn't want to do this due to not
storing calculated values, normalisation etc but then in this case it is
required because the error level codes for future calcs could change and the
old ones would need to remain at the old levels.

Cheers
Sue

Not really sure what it is you are looking so .....

To get a running total of a column of a combo (one method is) create a
hidden text box on your form =[ComboName3].Column(5)
Do your running total on this text box.


--
Wayne
Manchester, England.

Hi all

I have a combo box on a subform that a user chooses an Issue Code from. The
errorlevel is displayed by referencing the 5th column of the combo box, this
works fine. But, I want to calculate a running total of this and I get an
error. I know you can't reference calculated controls from unbound text
boxes, but I'm not sure how to do this since its from a combo box column. Do
I need to do it another way somehow?

Thanks in advance for any help.
Sue

 

[Ken Sheridan]

Sue;

What you've done is quite correct. The 'rule' that one should not store
'derived' values is not an absolute one. It depends on what the derived
value is functionally dependent on. Normalization (to third normal form at
least) requires that a non-key column is functionally dependent solely on the
whole of the key of the table ('The key, the whole key and nothing but the
key, so help me Codd'). In your case, the column to which you are assigning
the value from the combo box is functionally dependent on the key of the
underlying table as its value needs to remain static when the value from
which it is derived changes, so no redundancy is being introduced and the
table is, all other requirements being met, normalized to third normal form
at least.

If you take a look at the order details subform in Northwind you'll find an
analogous situation in the case of the UnitPrice column, whose value is
looked up from the products table and assigned to the column in the order
details table.

Ken Sheridan
Stafford, England

Hi Wayne

I don't think there is a Running Sum option on a form like there is a
report? The problem is if you try to reference the text box
=[ComboName3].Column(4) it doesn't recognise it because it is a calculated
control and prompts you for a parameter. You can't ref this as
=sum([ComboName3].Column(5)) as it doesn't work.

I've decided to store the data now anyway in the table which allows me to
then do a running total. Initially I didn't want to do this due to not
storing calculated values, normalisation etc but then in this case it is
required because the error level codes for future calcs could change and the
old ones would need to remain at the old levels.

Cheers
Sue

Not really sure what it is you are looking so .....

To get a running total of a column of a combo (one method is) create a
hidden text box on your form =[ComboName3].Column(5)
Do your running total on this text box.


--
Wayne
Manchester, England.

Hi all

I have a combo box on a subform that a user chooses an Issue Code from. The
errorlevel is displayed by referencing the 5th column of the combo box, this
works fine. But, I want to calculate a running total of this and I get an
error. I know you can't reference calculated controls from unbound text
boxes, but I'm not sure how to do this since its from a combo box column. Do
I need to do it another way somehow?

Thanks in advance for any help.
Sue

 

[hughess7]

Hi Ken

Thanks for the very detailed reply, always good to have confirmation that
I've done it right for a change ;-).

Cheers
Sue

Sue;

What you've done is quite correct. The 'rule' that one should not store
'derived' values is not an absolute one. It depends on what the derived
value is functionally dependent on. Normalization (to third normal form at
least) requires that a non-key column is functionally dependent solely on the
whole of the key of the table ('The key, the whole key and nothing but the
key, so help me Codd'). In your case, the column to which you are assigning
the value from the combo box is functionally dependent on the key of the
underlying table as its value needs to remain static when the value from
which it is derived changes, so no redundancy is being introduced and the
table is, all other requirements being met, normalized to third normal form
at least.

If you take a look at the order details subform in Northwind you'll find an
analogous situation in the case of the UnitPrice column, whose value is
looked up from the products table and assigned to the column in the order
details table.

Ken Sheridan
Stafford, England

Hi Wayne

I don't think there is a Running Sum option on a form like there is a
report? The problem is if you try to reference the text box
=[ComboName3].Column(4) it doesn't recognise it because it is a calculated
control and prompts you for a parameter. You can't ref this as
=sum([ComboName3].Column(5)) as it doesn't work.

I've decided to store the data now anyway in the table which allows me to
then do a running total. Initially I didn't want to do this due to not
storing calculated values, normalisation etc but then in this case it is
required because the error level codes for future calcs could change and the
old ones would need to remain at the old levels.

Cheers
Sue

Not really sure what it is you are looking so .....

To get a running total of a column of a combo (one method is) create a
hidden text box on your form =[ComboName3].Column(5)
Do your running total on this text box.


--
Wayne
Manchester, England.



:

Hi all

I have a combo box on a subform that a user chooses an Issue Code from. The
errorlevel is displayed by referencing the 5th column of the combo box, this
works fine. But, I want to calculate a running total of this and I get an
error. I know you can't reference calculated controls from unbound text
boxes, but I'm not sure how to do this since its from a combo box column. Do
I need to do it another way somehow?

Thanks in advance for any help.
Sue