41
Hi..I’ve been teaching myself access through trial and error and google, but now I am completely confused and STUCK!
These are the relevant forms in my database:
frm_Recipes:
Main form for entering/viewing recipes. Recordsource is tblRecipes. Has a subform – frm_RecipesSubform
frm_RecipesSubform:
Continuous forms subform on frm_Recipes - shows the ingredients for the recipe displayed on main form.
FrmIngredients:
Used for entering and editing ingredients and their details (package cost, package size, grams per cup). Recordsource is tblIngredients.
Originally I had my subform’s recordsource as tblRecipeIngredients, and used vba to do all the calculations, but I was storing a lot of calculated values, and I want them to update whenever any relevant value changes. After a LOT of research I found out that this was wrong so have been trying to base the subform on a query, Query3 instead. Everything seems to work well, except for the IIf statement in my query.
frm_RecipesSubform: (relevant fields only)
[Quantity]:
User entered quantity eg 1/2, 1, 1 1/2
[comboMeasure]:
Type of measure eg cup, g, tsp
[comboIngredient]:
On NotInList, ingredient is entered into tblIngredients, and frmIngredients is opened to allow details used in costing calculation to be entered (PackageSize, PackageCost, GramsPerCup – CostPerUnit is calculated after update of either of the first two). After this form is closed I have a requery on Activation of the subform, to update the calculated fields.
[CalcQ]:
After update of Quantity or comboMeasure, values are changed to decimal (0.5, 1, 1.5 etc), and this is divided by the relevant amounts depending on whether it is cups , tsp or whatever, through VBA and stored in CalcQ. I understand about not storing calculated values, however the quantity and measure fields will only ever be entered or changed via the subform.
[subRecipeCost]:
In form footer - =Sum([IngredCost]) This will be passed to the main form and stored (I know, stored values, *sigh* ;) ) so that I can search by recipe cost in another form.
[IngredCost]:
I need to use one of two different formulas to calculate IngredCost depending on the value of [Measure]:
IIf([Measure]="cup" Or "cups" Or "tsp" Or "tbsp",[CalcQ]*[GramsPerCup]*[CostPerUnit],[CalcQ]*[CostPerUnit])
I just can’t get this to work, it only evaluates the expression as true, never as false. I’m guessing that it is looking at all records in the field [Measure], instead of each individual row..not the required effect at all. What is wrong here? Is there a way to achieve this?
First I had CalcQ as just the decimal version of [Quantity], with a bunch of nested IIf’s in my query to do the relevant division (for tsp, tbsp) but changed it because I thought the problem was with nesting the iif’s.
Any help would be very much appreciated, as I said I am absolutely stuck. Sorry this is long but I wanted to (try to) be as clear as possible :)
