I am seeking assistance with an Access 2007 DB - I have poked around with very basic Access and I'm trying a few more advanced programing. I am very much jammed

The background This database is a costing tool for a food manufacturing company. It's really simple - it was 3 tables Ing, (which is short for Ingredients) Recipe and Details. The database has a Recipe form and a Details form - The recipe form is the Parent and the Details is the Child or subform The idea here is that the user can create a recipe name, then choose the ingredients from a look combo box that pulls from the Ing table. Then the user enters in the QNTY used and the sub form calculates the total cost in a text box called TotalCost. (I used DLookup in a text box called Cost to pull in the cost off the Ingredient table) What is important here is the the cost of Ing can change so we wanted that to be fluid and not store in a table

My problem is creating the total using Dsum function on the form- I've placed a Text box on the parent for Recipe and I'm trying to sum up the total cost of the text Box's on the sub form unique to that RecipeID. This is what I used. The problem is that the result I am getting back is not correct. The more entries I have in the sub form the higher the increase of error is as the result. I hope I communicated this efficiently.

=DSum("Form!Details!TotalCost","Details","RecipeID=" & [RecipeID])

Thank you in advance for your advise

Brandon

Hi Brandon,

(IMG:style_emoticons/default/welcome2UA.gif)

Instead of using DLookup() and DSum(), you might be able to modify the query for your subform to include the total cost for each ingredient so that you can then have a grand total in your form. For example:

SELECT DetailsID, RecipeID, IngredientID, Qty, Qty*Cost As Total
FROM tblDetails
INNER JOIN tblIngredients
ON tblDetails.IngredientID=tblIngredients.IngredientID

You can then try something like this in your textbox:

=Sum([Total])

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

One thing that I see right away is that the criteria for your DSUM statement appears not to be a string...

I will try working with this to see what I can work out

Okay... Let us know how it goes...

Thanks, can you point out why this is not a string. It would be a helpful learning lesson for me.

Many thanks