By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,423 Members | 1,601 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,423 IT Pros & Developers. It's quick & easy.

Updating a field on a form with a total obtained from another related table

P: n/a
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.

Jan 23 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Chippy wrote:
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.

see response to your other post.
Jan 24 '06 #2

P: n/a
TY!
much appreciated.
Jan 24 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.