Forms :: Getting Sum Of Calculated Field In Subform Footer
Nov 9, 2014
I've got a subform (simple table view) inside a main form, the subform (sfrmRecipes) just has an IngID field (which stores ingredient ID's) and a percentage field
I've added a few calculated fields to this subform, to assist users in making decisions, however each of these fields needs a sum total
So far my percentage field (which is an actual stored field in tblRecipes) is the only one with a functioning sum
for some reason the two calculated fields (which are both plain numbers, no text) do not give me a sum total option, i am only able to use the count function on them
Why calculated fields dont sum up and if there is an easy way to get it working (tried creating my own text field in the footer and manually typing SUM(fieldname), but it does not work).
View Replies
ADVERTISEMENT
Mar 12, 2014
I have a form with three fields named 'quantity' (numberfield), 'service' (combo box) and 'amount' (calculated field with controlsource =[service].[Column](2)*[quantity].
I try to sum the amount field in the formfooter with =Sum([service].[Column](2)*[quantity]) but I receive an error.
Somehow it does not get the value for [service].[Column](2)
How can I do this?
The table for the form is called customer_detail
View 2 Replies
View Related
Apr 6, 2013
I have a form that is displayed continuously and there is a text field in it (Text12).In the footer of this form I would like to add a textbox that is the sum of all Text12's on the form.I tried =Sum([Text12]) in the textboxes control source but that is just displaying an error when I run the form.
View 2 Replies
View Related
Feb 8, 2014
I'm trying to sum a couple of columns from a subform datasheet and carry them onto the main form.
My main form, [frm_tours_details], holds subform [sbfTours] which is a datasheet.
On [sbfTours], I've got a couple calculated fields within the datasheet, one, called "txtStudentSum", which calculates number of people total ([students]+[adults]+[numfreeteachers]) and one, called "txtptotal", which calculates fee for the program based on the following formula:
Code:
=IIf([txtMonth]>=10 Or [txtMonth]<=4,([Students]+[Adults])*7,([Students]+[Adults])*8)
(The fee is $8/person in season, and $7/person out of season.)
I've been following online directions I found to put a text box in the form footer which then sums each of these calculated fields ("txtStudentSum" & "txtptotal"), and then reference these text boxes from my main form.
However, the main form text boxes just come up as #Name? or #Error when I try to reference these boxes on the subform. Or, sometimes, the one calculating total students will show the total of ALL students, adults in the database, not just those for this particular record-but only, it seems, if I have the subform open separately first, then load the main form. I also tried putting the whole original calculations into text boxes on the main form, but they will only reflect either the current record selected in the subform, or they will only show the first record, regardless of what you've selected, depending on which way I tried.
I've attached a copy of the database ... click on the tab 'tours', then click on, say, tour #6 (since this one is multiple programs and needs to calculate) to open the tour details form.
View 1 Replies
View Related
Aug 21, 2013
I have a subform embedded in a main form, the subform has a field (text box) that is doing automatic calculation so no manual entries are needed from the user. I have tried to pass automatically the final result to a text box in the main form, but all events are not working (afterupdate, On change, On Dirty), etc.
I have a button to do the above mentioned in the main form, but I don’t want to use that button, I want to do all the process from the subform.
Also, from the subform I was able to do click on the button on the main form (programmatically), however MS Access is too quick and the value extracted from the subform is always empty (zero); Acees is finishing before the calculation is done.
I am using MS Access 2010.
View 4 Replies
View Related
Apr 3, 2014
I have a "Main" form called frmProjects that sports multiple tabs. One of those tabs is labelled "Milestones", into which I inserted a datasheet subform called frmSubMilestones. The table that feeds the subform is tblMilestones. I established the parent / child relationship between frmProject and frmSubMilestones, and everything is working just fine...
To summarize: frmProject as main form -> Milestones Tab on main form -> frmSubMilestones as datasheet subform on the Milestones Tab (there are other tabs that are not subforms).
I have been asked, for each milestone in the frmSubMilestones subform's datasheet, to capture the number of days spent by employees on a monthly basis. The Milestones table and the PersonDays table have a primary key and a foreign key that are similar.
To summarize: frmSubMilestones as subform -> txtAggregatePersonDays as calculated Textbox fetching data from tblPersonDays -> OnClick event -> FormLoad of frmPersonDays as datasheet PopUp form.
DoCmd.FormLoad is supplied with the usual parameters to make the PopUp form appear, filtered on the currently highlighted milestone. Everything works fine so far... well almost.
Problem 1: When I introduce new rows in the frmPersonsDays datasheet, everything seems fine at the surface, BUT the primary key of the calling form (i.e. frmSubMilestones) does not appear in the tblPersonDays table; this is normal because there is no form-based parent / child relationship between the two forms. As one might expect, I am trying, from frmPersonDays, to get the primary key value from the "parent" frmSubMilestones using the Forms collection. No dice. I surfed and surfed, tried the bang and the dot operators, drank scotch, but nothing worked. I had to add foreing keys manually in tblPersonDays to further my tests. I can't figure out how to reach any control on the calling form, which is a subform.
Problem 2: The calculated txtAggregatePersonDays on the frmSubMilestones works wonders for existing data in tblPersonDays. However, when I introduce new rows in the PopUp form, I also realized that the calculated Textbox in frmSubMilestones is not updated when I close the frmPersonDays PopUp. I have to close the PopUp form and move the cursor in the Main form (frmProjects) to refresh its associated milestones.
View 3 Replies
View Related
Jan 24, 2015
I have a problem with calculating sum of fields in the footer of subform
The subform consists of following 5 fields
Opis kolicina txtCena rabat CenaSpop
Opis is combobox (cbododatki), which takes values from another table (tbldodatki)
SELECT DISTINCT [IDdodatki], [Opis], [Cena] FROM tbldodatki ORDER BY [Opis];
Kolicina is enetered manually
Rabat is entered manually
Cena is calculated with =cbododatki.column(2)
Cenaspop is a calculation of [kolicina]*[txtCena]*(1-[rabat])
In such case i cant get Access to calculate the Sum in the footer of the subform
On the other hand, if i add an additional column to the source table (tblPodrobNaroc) of this subform (frmPodrobNaroc) where i enter Cena manually (instead of only being part of cbododatki), then all calculations are fine.
Which option do you reccomend, should i:
a) add to the source table of subform (tblPodrobNaroc) another column with values of Cena from tbldodatki (how is that done?), which would be filled in through cbododatki, and then perform needed calculations
b) call Cena in subform in such way that the Access recognizes it (not as cbododatki.column type) and performs the calculation.
View 14 Replies
View Related
Apr 15, 2015
I have two forms (attendance & member details). I put the attendance form in the member details form as a subform. My problem is that my attendance form has buttons in the footer that I want to keep (and need), but I don't want them showing up in the subform
View 2 Replies
View Related
Mar 23, 2014
I have a report and in the footer I have added a text box (Textbox136) to work out the average percentage of the field [Percentage], which works fine.
For the value in textbox136 I want to output a grade and want to use an IIF function in the control source, something like:
IIf([Textbox136]>=100,"A+",IIf([Textbox136]>=90,"A+",IIf([Textbox136]>=80,"A",IIf([Textbox136]>=70,"B",
IIf([Textbox136]>=60,"C",IIf([Textbox136]>=50,"D",IIf([Textbox136]>=40,"E",IIf([Textbox136]>=30,"E-","U"))))))))
But it won't work.
View 1 Replies
View Related
Dec 10, 2014
I have a Report that has calculated fields in the details section. I want to total those fields in the Report Footer. When I run the report I am prompted to enter the value for the calculated fields and the Totals do not appear. Access must be inspecting the report before it actually runs. And so, since the calculated fields do not exist until the report is run then Access doesnt find them during the inspection and hence prompts for input.
View 2 Replies
View Related
Oct 22, 2013
On a form I have the following field:
Name: PaymentsMade
Control Source: =DLookUp("SumOfPrePaymentValue","qryInvoicePrepaym entSum","InvoiceId = " & [InvoiceId])
In the form's footer I have the unbound text control:
Name: SumOfPaymentsMade
Control Source: =Sum([PaymentsMade])
SumOfPaymentsMade returns #Error
How do I get it to return the sum of PaymentsMade?
View 4 Replies
View Related
Aug 17, 2015
I'm trying to set up a sum field in the footer of a continuous forms form but I'm stuck on the code. The field is amount and I need the sum field to show the total of all the records on the form.
View 5 Replies
View Related
Nov 14, 2013
I have an invoice form named frmInvoice. It contains a subform named subInvDetails. I am trying unsuccessfully to transfer a calculated subtotal from a text box in subInvDetails to a total in a text box in frmInvoice. Is there a way to do this? I have tried everything I can think of but I can't seem to make it happen.
To be more precise, I have a textbox which is a calculated value ([Rate]*[Hours]) named txtSubTotal in the sub form. In the main form I have a field named txtTotal. I thought that setting the Default Value of txtTotal to =Sum([Forms]![subInvDetail]![Subtotal]) was the right way to it, but the value of txtTotal remained zero. I have also tried using =Sum([Forms]![subInvDetail]![Subtotal]) as txtTotal's Control Source, but that didn't fit my needs or seem like the right way to do it. It didn't work anyway.
View 3 Replies
View Related
Jul 5, 2015
Eventhough i know calculated control are for queries, I still need to store a calculated value of a sum. I have a form with customers, dates, locations. That form has a subform that contains the services done and materials used. This subform is in datasheet view. In the footer i have a control with =Sum([Price]).
So I need the value of this control to be stored in the table the main form is based on. I did an unbound control in the main form where the =Me!Subform.Form!.Textx will bring the value "over" Then for the on focus event the vba code Me.Total=Me.Textx stores the value. That is fine. The same works with a command button with the same vba code.
However i am asking if there is a more automatic way to get the value over without the unbound control/command button.
View 7 Replies
View Related
Oct 20, 2014
What I'm trying to do: I have created an unbound field within a subform's footer to calculate the average of the displayed record values. The subform is in datasheet view. The records are returned based on a query with a relationship between two tables. I need the calculated data (which I currently have displayed on the main form) to populate within the master table.
I can't figure out how to automatically do this. I created a simple command to get it there, but I'd like the user not to need a button to display a calculation.
Main form based on TableA; subform based on TableB.
Here's how I have it set up (Btw, I suck at SQL I just figure this is easiest to read):
Query SQL looks like this: SELECT tblB.Field1, tblB.Field2 FROM tblB.Field1 INNER JOIN tblA ON tblB.Field1 = tblA.Field1
Main form: unbound txtbox = [Forms]![frmA]![subfrmA].Form.[txtAvg]
The field that is averaged is tblB.Field2. There are potentially a bunch of records displayed in the subform.
I need that [txtAvg] field to save to TableA. My command button is simply Me.tblA.FieldName = Me.unboundtxtbox. I'd like to do away with that.
I tried to use the on current or on load event for the main form; but the calculated field value is 0 until it calculates it. It seems like there is a short delay before the value shows up on the form at which point the on current or on load events don't pick up the calculated value; just the 0 that is initially there.
View 5 Replies
View Related
Jan 16, 2014
The first report example is what I get when I start on the main form and select an organization, run the report, opens report - BIG SPACE between the details and my footer section which holds a subform.
The second report is what I need it to look like, what's strange to me is I get the report correctly when I just run the report (I don't pass the organization into the report from the main form)
I tried changing the background color of the detail section and the footer section to figure out where my problem is but the space just stays white.
View 7 Replies
View Related
Jul 13, 2015
My form is a datasheet with 2 fields: "Product" and "Quantity". Obviously I can't add a calculated field in this type of form; however I would like the word " Total" to display in the "Quantity" field just under the last record. I would also like the sum of the "Quantity" field to display at the bottom of the "Quantity" field in the empty record below the last record.
View 14 Replies
View Related
Feb 18, 2015
I have a simple access database which up until now was working as it should. Then i made a Backup of the database Named it something different deleted certain data from the backup and when i went back into the original DB i now have #Name? where it used to calculate 2 fields.
View 1 Replies
View Related
Mar 14, 2014
I have a subform which lists a set of records. In the footer section of the subform, I have a number of controls which display calculations based on the records present.
The number of records in the subform will change (reduce). Basically, this is a review / approval function, the idea being that each record will be checked and approved, which will then remove it from the list.
So eventually, all of the records in the subform should disappear (the subform is requeried each time the user approves a record)
I want to be able to hide the controls in the footer section once the subform has been cleared of all records.
But not sure
a) how to determine when the subform's recordset reaches zero and
b) what event to use to fire the code to hide the footer controls.
This is my basic attempt :
Code:
Private Sub Form_Current()Dim ctl As Control
For Each ctl In Me.FormFooter.Controls
ctl.Visible = Not (Me.Recordset.EOF)
Next ctl
End Sub
But it doesn't work (it only seems to fire when the subform is loaded, not when it is requeried after each approval?)
I've tried Form_Query, Form_DataChange and Form_DataSetChange but no joy with those either.
View 6 Replies
View Related
Sep 15, 2013
I am struggling with the DateDiff fuction in a calculated field. After some study, I have used several variations of:
=(DateDiff("d",[DUED],Date())/7)
to calculate the time (in weeks) until a due date starting from today's date.
It continually turns up a specific date in the late 18 and early 1900's. It does not give me the number in weeks which was what I was hoping for.
Earlier I used a very simple: =DateDiff("m",[Birthdate],Date()) to calculate the age from a date of birth perfectly with no problems.
I do not understand why the formula is not working. Does it have to do with format or the fact that I am working with a future date and how can I rectify this problem?
View 4 Replies
View Related
May 31, 2013
I have a main form with several with four subforms. On the main form I have a bound text box [ShiftTotal] I have on each subform a unbound text box that sums a field [ExtTaxIn]. I am working with just one subform till I can get it to work correctly. The code I am using on the after update event is as follows.
code
Private Sub TxtSoldQty_AfterUpdate()
On Error GoTo HandleError
Me.ExtTaxIn = (Me.TxtSoldQty * Me.TaxIn)
Me.ExtPrice = (Me.TxtSoldQty * Me.Price)
Me.InvSold = (Me.TxtSoldQty * Me.UnitOfSale)
Forms!frmShiftMain!TxtShiftTotal = Forms!frmShiftMain!TxtRunningTotal
Me.txtExtTaxIn.Requery
[code]...
This sort of works but the [ShiftTotal] on the main form is always one table row behind. I think this is because of the order the event fires.
View 2 Replies
View Related
Jan 14, 2015
My form has a button that has a calculated field on it (a dcount(...)). When the button is clicked, a couple of conditions are checked and if all is ok, it runs the vba code. If the tests fail, it returns to the form without running the code. All works correctly, except that the dcount number has disappeared. I finally figured out that it is now hidden behind the button. How do I bring it forward again programmatically?
View 11 Replies
View Related
Apr 13, 2013
A textbox on a form concatenates 2 strings. I want to insert that resulting string into a table .how can i do that ?
View 1 Replies
View Related
Sep 4, 2014
I have a form called frm002_PAF_MonitoringMAIN and on this form I have a subform called frm002_PAF_Monitoring. The subform has a button to another form for contact details called Contact_Details. The Contact_Details form opens with the details of the person who I have selected on frm002_PAF_Monitoring. There are some fields on frm002_PAF_Monitoring that I would like to be populated on Contact_Details when opened, like FirstName and LastName. How can I do it?I have tried on afterupdate event on Contact_Details below but those don't work:
Me!FirstName=[Forms]![frm002_PAF_Monitoring]![FirstName]
Me!FirstName=[Forms]![frm002_PAF_MonitoringMAIN]![frm002_PAF_Monitoring]![FirstName]
Me![FirstName].Value=[Forms]![frm002_PAF_Monitoring]![FirstName]
Me![FirstName].Value=[Forms]![frm002_PAF_MonitoringMAIN]![frm002_PAF_Monitoring]![FirstName]
View 11 Replies
View Related
Oct 16, 2014
I have an unbound calculated field [Txt1] that I would like to show the result as currency. The fields in the calculation are:
[CR] number, currency
[Commission1] general number, two decimal places
[Commission2] general number, two decimal places
The calculation is: CR15W * (1 - Commission1) * (1 - Commission2)
If I just set the field to currency it works fine. But I want the field to show the following:
"Some Text" & " " & Format(Calculation, "Currency")
When I tried to include the format it to currency I get type mismatch, run time error 13.
So I thought that perhaps I should try the following just to see where my error starts and I get the same error message when I use:
Dim Val1 As String
Val1 = CR15W * (1 - Commission1) * (1 - Commission2)
Txt1 = Format(Val1, "Currency")
Everything I have read says the formula is:
Format([Calculation], "Currency")
Is correct. So why won't it work for me?
View 8 Replies
View Related
Sep 18, 2014
We have a recent issue on client PC. After application running for a while, all the sudden the calculated field on the form not showing the details, though there is data on the control. After restart the PC, the calculated field display correctly. We guess this may be due to the theme we use.
View 3 Replies
View Related
