Store Calculation In Table And Run As Code In Form

[Lewis825]

Hi Guys,

Ok I have a table which lists suppliers, each of our suppliers have diffrent discount stuctures.
These discount structures are stored in the supplier table as a simple math equation

The Aim Is...

When adding a new product I want to automaticlly work out my cost price at the click of a button dependant on the Supplier ID entered on the form.

so the code will look at the supplier, get the formula from the suppliers record, run that formula with the Lsit Price entered on the form, then throw out my cost price after discounts & conversions.

Hope this makes sense.....



Thanks In Advance

[John_G]

Can you give an example of what the stored math equation looks like? Presumably it is text.

You can probably achieve what you want using the Eval() function.

For example, if X=3, and your "equation" y= "*2" as a string, then eval(x & y) yields 6 (2*3)

[orange]

Further to John's question and advice, do you have the same discount for all products for a given supplier? Is/are there different discount structures for different quantities?

Discount rate for 1-20 of product X; discount rate for 500-999 of product X????

Sample data to illustrate your intent would help.

[Lewis825]

Thank you for your input,

the discount sturcres are

sup1 - ((ListPrice-60%)-5%)/ConversionRate
sup2 - (ListPrice-50%)/ConversionRate
sup3 - ListPrice-30%
sup4 - ListPrice (No Discount Given)

We do not get price breaks depandant on quantity so no need to worry about that

Hope this Helps,

[orange]

It appears that you have a limited number of Suppliers.
What happens if/when you get 2, 5, 10 new suppliers?

What exactly is Conversion Rate?
You are saying that every Product regardless of quantity has the same discount rate, correct?

[Lewis825]

It appears that you have a limited number of Suppliers.
What happens if/when you get 2, 5, 10 new suppliers?

What exactly is Conversion Rate?
You are saying that every Product regardless of quantity has the same discount rate, correct?

This is just a smaple of suppliers, we currently have around 30 suppliers, and add new ones on a weekly basis.

hence the reason i would like the admin staff to be able to add a new supplier and work out our cost prices on the click of a button, with out having to calculate it them selves or add a formula into the VBA coding

conversion rate is to do with the currency, we have to convert some prices from Euro to GBP

Yes, prices are the same regardless of quantity

[orange]

It seems you have a relatively complex calculation. I say that because ListPrice is not a constant. Suppliers and vendors often change/adjust prices. And for some stock/items, there may be block discounts/clearance to move stock before fiscal period end etc.

I have written a few posts referencing AgreedToPrice ,historic orders , discount which may be useful.

You might consider DiscountRate or similar field with values such as .23 (23 % discount). This would be applied to the current ListPrice... I also think you should be clear on your Price fields/definitions. Current selling price, list price, cost price, discounted Price, AgreedToPrice...
Especially if, as you say, users are recording new discount factors etc. I don't agree that general users should be modifying/inserting the discounting algorithm anywhere. The value of a user interface, forms and logic is that you control the various components that are involved. A simple coding/typo can not get saved. There is always some "does the values make sense" test to ensure there are no 200 % discounts etc.

There was a poster who had different rates for each product for each customer and then had factors based on volume purchases. It was tedious to monitor and maintain.

Might be easier to have a limited number of categories, each with an identified discount factor. Then assign a supplier to a category, and the discount factor is mostly automatic. This sort of thing will reduce maintenance and does not preclude loyalty programs nor individual agreed to prices.

Good luck. Hope some of this is helpful.