Form And Two Subforms Calculations
Feb 5, 2006
I have a problem with main form and two subforms in it. Both subforms are in "continuos" mode, and each of them has field in footer, where it calculates total sum for all field that are in it.
Then there is main sum below both subforms (all this is in detail section of form) and it calculates total for both sums of each subform.
Whenever i fill values in first subform and in second subform, i have results everywhere, but if i fill values in first subform but leave second subform empty, the "main sum" below both subforms is empty also....
The control source for main sum field is "=Forms!frmMainForm!frmSubform1!Text1 + Forms!frmMainForm!frmSubform2!Text2" where Text1 is "total sum" in first subform and Text2 is "total sum" in second subform...
I set default value = 0 for all fields in both subforms, but it won't help....still shows empty fields in that subform, which isn't filled....Actually when i open form for the first time, it shows "0" values in all empty fields and then immediately clears them and shows empty fields...
Any ideas how i could solve it?
Thanks
View Replies
ADVERTISEMENT
Jan 21, 2005
Hi Everyone! Bear with me, I'm new to Access and I may not understand or use all the terminology correctly.
Background: I'm creating a database to track the inspections of many businesses. Each business will have many inspections throughout the year and each inspection has many items. I have a form set up which includes Business info and Inspection info (#items inspected, subtotal for inspection) on the main form, and Items in error listed on a subform (no error=no items listed).
Problem 1: I need to make a count of all items that are listed. I can do this in the subform, with no problem (in one of the footers #Discrep:count([item])) but now I need to report and use that value on the main form to calculate the percent error for the entire inspection. I don't know how to pull in onto the main form to set up my calculation. :confused:
Problem 2: This one is related and is probably the same fix. I have PriceCharged and PostedPrice for each item. I need to calculate the net error and then, using the subtotal on the main form, calculate the % dollar error.
Any suggestions, oh great and powerful gurus of the Access world? I hope I explained that clearly enough for you.
Thank you very much in advance!
~Lith
View 3 Replies
View Related
Sep 28, 2015
I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error;
and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
View 14 Replies
View Related
Feb 4, 2006
I forum could someone tell me:
I have an unbound form on that form I want to put three sub forms one on a products table the other on a course start dates table and the link table that joins the other two together. all three are related to each other with Pk/FK links.
When I try to link them it says you cannot link items on an unbound form.
regards in advance
Peter:eek:
View 1 Replies
View Related
Mar 31, 2005
Hello,
I am trying to creating a database for attendance issues for employees. I have the following text boxes on my form:
First Date Absent, Last Date Absent, Reason for time off, Disiplinary action, point value, comments
Our attendance policy requires certain disciplinary action be taken when the point values total a certain amount in a specified amount of time.
With all that said, I am wondering if I can create a text box on this form to calculate the point value within a 30 day period and a second text box to calculate the point value within a 12 month period. I can sum the total points, but I am unsure how to sum the points within the date ranges needed. Any ideas?
Thanks so much!!!
View 1 Replies
View Related
Sep 19, 2006
Dear All, i have a very simple database with only one table.
The table has three values [Start], [end] and [Total].
The [start] & [end] fileds are numerical.
I am trying to get it so that i can enter the values onto a form, the two values in [start] and [end] need to be added together to give the [total] value.
I can get it to do it on the form but i don't know how i can get the record in the table to be updated.
Does anyone know if this can be done and if so could you advise me where i'm going wrong.
Any help would be most appreciated.
View 5 Replies
View Related
Oct 16, 2006
I have created a form in Access to enter data into a Table. But one of the firelds I need to enter into the Table is calculated from some of the data being entered in the form. I currently use an Update Query to go back and do the calculation and then update the appropriate field. What I would like to do is to do the calculation and update the calculated field while the user is entering the data. (Boy I hope this is making sense!)
If it helps understand what I am trying to do here is an example: The user is entering data for a job we produced. Some of that data is NumberOfPages, PaperSize and JobType. But I also need to take those three pieces of info and calculate the number of square inches. Currently I run a function I wrote via an Update Query that updates the SquareInches field in the table. I was wondering if there was some way to do the calculation on the fly and enter the calculated value into the table while all the other data is being entered. I tried writing a little code to attach to one of the field's OnExit event but I seem to be having a problem getting the values from the fields I have already entered.
If anyone has any suggestions I would sure appreciate hearing them. Thanks!
View 7 Replies
View Related
Jun 27, 2013
performing calculations in a form in an Access 2007 database.The form is used to generate invoices and sales receipts. At the bottom of the form there are 3 text boxes: "Sub-total" (Text128), "VAT" (Text130) and "Total" (Text132). Each text box is bound to the Control Sources "Sub-Total", "VAT" and "Total" respectively in a table called "Orders".
I would like the "VAT" text box to automatically calculate the VAT on an order and enter it into the VAT field in the table "Orders". To do this I have tried using the formula:
Code:
=[Text128]*0.2
I would also like the "Total" text box to automatically calculate the total cost of the order and enter it into the Total field in the table "Orders". To do this I have tried using the formula:
Code:
=[Text128]+[Text130]
I know that these calculations are correct as they work in an unbound text box however I cannot get this to work with bound text boxes.Is it actually possible to do this or do I need to use another method to perform these calculations?
View 13 Replies
View Related
May 31, 2013
What I am trying to do is hard to explain but I am going to try and explain it as best as possible. I have a form that I enter football stats into for my FFL league. For the sake of simplicity I am going to focus on the completions(comp) field. I want to add a text box/label to the bottom of the page that will calculate bonuses; if comp<25=0 if comp<50=1 else 2. I also want to have the CompBonus field then be entered into a new table so I can track bonuses for each player as well.
View 3 Replies
View Related
Nov 3, 2004
I hope you can help - I have two simple problems and one slightly more complicated problem (bearing in mind I'm quite an Access Novice!)
Problem 1.
I have set up a simple form for a Timehsheet where the user can select a project and then enter hours worked in 7 text boxes for Mon-Sun. Then I have create a Total Text box at the end of the row. I need the Total box to Sum up the hours from the 7 boxes dynamically. How does one do this?
Problem 2.
How do I then ensure this calculated field populated the field hoursID in tblHours? (I am able to do this with a normal field, but with a calculated field, doesn't the formula go into the Controlsource field??)
Problem 3.
I plan to put this form as a sub-form into a main form 4-5 times so that a user can select 4-5 projects to enter hours against. I would like to have a SUBMIT button on the main form, so that all the calculated hours and selected projects populate the respective tables ONLY after this button is pressed.
Can this be done?
Thanks in advance!
Sunil
View 2 Replies
View Related
Jun 11, 2013
I am currently working on a form that calculates the sum of counts and charges for various categories. The form sums the number of items in each category, then the sum of the charges. Where I am stuck is trying to break out the "per unit" charge for each category. The calculation works fine (sum of charges/sum of items) when there is a number greater than 0, but if the count or charges are 0, the fields display errors. I attempted to compensate for this by setting the text box value on the form to iif(iserror(sum of charges/sum of items),0,(sum of charges/sum of items), but it still shows the error!. I could probably write nested iif statements to evaluate the values of each sum, but you would think this would be simpler. Here is a sample of the actual ControlSource field on one of the text boxes:
=IIf(IsError([SumOfOracle_Qual_Dev_Charge]/[SumOfBaseline Qualified Developer]),0,[SumOfOracle_Qual_Dev_Charge]/[SumOfBaseline Qualified Developer])
View 2 Replies
View Related
Apr 3, 2013
I have a dashboard that opens with my main form and it contains many textboxes with Dcounts, Dlookups and Dsums in it. I am hoping that I can put these textboxes on a separate form that is hidden unless a command button is clicked to make it visible.
I know how to do this, but my question is whether or not all of those queries will run whether or not the form is visible. Obviously if the Dsums etc.. are going to run anyway, then I won't bother.
Is there a better way to do this?
View 1 Replies
View Related
Jun 8, 2012
I've got a data entry form that is used to enter incoming waste consignment data. (I've attached a screen grab of the form) Each screen represents one record and the data is recorded in a table called "IncomingWaste". The top of the form (light blue) refers to the customer/producer of the waste. Most of that detail is held in another table on the db. The bottom (Dark Blue) part is used to record the consignment data. It gives the user a grid to enter the individual waste components within the consignment (choosing from Drop down menus for the various descriptive elements) Most crucially, the weight of each component is recorded.
I have a calculated field at the bottom which gives a Total weight for that consignment. It simply adds the weight fields together and delivers a total in the box at the bottom of the screen which displays the total weight in that consignment using "Nz([weight1])+Nz([weight2)]+ ...etc - which works fine on the form. However, I can't seem to get this calculated total recorded and appended to each record in the table and I assume it must be re-calculated each time the screen loads (?)
The main issues is.... I need to create a report which adds the total weights from each customer over a monthly/quarterly period and I simply cannot seem to do it.
I have created the report which groups all the consignments from each customer and lists them in date order, but I can't identify or define the method I need to use to collect that Total Weight calculation from each consignment record and add them up to give a grand total for that customer for a particular period. I'm assuming if I can crack the first part and get the total recorded in the table, then I can call the value into the report.
View 8 Replies
View Related
Jul 12, 2007
I would like my table to show the result of a calculations (or a formula). I surmise that you can not have a column cell calculate data, however if I can do this in the Form View and on a Report - can it just SHOW on the Table so I do not have to keep switching back and forth to see the information.
I am new to Access and think the forum will be a big help to me.
Thanks
View 3 Replies
View Related
Dec 18, 2014
All I'm after is, to sum in all records "where" reconcileDate (on the sub=form) = today in both the credit and debit fields and minus one from the other. I have a simple form and sub-form. On the sub-form I have a query based calculation that returns the following..."if ReconcileDate (on sub-form) = today, then show Debit value in yndebit" textbox..Likewise with the credit box, and all works perfectly fine.
However, I can't seem to sum these two boxes. Because the circled textboxes are query based, I've used the query as the control source (and not the text boxes) and all I get is an #ERROR? when placed in the form footer. I've tried to do this using the textboxes as the control source, and still nothing. I've tried to add a calculated control on the form itself, and still #ERROR? or #NAME? despite knowing exactly how to reference subforms within forms (this I've become quite an expert at as I simply use query design to make sure I have the correct path and control)anyway.
View 13 Replies
View Related
Jun 8, 2005
I have an odd problem that has stumped me for several days. I'm working on a form that contains a chart. The chart is based on a query, which is based on another query, which is based on a table. In the top query, I need to put some calculated fields that operate on other fields. But when I try to multiply two fields together, I get Null. I'm pretty confident that the fields I'm operating on are numerical.
The fields of the query should be:
MaxOfEverInSchool
MaxOfSurvivalToGrade
MaxOfEverInSchool
With these being the calculated fields:
MaxOfSurvivalToGradeCum: MaxOfEverInSchool*MaxOfSurvivalToGrade/100
MaxOfCanReadCum: MaxOfEverInSchool*MaxOfSurvivalToGrade*MaxOfCanRea dCum/10000
Any insight would be greatly appreciated--I just can't understand why Access would suddenly refuse to do math.
View 1 Replies
View Related
Jul 17, 2013
I have a form with a subform in a navigation menu. Something like:
Navigation Form
NavigationSubform
ParentForm (header data and some unbound calculation fields)
Subform (Multiple lines tied to query)
I have some fields in the ParentForm (i.e PF1, PF2) that would effect the values on the Subform (i.e. SF1, SF2). Also, there are some user editable values on the subform that will calculate the remaining fields on the subform (still using data from parentform).
My calculations work fine for the changes made on the subform. However I need to be able to calculate all the children lines on the subform when the form loads or a change is made to certain fields on the parentform. My On_Current event only wants to recalculate the first line.
Parent Form
PF1=10
PF2=3
Subform QTY ADJ SF1 SF2
Line 1 5 .05 10.6 112.89
Line 2 8 .14 10.38 105.15
...
Line N
SF1=PF1+(PF2/QTY)
SF2=SF1*(SF1+ADJ)
So if PF1 or PF2 were changed then all the lines is subform would recalc SF1 and SF2. If changes were made to QTY or ADJ, then that line would recalc SF1 and SF2.
View 3 Replies
View Related
Aug 10, 2006
Hi all, I have a form wich contains 2 subforms, these subforms are runned by a query wich selects data between a range of dates, the problem is when i open the form it will ask me the range of dates twice, once for one subform and then again for the other, as the range of dates are the same for both subforms is there a way to open the form typing only once the range of dates?
Thanks
Marco
View 2 Replies
View Related
Feb 4, 2005
Hello all.
I'm really new at VB.
But I'm learning. I'm also learning to work around access too.
What I'm trying to do is have a Form with a TreeViewCtl that acts as a menu for other forms.
I'd like to have the TreeViewCtl open those forms in a subform so there is always just one form open at all times.
This way my "clients" don't get lost in the forms. Believe me, they get lost easily...
I already posted a question like this here:
http://www.access-programmers.co.uk/forums/showthread.php?t=80756
But I'm not sure if this is a VB question (TreeViewCtl) or a Forums question...
A sample of what I'm trying to do is available at the link.
My TreeViewCtl works fine, but the forms open in seperate forms.
Sorry for the dbl-post.
Does anyone have any ideas?
I've been searching google for 2 weeks without any answers.
Thx for your time.
View 1 Replies
View Related
Apr 12, 2005
hi
i have a stock control db. when new stock is ordered it updates 'units on orders' field, then when goods are recieved the subform field is updated and this automatically updates the 'units on hand field'.
i then have another subform for stock going in and out, i would like to be able to have the units on hand field as above (which is the main form) automatically updated like is when using the the goods recieved sub form??
not sure if i explained it properly but i can send the structure if that helps?
many thanks
View 1 Replies
View Related
Jun 10, 2005
My main form has four command buttons that when clicked will make visible one of four subforms.
I can switch freely about the subforms by clicking on each of the buttons. However, when I place the cursor in a subform(and thus give it Focus), and then attempt to select another button to open another subform, I get the dreaded "can't hide a control that has the focus" error message.
What type of code should I use and where exactly should I be putting it?
I've tried setting focus to another subform in the OnClick event of my buttons, but that doesn't seem to be working.
View 2 Replies
View Related
Sep 8, 2006
Hi!
I've developed a big main form with subforms on. All'n all there are lots of controls on all these forms. (4 subforms in the "main" form).
As I've added controls now and then, they are not added in order from top left to bottom right.
Anyone know if I can set taborder even for the subforms?
Cause now, the tab-order is bad.
Thanks for any help ;)
View 2 Replies
View Related
Apr 11, 2013
I need a form with 2 subforms. the main form is for an indidual. the first subform is a list of characteristics. the second subform is sort of a calendar function, produced for date scheduled, results, next meeting date.
I have tried the form wizard, but it produces a jumble of the mixed fields. Can I create a subform or 2 before I create the main form, and then link them somehow?
View 6 Replies
View Related
Oct 24, 2014
I want to create a form with two subforms. I create a form with one subform without problems but i would like to know how i can link the two subforms, so when i select a row in first subform display records in second subform.
View 1 Replies
View Related
Oct 3, 2013
is it possible to have 3 subforms in a form?if so how would i go about this i have created the form and the 3 subforms however it only allows me to link one into the form.
View 7 Replies
View Related
Feb 17, 2006
Hi everyone
I use Access 2003 and have created a form with several subforms which I've placed onto a number of tabs. I'd like to have some of the most recently entered data from the subforms displayed on the main form.
For instance - if one of the subforms details the repairs to my car, in terms of date, items repaired, name of the mechanic and the cost, I'd like the most recent date of repair to be displayed on the main form. I know that I can see it be clicking the "Car Repairs" tab then scrolling down the information, but I'd like to have it displayed for easy viewing. I'm not sure if I'd need to have some VBA to do this or if it can be achieved by, for instance, creating a query to populate the appropriate textbox on the main form.
Thanks for your time and patience!
Juan
View 6 Replies
View Related
