Form that allows user to select fields to build an expression that outputs a calculation?

[myoung2746]

Hello,



I'm new to this forum and just started working on building a manufacturing database for my family's small business (cheese making).

I would like to allow any database user to easily use a form to build an expression that calculates recipe ingredient amounts. (I want to make a user-friendly form for building these expressions because users will need to be able to build different recipes based on the cheese they are making, I can't just build the ingredient formulas one-time and walk away)

For example (form screenshots included)

1. on frmProduct, the user inputs the different ingredient steps in a specific order (building a recipe), like pumping milk and estimated cheese wheels produced, to make a specific cheese product (i.e. Alpine)
   
   • The user needs to input a formula for estimated number of cheese wheels based on the milk pounds used (i.e., ((Milk Pounds)/8.6)/11).
   • This formula could be different depending on the product recipe, so user needs to be able to input it into the product recipe form
   
   
2. Then on frmProduction, the user begins filling out the Production sheet (which is generated from recipe built on frmProduct, think of it as an instance of the recipe) to make the cheese
   
   • The user first inputs the number of milk pounds used (first step of the recipe)
   • The last step of the recipe "estimated cheese wheels" then needs to take the user entered formula for estimated number of cheese wheels from the recipe sheet that was built on the frmProduct, and calculated the number of cheese wheels on frmProduction

Any ideas on building this process?

[CJ_London]

you can use the val function or the eval function

val("[Milk Pounds]/8.6/11")

the trick is getting the value for milk pounds. perhaps a domain function such as dlookup or dsum

you have only provided one example of a calculation. If they are all based on milk pounds then the solution will probably be straight forward - depends on how normalised your data is - but not enough information to suggest what that may be.

But if you are having to 'look up' other values, we need to know what they would be and how they are in turn calculated so we can determine the scope of the calculations.

For example perhaps all the different types of values are all based on a step order of 0.1

[myoung2746]

Thanks Ajax, I will definitely play around with the val and domain lookup functions.

The estimated cheese wheels formula calculation was just a simple example, each recipe would have several other recipe steps that would need to calculate numerical values based on numerical values entered in prior recipe steps (like milk pounds, but also based on other step values like grams of a certain ingredient used etc.)

All values that need to be looked up would have been entered in the recipe form (frmProduction), which is stored in a normalized table tblProductionStepInstance, where each record is an instance of a recipe step.

I was hoping there was a way for the user to enter a formula for the corresponding recipe step in frmProduct (stored in a normalized tabletblProductStep, where each record is a recipe step for making a product) that could then be called in frmProduction to calculate a recipe ingredient numerical value for the corresponding recipe step.

Can you explain what you mean by "all the different types of values are based on a step order of 0.1"?

[CJ_London]

deleted - only draft to post #5

[CJ_London]

Can you explain what you mean by

you will need to apply some rules to find [milk pounds] - one of those rules might be that whatever you are looking for can be found in a record where step=0.1

I don't see a field value of 'milk pounds' only 'milk pump', 'milk pumped' and 'milk pounds pumped' so to find the value you will either need to use an algorithm or a lookup as to where to find these values (e.g. 'where productionstep like replace(replace("[milk pounds]","[",""),"]","") & "*") or some other indicator such as step=0.1 - and you still need to identify 'milk pounds from your calculation - for that you will need to use string functions like left, mid and instr and instrrev. - and that will get more complex if your calculation contains more than one value surrounded by [ and ]

you might find it easier to apply your formula calculation in the form recordsource rather than the form control and/or even use a vba function.

when I've done this sort of thing in the past, the user does not just write the formula, the user uses a continuous form to pick from a list of stored values and similarly picks the arithmetic characters - a bit like the way a calculator works and in the background the form actually creates a sql string and it is that that is stored.

good luck with your project

[myoung2746]

Great ideas, I've got some good direction now. Thanks Ajax.