Form Calculations not returning correct results

[dwcrockford]

On a form, I have values based on a table lookup.
When I select "PERSON", the fields populate with correct values based on the associated table.

On that form, I also have values summing up criteria from a subform (associated to a separate table).
I have totals calculating properly regarding the subform. No issues as of yet.

Now, I want to do come "combo" calculations, using the values from the totals from the subform, and the values pertaining to the person's info.

I want to calculate value of hours.... So the code that's not working is:

=[TOTALHOURS] *[PAYRATE]

This refers to the names of the field boxes on the forms.

The TOTAL HOURS is calculated from the subform (and works correctly), the PAYRATE is pulled from the Employee Table, and the correct value is displayed for the correct individual.

However, when this "VALUE" field is calculated, instead of using the value of the PAYRATE, it is calculating based on the persons ID# (PK, autonumbered). So 10.00 hours x $35.00 returns a result of $20.00, which is because the persons ID# was 2.

I have played with bound column, etc., but am not getting the correct return.

Suggestions on what I'm missing in the coding?

D

 

[xenou]

I assume you need the combobox bound to EmployeeID because that's the primary key, which seems correct to me. Might work in some cases if you are binding the hours column - depends on the situation. But either way it can still map to hours. To get the hours associated with the employee use DLookup:

[TSTMonTotalHrs]*DLOOKUP("NameOfField","NameOfTable", "EmployeeID = " & [EEVacPayRate])

The final parameter in the function is basically a where clause without the word where. Be careful when using this but basically you can use string concatenation to create the criteria - the syntax must be correct when you do this (with single quotes around string literals and hashes around date literals, if applicable). The table you are looking up here is the table with the vacation rates in it, of course.