| re: Updating field on a form from a sum of values field on a subforms footer.
Chippy wrote:
[color=blue]
> I am having trouble working out how to update a field on an open form
> with a calculation based upon calculations from other tables!
>
>
> For example, I have 3 tables:
>
>
> Diary
> Diary_ID: autonum, primary key etc
> Diary_Date: date/time
> Diary_CaloriesTotal: number
>
>
> Calories
> Calorie_ID: autonum etc
> Diary_ID: number - linked referentially to Diary.Diary_ID
> Food_Lookup_ID: number lookup on food table
> Calorie_Amount: number
> Calorie_TotalValue: number (this is amount * food.value)
>
>
> Food
> Food_id: autonum etc
> Food_Name: text
> Food_Value: number
>
>
> I have a main form for Diary and a sub form for Calories (plus a
> "setup" form for food).
> When you create a diary entry you can add Calorie information and in
> the subform it quite happily calculates the total for each food entry
> AND displays a runing total in the sub-forms footer.
>
>
> However, I'd like to update Calories_Total on the Diary form with this
> same running total from the sub-forms footer.
> I don't mind if this is done on-the-fly or via a button etc.
> I know the SQL to use in a query to ge the values, but I don't know how
> to get that value in VBA to pass to the field. i.e. When I run the
> SQL, how to I get a return value?
> Or is there another way using queries where [Calories].[Diary_ID] =
> [!forms].[diary].[diary_id]
>
>
> [As additional information: In the future I'll have forms for using
> calories too, so want to have a total used, total eaten and then the
> net total on the diary form]
>
>
> Thanks for any assistance.
>[/color]
I'll assume you store the calculated value Calorie_TotalValue in the
table Calories. If so, then in the Form_AfterUpdate event of the
Caleries subform you could enter something like
Forms!Diary!Diary_CaloriesTotal = _
NZ(DSum("Calorie_TotalValue","Calories", _
"DiaryID = " & Me.DiaryID),0)
| 
