Manually modify the result of a calculated field in a form

[Lisa Papakis]

Hi everyone.

I have a calculated field which I would like to be able to over-write the data if applicable. It's a number field which calculates 15% discount on another number field (giving the wholesale price for a product). Some products, however, have more than 15% discount, and I have the figures for actual cost price rather than the percentage discount, so I need to be able to use my minus 15% calculation most of the time, but sometimes I need to over-rule it. I'm sure this is a really simple answer but I'm missing it - please help.

 

[missinglinq]

How/where are you currently doing this calculation?

 

[Lisa Papakis]

It's a calculated field on a form. I've also tried it in a normal field, bound to a query, but still it doesn't allow me to overwrite. Tks.

 

[Lisa Papakis]

It's a calculated field on a form. I've also tried it in a normal field, bound to a query, but still it doesn't allow me to overwrite. Tks.

 

[Lisa Papakis]

It's a calculated field on a form. I've also tried it in a normal field, bound to a query, but still it doesn't allow me to overwrite. Tks.

 

[Rabbie]

If you have it as an unbound field with no default value you could calculate it with the lost_focus event of the field holding the wholesale price. This would let you change it if necessary.

 

[Lisa Papakis]

Sorry Rabbie, I don't understand. I haven't used Lost-Focus before, I found it in the properties, events, list and played around a bit but got nowhere. If I understand you correctly (and I probably don't), the wholesale price of every product is different, so entering it into the properties of every record is not going to be viable. Thanks for your help so far.

 

[Rabbie]

Hi Lisa

I have forms where I can alter the values manually if required. I drive these by calculating them when I get Lost_Focus from the previous field. Normally I have a fixed formaula sinilar to your 15% discount. Your design is obviously different so without seeing it I cant comment further other than to outline my approach.

Basically If you have a fixed formula for the field you can't alter it directly as you want. So you need to fill in the field indirectly. The On current event might work for your form.

Then you can alter the value manually if required before you update the DB.

 

[missinglinq]

It's a calculated field on a form.

This doesn't say where the calculation is being done! What event does it occur in? What code are you using to do the calculation? Where is the code placed?

 

[Lisa Papakis]

On the form, I put a text box. In the text box, I typed

=[Price GBP website]-[15 percent]

(the 15 percent field already has a calculation in it). This returns the total I need, but I need to be able to overwrite it, sometimes. So the calculation, in this case, is done in the form.

I have also tried it with a text box on the form bound to a query, which does the calculation but again, does not allow me to overwrite.

I have not used any events.

Many tks, Lisa

 

[missinglinq]

Okay, now we know where/how you've done your calculation! You see, there are several ways to have a "calculated field on a form!" The one you've chosen is to use your calculation as the Control Source, which does, indeed, keep it from being edited manually. You need to move this calcualtion to elsewhere in your form, like in the AfterUpdate evnt of the field where you enter the [Price GBP website]. Am I correct in assuming that [Price GBP website] is a textbox? If so, you'll need to do something like this where YourTextBox is the name of the text box you're trying to fill:

Private Sub Price_GBP_website_AfterUpdate()
  Me.YourTextBox =Me.[Price GBP website]-Me.[15 percent]
End Sub

Now go to the Properties box and remove the formula from the Control Source. The calculation will take place after you enter the [Price GBP website], but you should now also be able to go in and manually change it.

Now, here are a couple of other points. Since you had the Control Source set with your formula, you obviously were not storing this value in your table. Normally a calculated value isn't stored, you simply re-calculate it anytime you need it, but in this case it probably needs to be stored (if you'll ever need to refer to it again) because you may be manually overriding the calculated value! If you need to store it you need to bind it to a field in your table.

Also, you really need to stop using field/control names with spaces in the name; it forces you to use square brackets around the name anytime you refer to it in code, and sooner or later you'll forget to do that.Far better to use names like [PriceGBPwebsite] than [Price GBP website]!

 

[Lisa Papakis]

Missinglinq, I have followed your instructions, but I'm getting an error message. Here's exactly what happens.

Noting your recommendation to store the value in a field in my table, I created a new text field in the table called *[Wholesale Price GBP]*, and put it on my form. (Its data type is currently 'text' rather than 'number' - could this be causing the problem?)

I opened the Properties box of the field *[Wholesale Price GBP]*, and went to AfterUpdate. I opened the Expression Builder, and typed in the top window

Private Sub Price_GBP_Website_AfterUpdate()
Me.[Wholesale Price GBP]=Me.[Price GBP Website]-Me.[15 percent]
End Sub

And I get an error message saying that it has invalid syntax. So then I played around with various combinations of your code, and the nearest to success I achieved was

=Me.[Wholesale Price GBP]=Me.[Price GBP Website]-Me.[15 percent]

which returned the error message

'The expression AfterUpdate you entered as the event property setting produced the following error: the object doesn't contain the Automation object 'Me.'.

Probably I am putting the code in the wrong place.

I understand what you're saying about omitting spaces in field names, that will be useful from now on.

I really would appreciate your continued help.

Kind regards, Lisa

 

[boblarson]

=Me.[Wholesale Price GBP]=Me.[Price GBP Website]-Me.[15 percent]

which returned the error message

'The expression AfterUpdate you entered as the event property setting produced the following error: the object doesn't contain the Automation object 'Me.'.

That error tells me that you likely put the code in the event PROPERTY instead of the event in the code window. See this for more info on where to put the code:

http://www.btabdevelopment.com/main/QuickTutorials/Wheretoputcodeforevents/tabid/56/Default.aspx

 

[Lisa Papakis]

Missinglinq and Bob - it's perfect, it works beautifully. Thank you so much for saving me days of hopeless searching. I will never ever put spaces in my field names again, and I promise always to use the VB editor, in honour of you both! Efharisto para poli, kai filakia, Lisa

 

[boblarson]

Glad we could get you on the right track. Linq and I are definitely glad each time we manage to assist someone who needs help.

 

[KimHuff]

I have a similar issue

I have a similar issue as Lisa and was hoping I could find some assistance.

I have a very basic database that manages our inventory and pricing. tblSupplements has a variety of fields including ProductName, Count, WholesalePrice, RetailPrice, StockQuantity, etc.

In most cases (95% of the time), the MSRP is double our cost. So, I created a query and in there I made an expression that calculates the MSRP. Unfortunately, I am unable to edit this field. I would like to be able to change this amount for some products since some of the manufacturers have different criteria.

Obviously, the query is calculating this on the fly, which means I will need to also have a place to store the MSRP. Does this mean I need to create a new field in the tblSupplements? If so, then how do use the event procedure?

Thank you in advance.

Kim