Calculated Value from form back to table

[debinnyc]

I have a form designed to provide a value to any of four values selected in
the combo box next to it. For example, select excellent, and the box returns
10, proficient 6, etc.

My problem is getting that information back to the table to store (and later
analyze)

The result box(es) has it's own field in the table, but despite the fact the
form reads out properly, the table still reads zero. Any help would be
greatly appreciated. Thanks

 

[Arvin Meyer [MVP]]

Normally, and in this case, calculated data is never stored in a database.
Instead, store the elements of that calculation (which you appear to be
doing) since the actual calculation can be redone at any time.

 

[debinnyc]

Ok, so if I am storing the elements in the form, how can I trend and report
on that data? Since this db & form are being used to track employee
performance, and there are eight indicators, I need to be able to harvest the
quantitative portion and report/analyze. Thoughts?

 

[Arvin Meyer [MVP]]

Nothing is stored in a form or report. Everything is stored in a table. Is
there a history of employee performance, that is required? If so, then you
must either store multiple instances of the indicators (preferable since you
can see progress or lack thereof in all 8 indicators), or the calculations.

A query with the date as criteria will pick the instance you need to report
on (and can even do the calculations), and the form or report will display
the results.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[debinnyc]

Sorry, but either I am not being clear or I have no idea where you are going
with this. Let me explain again:

I have these (and others) fields in my table:

SecurityVerify
SecurityVerifyResult
TechKnowledge
TechknowledgeResult

etc.

On the form, there are combo boxes for security, tech knowledge, etc that
have the choices of four different levels, excellent, proficient, developing
and unsatisfactory.

In the securityresult, techknowledge, etc. they are text boxes where I have
used the following formula:

=IIf([SecurityVerify]="excellent","10",IIf([SecurityVerify]="good","8",IIf([SecurityVerify]="proficient","6",IIf([SecurityVerify]="developing","4",IIf([SecurityVerify]="unsatisfactory","2",Null)))))

What I want is to be able to collect and report on the results of the above,
basedon having to do multiple independent assesments on the same employee
every month. This way over time we can see how they have performed
historically and where there performance is going.

 

[Arvin Meyer [MVP]]

OK, you really don't need to store the calculation, but you do need a table
that has at least 5 fields.

PerformanceID - Autonumber - Primary Key
EmployeeID - Long Integer - Foreign Key from tblEmployees
AssessmentDate - Date/Time
SecurityVerifyResult - Integer
TechknowledgeResult - Integer

Use a query of the above table to be the recordsource of a subform with an
added calculated column:

Performance: [SecurityVerifyResult] + [TechknowledgeResult]

On the subform use 2 comboboxes bound to the 2 fields with the following
properties:
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0"; 1"
RowSourceType: Value List
Row Source:
10;"Excellent";8;"Good";6;"Proficient";4;"Developing";2;"Unsatisfactory"

In the AfterUpdate event of each of the combos use a piece of code that
simply reads like:

Sub cboSecurityVerifyResult_AfterUdate()
Me.Requery
End Sub

That's all you'll need.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Sorry, but either I am not being clear or I have no idea where you are
going
with this. Let me explain again:

I have these (and others) fields in my table:

SecurityVerify
SecurityVerifyResult
TechKnowledge
TechknowledgeResult

etc.

On the form, there are combo boxes for security, tech knowledge, etc that
have the choices of four different levels, excellent, proficient,
developing
and unsatisfactory.

In the securityresult, techknowledge, etc. they are text boxes where I
have
used the following formula:

=IIf([SecurityVerify]="excellent","10",IIf([SecurityVerify]="good","8",IIf([SecurityVerify]="proficient","6",IIf([SecurityVerify]="developing","4",IIf([SecurityVerify]="unsatisfactory","2",Null)))))

What I want is to be able to collect and report on the results of the
above,
basedon having to do multiple independent assesments on the same employee
every month. This way over time we can see how they have performed
historically and where there performance is going.

Nothing is stored in a form or report. Everything is stored in a table.
Is
there a history of employee performance, that is required? If so, then
you
must either store multiple instances of the indicators (preferable since
you
can see progress or lack thereof in all 8 indicators), or the
calculations.

A query with the date as criteria will pick the instance you need to
report
on (and can even do the calculations), and the form or report will
display
the results.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com