Edit Functions "on the fly"

[baronvont]

Is there any way to edit a custom function without going into the modules window and accessing the actual module?

From time to time the user will need to add, edit or remove parts of the function referenced by the "select" clause which does the calculation (usually 1 or 2 lines of simple code) and at the moment we have to allow him to edit the module itself - a bit dangerous!

The function returns an insurance premium based on the type of cover selected, as well as the vehicle data. The steps involved are

1. Create a new cover description eg PV - Private Vehicle
(this is held in a table so no problem here!)
2. Create the corresponding code in the CalcPrem(cc,value,cover) function which calculates the amount of premium based on engine capacity, value of vehicle and required cover type. The function uses a simple select case based on the cover so we have to add the case "PV" line into the code with it's associated formula.

Appreciate any help!

 

[Cagliostro]

I think you can use
DoCmd.RunCode
The string you pass as argument, you'll create on the fly. John Fill

ivfmd@mail.md

 

[MichaelRed]

The whole scheme sounds " ...a bit dangerous!".

Where-ever the "agent"? gets the info for the formula, should be available to the 'programmer' who sets up the app. ALL of the info should be placed in (APPROPIATE) tables. The parts applicable should be available through a selection process (fom w/ check boxes?). The final result should be calculated based on thee selections. All of the selections and their cost elements shold then be available to the 'agent' and customer as a report.




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[baronvont]

Thanks MichaelRed,

I don't think I follow you, can I try and describe the situation a bit more generally.

On a form are controls for Engine capacity, value and a dropdown for the cover type. A fourth control displays the formula =CalcPrem(cc,value,cover)

As I enter values for the vehicle, eg 1500,200000,PV the function must then return the insurance premium. Say the formula for PV is (Value * .05) + (cc * .1), I'll have a line in the function like

Case(PV) ' Private Vehicle
CalcPrem = (Value * .05) + (cc * .1)

Now I want to add a new code to the list eg CV. I'll have to modify the function to include

Case(CV) ' Commercial Vehicle
CalcPrem = (Value * .08) + (cc * .15)

As the developer, it's not a problem, but I naturally don't want the user messing with the modules directly, so can I insert/detete these lines any other way?

Thanks
Georg

 

[MichaelRed]

What I am 'saying' is that the 'buyer' (your employer?) of the application needs to provide you with the COMPLETE information of the 'things'/objects which they will insure, and the premium calculation (including variations). All of this to be ANALYZED and placed in some data container (TABLE?). When a priemium is calculated, it must be able to extract the necessary elements for the calculation from the "TABLE" and return the premium. If the insurance is available for a wide variety of categories (Vehicles, Buildings, Jewlery ... ), you might need several tables, and select the ata elements from the appropiate object type table. The actual calculation should be able to be done with a selection of just a few "formulas", based on information (again) in the table which could also have different coefficients for various terms predicated on other fields in the table.

All of this "SOUNDS" a lot harder than it actually is, but you NEED the information from the "buyer". It doesn't seem at all reasonable to have 'agents' just "making up" the formula for the premiums on a 'whim'.

refering to your example:
Case(PV) ' Private Vehicle
CalcPrem = (Value * .05) + (cc * .1)

Now I want to add a new code to the list eg CV. I'll have to modify the function to include

Case(CV) ' Commercial Vehicle
CalcPrem = (Value * .08) + (cc * .15)


the Formula is the SAME, only the coefficient of value and CC vary, so if there is a table for the coefficients:

[tblVehCoef]
[VehType][tab][VehCoef][CcCoef]
Pv[tab]0.5[tab]0.1
Cv[tab]0.8[tab]0.15

Then the single FORMULA appllies to both:

CalcPrem = (Value * [tblVehCoef]!][VehCoef]) + (cc * [tblVehCoef]!][CcCoef])

So, once the vehicl type is known, the coefficients are looked up in the table and applied to the calculation.

There is probably a bit more to the complete soloution, but the concept should be understandable. I would also suggest some additional reading and practice work. This should be very basic 'stuff' for anyone doing 'professional' work in Ms. Access (or most any programming environment).



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[baronvont]

OK, I see where you are coming from. But as you say, it does get messier, I can have the following...

Case NEW

If CC > 3000 then
CalcPrem = Value * .1
else
Calcprem = 1500
Endif

ie. the formulas have a variety of formats and there are over 30 different codes for the cover types...

What I was envisioning was a table with a memo field where my user (who has basic VBA literacy) would type out the formula as required and then with code insert it into the existing function. The main reason being I would rather he didn't tamper with the module as he may mess something else inadvertantly!

Alternatively, I am looking at Eval & RunCode to see if I can run a bit of "code" residing in a field.

Anyway, thanks for your input! I am obviously no one of the pro's and tend to hack out solutions as I need them - with help from the forums!

 

[MichaelRed]

You, of course, need to do what you CAN do.

Having said that, I can also say that your approach will - ultimatly - get messier than what I suggested.

Good luck. I think you NEED it.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[jflachman]

I have to agree with MichaelRed. You really end up with the same issue when you let the user add to the module, even if he/she does it through a form.

If you set up a table with fields for whatever information is required to calculate the premium, you'll be much better off.
So your table would look like this

Type value% cc%
PV .05 .1
CV .08 .15

You would use a SQL query statement or index seek on the vehicle type. That should yield the ROW in the table. Once you have that, your code would look like this:

CalcPrem = value * value% + cc*cc%

your formula may be more complicated than that. You may even have different types of formulas for different classes. The only information you NEED to have to set it up this way is the structure of the formula(s).

Letting a user write code is REALLY asking for trouble. It also depends on a certain level of knowledge in the end user. What happens if your current user leaves the company and is replaced by someone who couldn't write "Hello, World"?