Calculations from Form to Table, How?

[Guest]

Hello Everyone. I'm afraid I have a couple of questions. No doubt they have
been addressed, so if someone can point me to the answers, I'll try not to
bug you anymore.

I've created a 'ScrapDataTable' table. a.) Users enter data into a
form which consists of several combo boxes, to ensure we get the correct data.
b.) Several of the fields in this form perform calculations as follows:
[MaterialLength]*[NumberOfPieces] - giving me the total length in inches.
[TotalLengthInInches]/12 - giving me the foot length.
[TotalFootLength]*[MaterialCost] - this will be my first question!

Currently, the 'MaterialCost' is entered manually because I have been
unsuccessful in my expression attempts to have it automatically fill in.
'MaterialCost' is actually
a third column in the 'MaterialCostTable' where users already select the
'Material Type' (columns are 0";22";0"). Ideally, I want the cost to
automatically fill in. Is
there no way to automatically drag the 'MaterialCost' field from the third
column
when users select the 'MaterialType'? Both are individual fields on the
form and each 'MaterialType' has it's own unique 'MaterialCost' - and always
will.

For my second question, why are my calculations in the form not transferring
to the main table 'ScrapDataTable'? All other info is showing up just fine,
while the fields which are calculated in the form are showing up as zeros on
the primary table.

Thanks in advance! You guys are great.
Karen

 

[Joseph Meehan]

Hello Everyone. I'm afraid I have a couple of questions. No doubt
they have been addressed, so if someone can point me to the answers,
I'll try not to bug you anymore.

I've created a 'ScrapDataTable' table. a.) Users enter data into a
form which consists of several combo boxes, to ensure we get the
correct data. b.) Several of the fields in this form perform
calculations as follows: [MaterialLength]*[NumberOfPieces] - giving
me the total length in inches. [TotalLengthInInches]/12
- giving me the foot length. [TotalFootLength]*[MaterialCost] -
this will be my first question!

Currently, the 'MaterialCost' is entered manually because I have been
unsuccessful in my expression attempts to have it automatically fill
in. 'MaterialCost' is actually
a third column in the 'MaterialCostTable' where users already select
the 'Material Type' (columns are 0";22";0"). Ideally, I want the
cost to automatically fill in. Is
there no way to automatically drag the 'MaterialCost' field from the
third column
when users select the 'MaterialType'? Both are individual fields on
the
form and each 'MaterialType' has it's own unique 'MaterialCost' - and
always will.

Sure can. I suggest that most likely you have not made the material
cost part of the record set (query?) for the form. There are other
possibilities but since I don't know how you have things set up, I could
guess all day and not get it right.

For my second question, why are my calculations in the form not
transferring to the main table 'ScrapDataTable'? All other info is
showing up just fine, while the fields which are calculated in the
form are showing up as zeros on the primary table.

Thanks in advance! You guys are great.
Karen

First thing first. You are using Access not Excel. You don't store
results of calculations. In fact if you really look, you will see the Excel
does not store its results either.

You recompute the calculation each time. There are two reasons for
this. First it generally is more efficient for Access to do the calculation
than to store and retrieve the data. Second, if you store the results and
then change one of the numbers, the results will not be updated and will
result in an error.

 

[Guest]

Thanks for the quick reply.

I'm not quite sure what you meant when you referred to the
Material Cost as part of the "record set - query for the form"
I wouldn't want you to guess all day, I'm sure you have better things
to do! I've set up many db's, I'm just not familiar with the terminology
perhaps.

I started by creating a primary table, followed that by creating 4 more
tables connected to combo boxes, and finally the form where the user
enters and selects various data. I initially assumed the Material Cost
would be a breeze to call up, based on the Material Type chosen by the
user early on in the form.

How would I make sure the Material Cost is part of the record set as you
suggest?

Thank You!
Karen

Hello Everyone. I'm afraid I have a couple of questions. No doubt
they have been addressed, so if someone can point me to the answers,
I'll try not to bug you anymore.

I've created a 'ScrapDataTable' table. a.) Users enter data into a
form which consists of several combo boxes, to ensure we get the
correct data. b.) Several of the fields in this form perform
calculations as follows: [MaterialLength]*[NumberOfPieces] - giving
me the total length in inches. [TotalLengthInInches]/12
- giving me the foot length. [TotalFootLength]*[MaterialCost] -
this will be my first question!

Currently, the 'MaterialCost' is entered manually because I have been
unsuccessful in my expression attempts to have it automatically fill
in. 'MaterialCost' is actually
a third column in the 'MaterialCostTable' where users already select
the 'Material Type' (columns are 0";22";0"). Ideally, I want the
cost to automatically fill in. Is
there no way to automatically drag the 'MaterialCost' field from the
third column
when users select the 'MaterialType'? Both are individual fields on
the
form and each 'MaterialType' has it's own unique 'MaterialCost' - and
always will.

Sure can. I suggest that most likely you have not made the material
cost part of the record set (query?) for the form. There are other
possibilities but since I don't know how you have things set up, I could
guess all day and not get it right.

For my second question, why are my calculations in the form not
transferring to the main table 'ScrapDataTable'? All other info is
showing up just fine, while the fields which are calculated in the
form are showing up as zeros on the primary table.

Thanks in advance! You guys are great.
Karen

First thing first. You are using Access not Excel. You don't store
results of calculations. In fact if you really look, you will see the Excel
does not store its results either.

You recompute the calculation each time. There are two reasons for
this. First it generally is more efficient for Access to do the calculation
than to store and retrieve the data. Second, if you store the results and
then change one of the numbers, the results will not be updated and will
result in an error.