How to see results in a table or report when using formula in a split form?

[nicole05]

Can any body help me?
I am using ms access 2007, i have a problem regarding formula.
My form:
Table Name: Cellphone
Fields Name : "Cellphone List" "Cost" "Actual Cost" "Difference" "Percentage"

I am using a split form."Difference" and "Percentage" fields have formula.

Control Source of Difference "=[Actual Cost]-[Cost]"
Control Source of Percentage "=[Cost]/[Actual Cost]"

It really works fine i can saw all results in split form including TOTALS but my main problem was i can't see results of "Difference" and Percentage" on a table
or if i make a WIZARD REPORT only "Cellphone List" "Cost" and "Actual Cost" have values but "Difference" and "Percentage" fields i cant see any value.
I thinks it is because the 2 fields have an equation? I dont really know what's the problem? Please help me to solve it.Thanks in advance

[C CSR]

Assuming you are set up to protect the data from input on the form (diff & percent) you can set up the following Form OnCurrent event and leave the fields control source alone (reset it to the fieldname in the dropbox):

Private Sub Form_Current()
Me.diffence = Me.ActualCost - Me.Cost
Me.percent = Me.Cost / Me.ActualCost
End Sub
 

The table will accept the calculation. Yu may have to run an update query accross the board or stroll through the records real quick to change the present data value. There are a few other ways using queries as your record source for the form. I'd rather manipulate it from the code.

[C CSR]

Use the properties box for the whole "Form" in design mode, look at the Event tab, select the "..." next to "On Current" and insert the two inner lines above inside the subprocedure. Change the variable names I used, of course, to match your fieldnames. I hope this is clear for now.

[NeoPa]

Nicole05:
It really works fine i can saw all results in split form including TOTALS but my main problem was i can't see results of "Difference" and Percentage" on a table

Why would that surprise you? You have formulas on controls in your form that show you these values in the form. You have nothing in your table that does anything similar. Why would the fact that the information which is on the form, is missing from the table be cause for thinking anything is wrong?

PS. Updating fields in your table should never be recommended by anyone who understands Normalisation (See Database Normalisation and Table Structures.) or working with any RDBMS. You should think very carefully before considering taking that course in any database design.

[C CSR]

I meant only update to fix the preexisting condition. After that no update would be necessary.

[NeoPa]

I'm not sure I follow clearly. I'm saying that [Difference] & [Percent] fields shouldn't even exist as they represent data that is already calculable from existing data. I didn't mean to be disparaging of your posts (as I've seen some of your good work), but Normalisation is such an important and fundamental issue that it needs to be highlighted wherever it comes up.

[C CSR]

Quote from OP:
"but my main problem was i can't see results of "Difference" and Percentage" on a table."

The OP wants the results in a table, and didn't see making his calculations for other applications of his data somewhere down the road. Also, even if he uses my initial approach, he has some coding to do on the form controls (events) so that if he changes the value for Cost and Actual cost on the form, the Difference and Percentage controls would have to change with them.

I appreciate your mentorship.

[NeoPa]

Whenever I see any question that pre-supposes a non-normalised approach (as in this case), I feel it's my duty to enlighten. Not only for the benefit of the member asking the question, but also for all the others who read the thread over time. That is not to say I won't help them go the wrong way if they are insistent, that is their choice ultimately, but I always explore first their understanding. This also ensures that the warnings are there before anyone else thinks any non-normalised approach is a correct solution. Nearly always it's simply lack of awareness that leads them to think that way.