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  1881  Rabbit 12,516
Recognized Expert Moderator MVP
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
Rabbit 12,516
Recognized Expert Moderator MVP
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
Rabbit 12,516
Recognized Expert Moderator MVP
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?
Rabbit 12,516
Recognized Expert Moderator MVP
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])
-
Rabbit 12,516
Recognized Expert Moderator MVP
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.For m.ControlName
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?
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 statement and comparing the $_POST vars to
the returned values, and only UPDATING those that have...
|
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 project that requires updating data every month.
A typical examle is an apartment rental software but could be applied
|
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, Price, Weight etc...
Table2 has the following: Product_ID, Category, Department
I need to add a new product into Table1 and automatically have the
product_id added to Table2.
|
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 no longer manage the plan the date
this occurs gets entered into a field. What I would like to happen is for
all the fields in that record to change their forecolor to red as soon as
the DateLost field is no longer null.
I also have several...
|
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 running at once will there be problems
with data updates?
The reason I ask is I'm thinking like this:
User1 launches the app and the dataset is created from the data in the DB.
| | |
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 scans the database to find out where to change it. Basic concept is form is populated from an object from mysql tables, user changes fields (usually few - not for creating new accounts), $_POST is compared against an array/object with current field...
|
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 the current date, and what I am trying to accomplish is if this date changes, then the second field, Tomorrow, will automatically become equivalant to the value of Today +1. Sounds simple enough, however I cannot get the table to update. I am able 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 selects a date for a javascript calendar solution.
Introduction
The reason I don’t want to use a text field, is because the website I’m developing is in Spanish and as you probably know, the Spanish format for the dates is dd/mm/yy, but I just...
|
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. In this table there are multiple columns that
cannot be NULL, which, are bound to other controls (but they're not
really important at this time).
I create a new row via the currency manager like so:
_currencyManager.AddNew()
_currentRow =...
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
