Hi everyone, I'm having an issue with getting my fields to update when i change records. I have a main form and a subform. The main form has "total", "deposit", and "due" text boxes. The subform has the items, and the item total(# of items*price).
I currently have an update query that calculates the Item total. And in the form I have an OnUpdate event that takes the (Total price for the order) - (any deposit) = to ammount due.
This all works great, accept for the fact if you go to a record with a different order number the total, deposit, and due fields remain the same as they were with the previous record.
From what I understand its not good to store calculated fields within tables so I'm trying to do this within the form.
How can I get the fields to update automatically when i change records?
Thanks,
Scotter
11 1850
Set the control source to the calculation instead of doing it through events and code.
Well yeah, I guess that would make sence :)
Thanks,
Scotter
It's not as obvious as it sounds. Most people don't understand what control source means. Good luck.
Thanks. But now I have another part of the question. On the subform I want to do the same thing, instead of having an update query do the math I want the form to. But I cant get that to work. I keep getting #ERROR.
Here is my "Update to" line in my update query -
nz([items].[# of Chairs]*[Price_Chairs].[Price],0)+nz([Items].[# of Tables]*[Price_Tables].[Price],0)+nz([Price_Tents].[Price])+ nz([Price_Combo].[Price])+nz([# of Table Cloths]*[Price_Table Cloth].[Price])
-
How can I change that into a control source for my text box?
Scotter
Thanks. But now I have another part of the question. On the subform I want to do the same thing, instead of having an update query do the math I want the form to. But I cant get that to work. I keep getting #ERROR.
Here is my "Update to" line in my update query -
nz([items].[# of Chairs]*[Price_Chairs].[Price],0)+nz([Items].[# of Tables]*[Price_Tables].[Price],0)+nz([Price_Tents].[Price])+nz([Price_Combo].[Price])+nz([# of Table Cloths]*[Price_Table Cloth].[Price])
-
How can I change that into a control source for my text box?
Scotter
You can't reference tables using that method. When you have to get information from a table outside your form's record source, use DLookup() instead.
Thanks. But now I have another part of the question. On the subform I want to do the same thing, instead of having an update query do the math I want the form to. But I cant get that to work. I keep getting #ERROR.
Here is my "Update to" line in my update query -
nz([items].[# of Chairs]*[Price_Chairs].[Price],0)+nz([Items].[# of Tables]*[Price_Tables].[Price],0)+nz([Price_Tents].[Price])+nz([Price_Combo].[Price])+nz([# of Table Cloths]*[Price_Table Cloth].[Price])
-
How can I change that into a control source for my text box?
Scotter
Ok I figured that out, and it was pretty much what i had there but with an = sign.. Butttt since I'm not actually saving the totals, I cant add them from my main form, I'm assuming its because a subform is an object or something like that. Here is my control source for my Total Price on the main form -
=DSum("ItemCost","TestQuery2_subform","[Order Number] = " & [Order Number])
-
any ideas about how to get thoose values to my mainform without saving them?
Ok I figured that out, and it was pretty much what i had there but with an = sign.. Butttt since I'm not actually saving the totals, I cant add them from my main form, I'm assuming its because a subform is an object or something like that. Here is my control source for my Total Price on the main form -
=DSum("ItemCost","TestQuery2_subform","[Order Number] = " & [Order Number])
-
any ideas about how to get thoose values to my mainform without saving them?
I'm not sure what you mean. What values are you trying to get to the main form?
The values from my subform, the subform is made up of all the items within an order. I cant add the individual prices of each item to get my total. I'm trying to put a total in the footer, but I keep getting #ERROR. Heres what I have in there now -
=DSum("Item total","testQuery2 subform","[Order Number] = "& [Order Numer])
-
I still don't follow. Could you provide a layout of your tables, forms, and an example of what you're looking to for?
Aside from that, to reference something on a subform you use Forms!FormName!SubformName.Form.ControlName
Ok, I figured out a way for everything to work. Insted of calculating the individual item cost of each item within the form, I changed it so the item cost was calculated within the query the form was based off of.
Then in the form I set the item cost field's control source to the ItemCost field in the query. Also in the footer of the form I set a textboxe's control source to
DSum("ItemCost","QueryName","[Order Number] = " & [Order Number])
So I could get the total cost. Then on my main form I changed my "Total" textbox's control source to the textbox in the footer of my subform.
The subform is in datasheet view so you don't see the header and footer, but this way I could still pull from it. Honestly I don't know if this is the best way to do it, but it works for me so I'm happy
Thanks again Rabbit
Not a problem, good luck.
Sign in to post your reply or Sign up for a free account.
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?
...
|
by: Hasanain F. Esmail |
last post by:
Hi all,
I sincerly thank you all in advance for your help to solve this
problem.
I have been trying to find a solution to this problem for sometime now
but have failed.
I am working on a...
|
by: Support |
last post by:
Is it possible to add a record in one table and have Access automatically
add date from one of the fields into another table??
In more detail:
Table1 has the following fields: Product_ID,...
|
by: Christa Waggett |
last post by:
Hi, I'm not a programmer but would appreciate some help with the following.
I've been looking at various sites but cannot find the information I
require.
I have a table of strata plans and if we...
|
by: Tc |
last post by:
Hi,
I was curious, I am thinking of writing an application that loads a dataset
from a database that resides on a server. The question I have is this, if
multiple copies of the app will be...
|
by: oh4real |
last post by:
I recently developed a compact function to efficiently allow users to change info in a form (like account info, contact info, etc.) and then the function automatically identifies what's changed and...
|
by: Ciara9 |
last post by:
I am having problems trying to update a field in a database using a field in a form. I currently have two fields, Today and Tomorrow in a table named Date. The Today field automatically defaults to...
|
by: EyeHawk |
last post by:
OK, hopefully somebody can help me out again.
My next problem is updating 3 form fields (type list/menu option) that correspond to a date, one for month, one for day and one for year when the user...
|
by: Mike |
last post by:
So here's the situation (.NET 2.0 btw):
I have a form, and on this form is a textbox among many other
databound controls. The textbox is bound to a field in a data table
via the Text property. ...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
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...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |