 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / April 2005Inserting calculation field on form
Hi All, I have created a form called frm_GrantPot with tbl_GrantPot as the record source. The form consists of the following fields: GrantPotNumber, GrantPotName, AvailableGrant, TotalGrantRemaining (calculated field with the following expression; =[AvailableGrant]-[frm_GrantApplicant subform].Form!AmountGrantAllocated The main form also consists of a subform with tbl_GrantApplicant as the record source and consists of the following fields: GrantApplicantNumber, GrantStatus (yes/no data type), DateGrantAllocated, GrantRecipient, ReasonGrantAllocated, AmountGrantAllocated, GrantSpendProgress, GrantConditions, GrantPotNumber (Foreign Key) For the 'TotalGrantRemaining' field on the main form I wish to calculate the following for each grantpot record: TotalGrantRemaining (on main form) = AvailableGrant (on main form) - AmountGrantAllocated (for each record in subform) where the 'GrantStatus' for each record in the subform is = yes (ticked). I hope that makes sense, please do not hesitate to ask more me for further clarification. Cheers. Kind Regards AMMO In the control source of the TotalGrantRemaining field on main form enter this =[AvailableGrant] - DSum("[AmountGrantAllocated]","TableorQuery","[GrantPotNumber] = " & Me.GrantPotNumber & " And [GrantStatus] = True") 'TableOrQuery' is the underlying source for your subform > Hi All, > [quoted text clipped - 30 lines] > > AMMO Hi Dennis, When I copy the expression you created into the 'TotalGrantRemaining' control source, I just get a #Name? appear in the field in normal view. I tried replacing the "TableorQuery" part of the expression with the name of the table that the subform is based on, but still not success. Do I need to do anything else with the expressiob? Regards Ammo >-----Original Message----- >In the control source of the TotalGrantRemaining field on main form enter this >=[AvailableGrant] - >DSum("[AmountGrantAllocated]","TableorQuery","[GrantPotNumber] = " & >Me.GrantPotNumber & " And [GrantStatus] = True") > [quoted text clipped - 36 lines] >> >. > Hi All, > [quoted text clipped - 30 lines] > > AMMO Try this (on a copy of your database!): Create a new QUERY. Add the table "tbl_GrantApplicant", then drag down to the grid fields "AmountGrantAllocated", "GrantPotNumber" and "GrantStatus". In the Criteria row of GrantStatus enter True. Make the query a totals query (click on the funny "E" in the toolbar). In the Totals row for the field "AmountGrantAllocated", change "Group By" to "Sum". Save this query as "qryTotalAllocatedGrant" (without the quotes). Create another new QUERY. Add table "tbl_GrantPot" and query "qryTotalAllocatedGrant". Join the two on "GrantPotNumber". Double click on the join line and select join type 2. Add the fields from "tbl_GrantPot" to the grid, then in the "Field" row of an empty column paste: GrantRemain: [AvailableGrant]-[SumOfAmountGrantAllocated] Save this query as "qryGrantPot". Create a new FORM. Set the Record Source to "qryGrantPot". Add the fields to the detail section and set the Default View to "Continuous Forms". If you want to see the amount of allocated grants, add "SumOfAmountGrantAllocated" to the grid for the query "qryGrantPot", then to the form. --- SteveS -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) Hi Steve, Thank you for your time to help me with this, but still no joy I am afraid :( I basically want to be able to input new records into the sub form (grant applicant details) for each grant pot (main form), with the 'TotalGrantRemaining' field on the main form being calculated each time new record is added with Grant Status = yes (ticked) Preferably I don't want to change my current form design, so looking for some form coding to do the job. Thanks for your time though. Anyone else have any more suggestions? Regards Ammo >-----Original Message----- > [quoted text clipped - 71 lines] > >. > Hi Steve, > [quoted text clipped - 12 lines] > > Ammo You don't have to change your form design. My example was just that - an example of how to use a calculation in a query. Your form (the way I *almost* always do it) should have a query as its Record Source. If you want, delete most of your data (and ALL of the sensitive data), compact it, zip it and sent it to me. I'm using A2K. --- SteveS -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) Hi Steve, I have emailed you the db as requested. Kind Regards Ammo >-----Original Message----- > [quoted text clipped - 27 lines] >(I came; I saw; I stuck around.) >. > Hi Steve, > [quoted text clipped - 3 lines] > > Ammo I received your database. After looking at it, I was still puzzled as to why DSUM() wasn't working. So before I created the new queries, I took another look at your formula for "TotalGrantRemaining". There I was, looking at the form, thinking about creating the queries when it happened!! Kind of like the guy that stayed up all night wondering where the sun went when it went down, when it finally dawned on him! <g> The DSUM() function is nothing more than a really, really limited query. You have a source (the table), a field to search and a where clause (without the where). And in a query, you *can't* use "Me.ControlName". You *have* to use "Forms!FormName.ControlName". Here is the new control source for "TotalGrantRemaining". (I rearranged the terms) =[AvailableGrant]-DSum("[AmountGrantAllocated]","tbl_GrantApplicant","[GrantStatus] = True and [GrantPotNumber] = " & Forms!frm_GrantPot.GrantPotNumber) Then, to get the "TotalGrantRemaining" to update when you change the subform, from the databasewindow, open "frm_GrantApplicant subform" in design view. In the FORM AfterUpdate event, enter: Me.Parent.Refresh It should look like this: Private Sub Form_AfterUpdate() Me.Parent.Refresh ' you might be able to to use instead ' Me.TotalGrantRemaining.Requery End Sub Two simple changes.... :-D It should work now...... --- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) Oops... That should have been: ' Me.TotalGrantRemaining.Refresh .Requery will reset the current record to the top of the recordset...not good... --- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) Hi Steve, Thank you for all your help and especially the time you spent on this. Yes, was going to mention about it should be. refresh instead of .requery. Can't believe it was the smallest of things as well, suppose that is the way of the world, its always the little things. Anyways thanks again. Best Wishes Amarjeet >-----Original Message----- >Oops... [quoted text clipped - 12 lines] >(I came; I saw; I stuck around.) >. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.