473,327 Members | 1,920 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

How to get the sum of a textbox value in sub-form, based upon the status?

Dear All

I have a Master-detail form which depends upon master-detail tables. Master form have the following fields:
MstID, MstTotalPrice and the detail form contains the following fielsds:
DtlID, DtlPrice, DtlStatus

I want to put the total price of all the records(Sum(DtlPrice)) in detail form into MstTotalPrice for the records having DtlStatus.value="Approved"

How can I?
Aug 21 '07 #1
5 3188
FishVal
2,653 Expert 2GB
Dear All

I have a Master-detail form which depends upon master-detail tables. Master form have the following fields:
MstID, MstTotalPrice and the detail form contains the following fielsds:
DtlID, DtlPrice, DtlStatus

I want to put the total price of all the records(Sum(DtlPrice)) in detail form into MstTotalPrice for the records having DtlStatus.value="Approved"

How can I?
Hi, there.

You can put a Textbox with "DSum" function as ControlSource to the Master-form. See "DSum" syntax and examples in Access help.
Aug 21 '07 #2
Dear Fish
Thanks for response. I used the said function as under:
Expand|Select|Wrap|Line Numbers
  1. =DSum("[FrmDtlRecomm]![Price]","QryPriceStatistics","[FrmDtlRecomm]![Status] = 'Recommended'")
  2.  
as the record source for the text box at master form. I found the value 18, which is wrong. According to this situation the correct value is 9. QryPriceStatistics is as under:
Expand|Select|Wrap|Line Numbers
  1. SELECT DtlSuggestions.STATUS, DtlSuggestions.PRICE, DtlSuggestions.PKRPrice, DtlSuggestions.Discount
  2. FROM MstSuggestions INNER JOIN DtlSuggestions ON MstSuggestions.SuggId=DtlSuggestions.SuggId
  3. WHERE (((MstSuggestions.SuggId)=Forms!FrmMstRecommendation!SuggId));
  4.  
  5.  
Also the value appears when i navigate between two records on master form
Any idea?
Aug 27 '07 #3
FishVal
2,653 Expert 2GB
Dear Fish
Thanks for response. I used the said function as under:
Expand|Select|Wrap|Line Numbers
  1. =DSum("[FrmDtlRecomm]![Price]","QryPriceStatistics","[FrmDtlRecomm]![Status] = 'Recommended'")
  2.  
as the record source for the text box at master form. I found the value 18, which is wrong. According to this situation the correct value is 9. QryPriceStatistics is as under:
Expand|Select|Wrap|Line Numbers
  1. SELECT DtlSuggestions.STATUS, DtlSuggestions.PRICE, DtlSuggestions.PKRPrice, DtlSuggestions.Discount
  2. FROM MstSuggestions INNER JOIN DtlSuggestions ON MstSuggestions.SuggId=DtlSuggestions.SuggId
  3. WHERE (((MstSuggestions.SuggId)=Forms!FrmMstRecommendation!SuggId));
  4.  
  5.  
Also the value appears when i navigate between two records on master form
Any idea?
Hi, there.

I'm very surprised it return any value at all.
In general DAggregate functions supposed to work with tables/queries, not with forms.

Try the following:
Expand|Select|Wrap|Line Numbers
  1. =DSum("[QryPriceStatistics]![Price]","QryPriceStatistics","[QryPriceStatistics]![Status] = 'Recommended'")
  2.  
Aug 27 '07 #4
Dear Fishwal

Thanks. Now it is working fine.

Regards,
Ata
Aug 28 '07 #5
FishVal
2,653 Expert 2GB
Dear Fishwal

Thanks. Now it is working fine.

Regards,
Ata
Nice. I'm glad its now working.

Regards,
Valentine
Aug 28 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

11
by: Joe | last post by:
Hello All, I have an ASP.NET page with one Textbox (SearchTextBox) and one ImageButton (SearchButton) server controls. The user can type search text in SearchTextBox and click SearchButton and...
4
by: Agnes | last post by:
Before I run a function, i will put the string in a textbox. However, I found that the messagebox show the correct value. BUT the textbox show nothing. Finally, after the function 's process is...
7
by: DazedAndConfused | last post by:
Curently I manualy code keypress edits. i.e. allow only 3 digits before decimal and two digits after decimal in .NET. Is there an easy solution to mask text boxes? The MSMASK32.OCX from vb6...
2
by: simon | last post by:
hello, new to vb.net, have a few questions about DataGrid. I have a dataGrid that is working pulling a dataset back from a stored proc and binding to the datagrid for display the datagrid's...
4
by: Dabbler | last post by:
Is there a way to mark the text in a TextBox control as selected so when the user types a new value the existing text is replaced? Thanks
16
by: mj.redfox.mj | last post by:
Can anyone help? I have a textbox which I'm programatically adding by using the following code: txtTest = New TextBox txtTest.ID = "txtLeft" + cntCount.ToString...
4
by: slinky | last post by:
I have a textbox that a user enters the current time (in this case at the end of a task, and it gets its value via a button's click event and getting its value from =Now() ). Is there a way to...
4
by: Dean Slindee | last post by:
Anyone got a quick and easy way to change the background color on a textbox after the user has change the text value. Already have lots of forms written, so an approach that does not depend on...
3
by: =?Utf-8?B?a2VubnltY2U=?= | last post by:
Hi, I'm probably being somewhat dim here and missing something obvious, but I'm trying to dynamically create a text box and then retrieve its value after postback. Partial Public Class...
1
by: JFKJr | last post by:
Hello everyone, the following Access VBA code opens an excel file and creates textboxes in a given range of cells dynamically. The code attaches "MouseUP" and "Exit" events to the textboxes (using...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.