Secondary Forms And Calculations, Pls Help
Oct 11, 2004
Hello
I have this problem. I've made a table called "producers" and a main form based on this table. I've also made a table called "orders", with several data regarding orders, and a secondary table based on the "orders" table. These tables are linked. So these forms show the orders made by each producers. Now , I try to add the "price" field of each record of the secondary form and then show the result in a text box in the main form, so that I can have a total price amound for each producer.I've tried many things but nothing seems to work. Is this so difficult to achieve? Pls help. Thank you in advance!
View Replies
ADVERTISEMENT
Oct 9, 2006
Hi
I have a main table in my database. For each record in this table there may exist 0, 1 or many records linked to it in a secondary table.
I have created a form for viewing the records in the main table, and now want to link to a second form that displays the records in the secondary table, if indeed any exist for the main record being displayed on form1.
Specifically . .
If I can detect whether secondary records exist for each record in the first, how can I pass a parameter (ie Table1id) to the secondary form so that we can view only the secondary records associated with the main record selected ?
thanks
ajm
View 2 Replies
View Related
Apr 17, 2013
I have a form where I am entering orders. For this, there is only 1 item and 1 customer per order. The Order_Table is the 'general' of the form. However, I want a Customer_name dropdown on the form, and return data to 'display' on the form (but capturing and ONLY storing the Customer_no), once the user makes a selection.
Additionally, in the same vein, I want to have an Item_name list, where the user selects one from the listing, and display (again, no edit) the name. Again, only want to store the Customer & Item numbers on the Order table.Here's the 'general' table layout (I've taken out what doesn't matter):
Table: Order_Table
Order_no
Customer_no
Item_no
Table: Customer_Table
Customer_no
Customer_name
Table: Item_Table
Item_no
Item_name
View 1 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
Apr 7, 2006
Hi Everyone:
I have a question that I can't seem to figure out. I am trying to create a form in which there are three major fields. 1) Value 1 2) Value 3) Sum of Value 1 & 2. I want it so that two fields are added up and then the sum, which will populate in the third field, will be stored in a table.
Does anyone have any suggestions on what I can do? I am fairly new to access and haven't had much luck finding anything on the net.
I appreciate any and all advice.
Thanks!
Darin
View 9 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
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
Mar 18, 2013
I created a Form to enter data into a table. Two fields are numeric and typed into, a third field calculates the ratio instantly. I did that by entering "=field1/field2" into the control box in the properties panel for that form element.
However, when I look at the table, all the data goes in except this calculated ratio. Is there a way to get the form to "write" it to the table?
Alternatively, how to get the table to act like a spreadsheet and automagically calculate that third column, then it actually doesn't even need to appear on the form.
View 5 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
Feb 22, 2005
Hi all,
I have a data entry form with several values that could be calculated and entered if I knew how. The FAA requires logging and tracking values that result in the following fields:
TimeSEL
TimeXC
TimeNight
TimeDay
TimeDual
TimePIC
TimeTotalFlight
My last flight resulted in entering 3.4, 3.4, 0, 3.4, 0, 3.4, 3.4
My question is: is it possible to add some sort of test expression in the TimeXC field which would examine an associated check box (i.e. "Was flight XC?") and if it was checked then would automatically calculate the flight hours (TachStop-TachStart) and enter the value in the TimeXC field?
Thanks for any help.
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
Nov 7, 2014
I have a form bound to a table, with a subform in there, and I have a textbox with a source one of the table's fields. I would like the value of this textbox to be calculated based on the values of some boxes in the subform, but I also wont to be able to edit the value on the textbox myself.
Is it possible to have a textbox that get its value both from calucaltions and user input?
View 1 Replies
View Related
Jan 23, 2006
Hey everyone,
Is there any way that you can do a secondary sort on a field?
Any help would be of great!
Thanks - edawson
View 2 Replies
View Related
Mar 14, 2013
I have a subform based on a query. That query contains a couple of calculations that end up in textboxes on the subform. The problem is that the calculations require me to enter 2 dates that are on the subform. If these are not entered before the calculation is performed I get a "Cannot update record" error message and then #error in both Lease cost and Discount Cost textboxes.
View 6 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
Mar 15, 2005
The main form (frmMain) is fed by qryMain. I'd like to put a memo field on a separate form (frmMemoField) to avoid cluttering the main form.
Is there a way to ensure that frmMemoField offers the same record currently displayed on frmMain, please, Experts?
View 2 Replies
View Related
Apr 25, 2006
Afternoon all,
ac2003
I have a problem concerning how to display records dependant or depending on the current record.
Basically I have a seperate area within the db that gives you the option to set which record(s) depends on or dependant to another record(s) as primary and secondary
When trying to display this info, it is fine for a direct primary - secondary within my listbox, but the complication occurs when the secondary record could also be a primary record of another record (if that makes sense).
for example. The data is as such,
PrimaryID SecondaryID
10 12
12 13
15 10
So from this data, my list box should show 1 record that this record depends on (15) , and in another list box 2 records than depend on this record (12,13)
I'm wondering if some kind of loop code would do the trick but not sure what angle to attack this at.
I would appreciate it if anyone knows a simple solution to this.
Thanks
Kempes.
View 13 Replies
View Related
Feb 17, 2005
Hello
I have a form that is bound to a table called "date". Date has to fields "date_id" which is a numeric value and "date_description" which is the name of the month.
A secondary form is bound to a table called "Employees" which has fields regarding data about the emploees of a company (eg "surname", name" etc). I use these 2 formes together to select the month of year and show/edit the data of the employees. Everything work perfectly except one last thing. Is it possible that after I edit (or add) a record in the secondary form (table "employees") the secondaty form to be sorted by "surname"? How can I do such a thing? I would be grateful if you give me an example.
Thank you in advance
View 6 Replies
View Related
Nov 20, 2014
I am trying to get the main form to display a total Amount for all records in Purchase order details but the control displays Error:
I have a Form called frmPurchaseOrder with a sub-form sbfrmPurchaseOrderDetails
sbfrmPurchaseOrderDetails takes item details from a table based on what is selected in the combo box then fills out four additional fields in the same sub-form.
SELECT tblItemListDetails.ItemID, tblItemListDetails.ItemName, tblItemListDetails.ItemSize, tblItemListDetails.ItemUnit, tblItemListDetails.ItemUnitCost, tblItemListDetails.ItemBrand FROM tblItemListDetails WHERE (((tblItemListDetails.Supplier)=[Forms]![frmPurchaseOrders]![SupplierCombo]));
Private Sub cmbItemName_Change()
Me.txtSize.Value = Me.cmbItemName.Column(2)
[Code] ....
View 5 Replies
View Related
Oct 27, 2014
I have an Access database, where one of the tables is near the maximum amount of fields (241 of 255), was just wondering if I could set up a secondary table that would be linked to the original table by a specific field and would store the other fields that would like to add to the table.
View 7 Replies
View Related
Aug 16, 2014
Imagine you have a split database with two users making reports on their own front end. Lets say user 1 wants to share a report they built with user 2. is it possible to send this Object through vba so that user 2 would received a working editable copy that they can use on their front end without sharing the actual accdb front ends?
I thought about creating a Database that would just host the reports but again if I cannot send or access secondary database objects then this method would not work.
View 3 Replies
View Related
Jun 13, 2006
Hi
I have three figures to display in a chart.
The Total Ongoing Calls
The Ongoing Calls in Area 1
The Ongoing Calls in Area 2
Ideally the Total Ongoing Calls should equal the other two but this is never the case due to calls in wrongs areas etc. To display this I have the Total Ongoing Calls shown as an Area Chart on the Primary Axis with the other two figures shown as a Stacked Area Chart on the Secondary Axis.
This way if I manually fix the Primary and Secondary Axis Scale to the same then I can see at a glance the total in both areas and the discrepancy from the Total. Great.
The issue is that the Primary Axis will almost always be slightly higher than the Secondary Axis which means that whenever the Scale changes I would have to manually change the other Axis. Is there any way, programaticallly or otherwise, that I can link the two Axis together so that if one changes up or down the other changes with it.
Thanking everyone I am about to confuse in advance.
JC
View 1 Replies
View Related
Sep 24, 2005
Hi,
I can't seem to get this right...any help much appreciated:
I have a two tables LESSON<PAYMENT (One to Many)
LESSON
Lesson_ID (PK autonumber)
Lesson_Date (Date)
PAYMENT
Payment_ID (PK autonumber)
Lesson_ID (FK)
Payment_Date (Date)
I am maintaining LESSON using a simple form. On creating each new LESSON record, I also wish to insert a row into PAYMENT, using values from the LESSON table - Payment_ID(autonumber), Lesson_ID = LESSON.Lesson_ID, Payment_Date = LESSON.Lesson_Date
What is the best way to do this?
I have tried to add an Event Procedure to 'Before Insert' but I can't seem to get the syntax correct. Also, for this to work, do I need to paint all fields on LESSON form including (hidden) PK?
I have simplified the tables above but they are relevant to what I am trying to do. Any help would be much appreciated- am new to Access...rather frustrating.
Many thanks,
Simon.
PS I have searched through existing messages but can't see one that answers this, I apologise if this has been answered before- just point me to the orig post.
View 2 Replies
View Related
Mar 9, 2006
Hi all, don't know if this is possible, but right now I'm running a query and then performing Dmin/Dmax on each record in the recordset and it's running SLOOOOW. Perhaps if I can get it from a query it would be faster.
I have Table-A and Table-B with a one to many relationship. In the Table-A I preferably need to return only one row per record. From Table-B, I need to return the minimum and maximum record that corresponds to the ID in Table-A.
I am familiar with min and max, but it the join methods I've been using either return multiple rows per ID or none. Any way around this?
View 2 Replies
View Related
May 14, 2014
I built an Append Query to take records of 'Leavers' from my Primary Table and add them to a Secondary Table named 'Leavers. This worked perfectly, but on reflection I determined that I needed to append a further column 'Notes' which exists in the Main Table but not in the Secondary Table.I amended the SQL statement as follows, but the query now fails stating that it doesn't recognize the field 'Notes'.
INSERT INTO Leavers ( [Member ID], Surname, [First Name], [Address 1], [Address 2], Town, PostCode, Phone, [E-Mail], Notes )
SELECT [Mail List].[Member ID], [Mail List].Surname, [Mail List].[First Name], [Mail List].[Address 1], [Mail List].[Address 2], [Mail List].Town, [Mail List].PostCode, [Mail List].Phone, [Mail List].[E-Mail], [Mail List].Notes
FROM [Mail List]
WHERE ((([Mail List].Leaving)=True));
Does this mean that one would need to recreate a new Secondary Table to incorporate the additional field? I have attempted to edit the secondary table by merely adding the 'Notes' field but that doesn't seem to be possible.
View 3 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
