Hello all,
I searched the forums as much as I can and haven't found a viable solution.
I have a subform that filters the records by Publisher.
In the subform I have a calculated control called "% of Track Commission", that takes the subform control "# of Writers" and divides it by the subform control "# of Writers for Track".
From this I have another control called "TotalTrackComm ission" that then takes "% of Track commission" and multiplies it with "Commission Per Track."
All of this works fine.
The problem arises when I try to put a text box control in the subform footer to sum the "TotalTrackComm ission" text box control for the current records shown.
Currently I'm trying to use =Sum([TotalTrackCommi ssion]), but this only returns #Error. If I use =([TotalTrackCommi ssion]) it does return a value, but obviously only for one of the values, not the sum of them. I know my problem probably lies in the fact that I'm trying to sum a calculated control (which is calculated off another calculated control LOL), but I just don't know the avenues I should be trying to take.
So I would appreciate any insight, whether I'm just referencing it wrong, or if I should somehow utilize a query, etc... My ultimate goal of course is to have a text box control on the main form pull the sum from this footer control.
Thanks for you time!
As a side note, is there a way to have another text box control "CommissionPerT rack" in the subform, take it's value from a control in the main form, or from a table unrelated to the one used by the subform? I tried adding the other table to the subforms record source so I could utilize it, but even though that works, it locks the controls for publisher where I can't change anything in the control boxes anymore. Right now I just set the control to a value of .091, but I would rather it pull this from the main form (or table) as this value is subject to change, and I won't be the one ultimately using the database, so this value needs to be changed easily. The form I'm trying to pull from is frmAlbumCommiss ion, and the control is "Commission Per Track", or the table possibility is tblAlbumCommiss ions, and the field is "CommissionPerT rack". I know the names could be differentiated better, but it is what it is you know.
Again I appreciate any help.
3 4895 nico5038 3,080
Recognized Expert Specialist
You can't use "calculated controls" to sum, use instead the sum of the calculation like: -
=sum([# of Writers"] / [# of Writers for Track])
-
I would also recommend to use no special characters and/or spaces in your field names.
Use e.g. [NumOfWriters], [NumOfWritersFor Track], etc.
Nic;o)
Hey Nico, I really appreciate your response to my problem. And I agree with you that I should lose the special characters and spaces. I'm proficient in access and i'm slowly losing the bad habits lol.
I had actually tried using that solution before I posted but for some reason I still couldn't get it to work (lol could be user error?).
But that did ultimately solve my problem. I really appreciate it. I then ended up using another calculated control in the footer that multiplied this result by another text box in the main form to get another sum result that I couldn't reference directly (another calculated field in the subform), but was able to work around that problem using this route.
Here's hoping there's always a work around to any problem we encounter.
Thanks again!
nico5038 3,080
Recognized Expert Specialist
I sometimes add the calculation to the query and use it in the footer's SUM().
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Lyn |
last post by:
Hi,
I have a form which contains a number of subforms in different pages of a
tab control. In the detail section of each subform, I list records related
to the main form record, but from different tables. In the footer section
of the subform I have a Notes text box and three buttons (Add, Update,
Delete). The latter each open a separate form for updating or deleting the
subform data (the records cannot be updated or deleted directly...
|
by: phaddock4 |
last post by:
Being fairly inexperienced at Access 2000, i've been reading many posts
here for the last several days, and testing myself to find the best
approach to do the following in A2K:
SET UP:
I have created a MainTable (and related form), which has an associated
SubForm (popup) along with its underlying, separate Table.
The tables' relationship is one to many respectively. The primary key
|
by: Bruce |
last post by:
Hi,
I have 3 nested forms and wish to calculate a value on Subform 1 by
adding 2 values on the same subform + to a value in a
control on the footer of the underlying subform
. I use this as the recordsource for my total
=.Form!Sumpd++)
which works well except when there are no records in the
transactiontbl subform - I get an error.
|
by: musicloverlch |
last post by:
I have a form with a subform that has a subform that has a subform that
has a subform. The 5th embedded subform never opens in Access 97 and I
get a message that 'a form has caused 1 error.' When I open the form in
Access 2k, it's fine. The users are using Access 97. What do I have
to do to get it to cooperate??
Thanks in advance,
Laura
|
by: Shannan Casteel via AccessMonster.com |
last post by:
I have a subform named "sbfrmParts" with a list of parts along with the
quantity and price. I have used a text box in the subform's footer and set
the control source to "=Sum(*)". I set the format to
display currency. This text box is called "SubformTotal", and is visible
property is set to "No". On the main form I have made another text box and
set its control source to "=.Form!SubformTotal".
When I enter some parts everything works...
| |
by: Anthony England |
last post by:
I wonder if anyone would have 5 minutes to check this for me. Access XP
with new database created:
Simple table tblTest: ID=autonumber, F1=Text with a few sample records
frmSub has this as its recordsource and is set to datasheet view
frmMain is unbound and has a subform control named 'sbfSub1' with 'frmSub'
as the source object which is placed in the form footer. It has a button on
the detail section with the following code:
MsgBox...
|
by: Kitana907 |
last post by:
Hi-
I am having issues with adding up fields in a subform. I have a form that has a subform in datasheet mode. It lists titles of books with their correlating units and dollar amount .
The user will be able to delete records from this subform. I am trying to add in text boxes that list the sum of oh, the sum of extretail, and the count of titles.
I have tried it the three ways below with no luck... (I'll give examples with the OH...
|
by: post |
last post by:
Sorry that this is rather basic. But I have tried the various
suggestions given in this group and cannot get them to work.
I have a main form, for simplicity called "Main". On it are two
subforms. For simplicity subform1 and subform2. subform1 lists fees
for items. The sum of all fees for all items is in a text box on the
footer of subform1. PurSum =Sum(). subform2 contains exactly
the same field, fees for items of a different...
|
by: AccessIdiot |
last post by:
This is driving me nuts.
I have an unbound textbox (txt_SumSize) in my subform that holds the total of a field (Debitage_Size_Quantity)seen in datasheet view (control source =Sum().
I have an unbound textbox on my form (txt_SizeTotal) that gets the value from the subform footer when the form loads:
Private Sub Form_Load()
Me.txt_SizeTotal = Me!sbfrm_DEBITAGE_Size.Form!
End Sub
|
by: csolomon |
last post by:
Hello:
I would like to use information on one form to populate another form. There are 2 controls I would like to transfer; a list box control and a text box control. both are unbound controls in the subform footer of the form F_MixDesign.
Form1: F_MixDesign
Subform1: SF_MixSample
Control name: txtWaterReqWt
Form2:FSampleRequest
Subform2: SF_SampleRequest
Control name: txtWater
|
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: 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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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: 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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |