Overide calculated control field

[louisa]

I have a calculated field, with a control source of =Nz([GrossOperatingProfit],0)/2
i am hoping that if i have a yes/no tick box and i select yes that i will be able to manually change the figure in the calculated field. Is there a way i could do this?

 

[gemma-the-husky]

no - if its calcluated, its not editable

what would be the point of editing a calclulated field anyway. What are yuo tring to achieve - set/change the figure for gross operating profit?

if so, popup a form (eg on a dble-click of the calculated control) - get a value and update it back in the underlying table

update thetable set profit = thenewvalue where ID = something
then refresh the form, to re-read the value.

note that this needs formatting correctly

 

[louisa]

Sorry Dave, sometimes the amount may be slightly different but only on a few occasions so i was hoping that maybe i could edit it.

 

[missinglinq]

Instead of using the Control Source you'd need to place your calculation in the GrossOperatingProfit_AfterUpdate event.

That way

1. The calculation will be done
2. The control could be edited, if necessary
3. The value could be stored via its Control Source

This third item is important. Since the value is sometimes entered manually, rather than always being calculated, it has to be a stored value.

Linq ;0)>

 

[louisa]

Thanks for helping, i have tried what you suggested, i have placed the calculation in my gross operating profit after update and removed the calculation from the control source but the figure just shows zero, i cannot get it to work out the value??

 

[missinglinq]

I guess we need to see your exact code to be able to troubleshoot it, as that should work.

 

[louisa]

I am using =Nz([GrossOperatingProfit],0)/2 in the control source.

 

[gemma-the-husky]

as I have said - you cannot edit a calculated control directly.

a) is this control bound, or unbound? It should be UNbound.
b) if you want to change this, you need to change the underlying value for GrossOperatingProfit.

 

[louisa]

Hi Dave,

I am trying missinglinq advise.

 

[louisa]

Can anyone help at all,,, please.

 

[DCrake]

As missingling requested, hat is the exact code you are using on your after update event of your text box?

 

[vbaInet]

Looking back at your post #7 it's clear that you didn't follow what missinglinq advised. You were still calculating in the Control Source.

 

[louisa]

Sorry maybe i wasnt clear there, the calculation in post #7 is the calculation that i placed in the after update event of the GrossOperatingProfits field.

 

[louisa]

I removed the calculation from the control source.

 

[vbaInet]

I think your case might be one of those cases where you need to save the calculation instead of just displaying the calculation.

So create a field to store the calculation. The calculation will only be performed after the GrossOperatingProfit is changed. Then set the field's value to the calculation in the After Update event of GrossOperatingProfit.

 

[louisa]

I am sorry but i am getting really confused over this and i am trying to research online but to no avail.

In my first post i mentioned i have a calculated control of =Nz([GrossOperatingProfit],0)/2.
I have seen that i failed to mention some further information which i apologise for if this is a reason i cannot get it to work.

I have two fields, one called GrossOperatingProfit and the other CommissionDue, The GrossOperatingProfit field is a calculated field and so is the CommissionDue. The CommissionDue field has a control source of =Nz([GrossOperatingProfit],0)/2 so it is halving the value in the GrossOperatingProfit field and displaying the amount in the CommissionDue field. On occasions i will need to edit the CommissionDue field so i am trying to find a way that it will work out the CommissionDue but also let me edit if required. I am realtively new to all of this so i apologise if i do not understand what you mean. Thank you for all your help so far.

 

[vbaInet]

I think we've covered this a few times but I will try to explain differently.

1. You need to create a field in your table to replace the CommissionDue field. As has already been mentioned, you cannot edit data in a calculated control.

2. Perform an Update query that will halve GrossOperatingProfit and updates this new CommissionDue field.

3. Change the control source of the CommissionDue textbox to [CommisionDue] so it displays data from this newly created field.

4. In your form, under the After Update event for the GrossOperatingProfit control put the following code:

Me.txtCommissionDue.Value = Me!GrossOperatingProfit/2

Where txtCommisionDue is the name of the textbox that is bound to the CommisionDue field.

I hope this makes sense.

 

[louisa]

I will give it a go, this may send strange to you but i have never done a query before, wish me luck. Thank you for your help.

 

[vbaInet]

Good luck!!

Drop us a line if you're struggling with the query but obviously Google for some tutorials on Queries.

 

[louisa]

Hi,

I am not sure if i am reading this correctly or understanding, i am not sure how to assign a query to a text box.