Button Press enters a (calculated) figure on form?

[RSIboy]

I'm sure this is really simple... apologies, but a search hasn't thrown up any clues.

I have a table with Invoice Value, and Commission Value in it. On the associated form I enter the Invoice Value, and also the Commisison value [which 99% of the time is set at 20%.

I am trying to make a button which, when pressed, enters the calculated commission in the form field (and underlying table) - I realise this could be in the 'On Click' property on the form, but in VBA I am after something like.

Private Sub btnCommCalc_Click()
tblSales.Commission = tblSales.Invoice * 0.2
End Sub

Could someone help me with the syntax? (I can then move on to using a variable, rather than hard-wiring the 20% ;-)

Many thanks

 

[Mile-O]

I am trying to make a button which, when pressed, enters the calculated commission in the form field (and underlying table)

Delete the calculated commission field and replace it with a field for storing the percentage. You can calculate the commission at any time by using a query, a form, or a report.

By doing this you are not violating Third Normal Form.

 

[RSIboy]

Thanks for the prompt reply SJ - I will have to digest.... ;-)

Just for reference, could you clarify what is wrong with the single line - (to change a field to a different value on the form)

Cheers

 

[Mile-O]

could you clarify what is wrong with the single line - (to change a field to a different value on the form)

I don't understand your question.

 

[RSIboy]

Sorry SJ...

All I meant was what is wrong with the syntax of the line:

.[Field] =

.[AnotherField] * [constant]

It's one of those situations where I did a 2-day VBA course and *loved* it - gave me the opportunity to drastically improve our [very small] company's database... Unfortunately, the familiarity has now faded as I havn't touched it for 6 months, and am getting frustrated with being unable to make a button enter a value!

Your patience is appreciated

 

[Mile-O]

All I meant was what is wrong with the syntax of the line:

.[Field] =

.[AnotherField] * [constant]

Two things:

1. You've already said that the percentage is not constant. You'd still store VAT (at 17.5%) in a field rather than have it as a constant as it is subject to change no matter how often it actually changes.
2. Database design calls for storing only the base information - not derived/calculated data. The keyword here is normalization which there are many threads on.

 

[RSIboy]

Thanks again SJ - you're not going to let me implement anything morally incorrect (from a database viewpoint) are you !!

I was trying to simplify my problem for the sake of explanation, and hence used 'sales and commission'. In our specific business situation, we are commercial brokers needing to record a policy's premium, and our earnings from that policy - These are sometimes commission (%) or sometimes a fixed fee, and sometimes a combination! - In short I do want to store this separate piece of information, and was simply looking to give myself a shortcut in the majority of situations when entering data.... I guess you'll tell me the best method would be to enter a % of the premium into the commission box when the premium is entered, leaving me the ability to overtype if the policy is a fee-type situation... [In this case, I still have the same problem - presumably I could use 'On Leave' for the Premium field - but what syntax would I use to set the value of the Commission field]

Once again, thanks for your comments. When making the original database I found this forum to be invaluable.

 

[Rich]

Is it too difficult for you to enter the commission rate that gives you the same figure as your fixed fee?

 

[Mile-O]

I guess you'll tell me the best method would be to enter a % of the premium into the commission box when the premium is entered, leaving me the ability to overtype if the policy is a fee-type situation... [In this case, I still have the same problem - presumably I could use 'On Leave' for the Premium field - but what syntax would I use to set the value of the Commission field]

Sort of....I'd just put 0.2 in the DefaultValue for the rate field and overtype it when necessary.

When making the original database I found this forum to be invaluable.

Then why would you, all of a sudden, want to go down the morally incorrect (from a database viewpoint) route?