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_CaloriesT otal: 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_TotalVa lue: 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. 1 1642
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_CaloriesT otal: 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_TotalVa lue: 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.
I'll assume you store the calculated value Calorie_TotalVa lue in the
table Calories. If so, then in the Form_AfterUpdat e event of the
Caleries subform you could enter something like
Forms!Diary!Dia ry_CaloriesTota l = _
NZ(DSum("Calori e_TotalValue"," Calories", _
"DiaryID = " & Me.DiaryID),0) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jason |
last post by:
Let's say I have an html form with 20 or 30 fields in it. The form
submits the fields via POST to a php page which updates a table in a
database with the $_POST vars. Which makes more sense?
1) simply UPDATING the values for all fields in the table, whether or
not any particular field has actually changed
2) running a second SELECT...
|
by: MX1 |
last post by:
I have a query written in MS Access that has a few calculated fields. Is it
possible to refer to that query in a form field. I'd like the form field to
show the sum of one of the columns from the query I've written. I've tried
to put the following in the form field's control source property but I'm
getting an error. Query1 is the name of...
|
by: chris vettese |
last post by:
I've been having trouble with this for a long time. I have a form
that has many subforms. In the subforms I have unbound text boxes in
the footer that sum a field. I want to Display this sum on the main
form for each subform and then add them all together. In a book I
have it gives the following example:
=(Object type!!.Form!)
I...
|
by: Claude |
last post by:
Let' say we have an application for a production facility running 24/7
broken into 3 shifts 6-2,2-10,10-6
each production report date contains 3 shifts as above
in each shift there can be from one to several different product runs (the
average is about 4) but it could be more or less - it's not defined or
definite for many reasons
...
|
by: Richard Stanton |
last post by:
Hello all
My database has a main form linked to table1. It has several subforms
on the main form, all linked to table2. Table1 and Table2 are linked
by primary/foreign key, no duplicates allowed, one-to-one.
If I update field(s) on a single subform it works fine but when I
update fields on multiple subforms ie without closing the form...
| |
by: Chippy |
last post by:
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
|
by: bassamsuha |
last post by:
Hi,
I have 2 subforms in my main form each of them has its own Autoincrement ID number.
When I try to add new record and filling table fields It showing me an error :The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.
I know the problem is with the...
|
by: Ecohouse |
last post by:
I have a main form with two subforms. The first subform has the child
link to the main form identity key.
subform1 - Master Field: SK
Child Field: TrainingMasterSK
The second subform has a master-child link to the first subform.
subform2 - Master Field: subTrainingModule.Form!TrainingModuleTopicSK
Child Field: TrainingModuleTopicSK
|
by: Richard Penfold |
last post by:
Can someone explain why a field on the many side of a one-to-many relationship, with referential integrity enforced, is not automatically updating?
I have 5 tables in my database with the following relationships:
Transactions (1-Many) Transaction Details (1-Many) Deliveries
Order Codes (1-Many) Transaction Details
Order Codes (1-Many) ...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |