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:
I have come across this problem several times. I have sub reports on a report and calculations are done using the data in the sub report. In many instances the subreport is empty. The calculated fields read #error. (otherwise the report runs well and records with data calculate fine) I would like to hide these errors and associated labels when the subreport is empty. Everything I have tried either does nothing (error still appears in text box - but runs ok otherwise) or I get a runtime error since it is trying to access "nothing" in the sub report. How can I catch and therefore react on this empty sub report. (or subform too) Thanks Lisa
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.
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?
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.
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.
this is probably an old one but its catching me out. I'm writing a sports club database in ASP. When users submit their club details in the form to Access, I get errors if it has the characters ' in any of the fields. I've managed to get around " by replacing it with "". However, this doesn't seem to work with '
I'm new to ASP... anyhow the part of my form which deals with this is:
I also do a bit more error checking on the email address, for length, @ etc. This bit seems to work ok. However, Any tips on how to improve the error handling for ' or " before its submitted to access is appreciated.
Hi guys . i wonder how i can trap the following errors in access 2000 forms. I have the database tables in sql server 2000 and front end is made by access 2000 forms. I be happy if some one show me how i can trap these errors and promt aproperte message to the user when they violate them using forms.Thanks
1)Violation of not null constraint 2)primary key violation 3)Trigger violation 4)and other erros
I seem to have the biggest problem with getting information from one window/form to another. I have a MainForm, that has 2 subforms on it. On the subform2 there is a command button that opens a new “Form3” in “dialog”. Form3 has a list box displaying data from a table and a command button that says “Add to Subfom2”. I want to get the information from Form3 and update the unbound textbox on subform2. I keep getting errors; “Field not found in….” I have tried: Forms!MainForm!Subform2.Textbox = ListBox.Colunm(1) Forms!MainForm!Subform2.Form!Textbox = ListBox.Colunm(1) Forms!MainForm!Subform2!Textbox = ListBox.Colunm(1) None seem to work, what am I doing wrong? Can some on help me with this syntax. Thanks Enviva
The queries all run off data elsewhere on the form, and i have triggered a "Form Refresh" macro on each of the changeable fields of the form, (so for example when changing the start date for the period the form then refreshes and re-runs the queries and updates the forms.
All things work perfectly on our windows XP systems but the rare few Vista operating systems causes these form fields to have errors. Basically the 3 subtotal fields don't appear to refresh when they are supposed to, and the total field shows as "Error"
It is actually a fairly cosmetic thing as the more important reports are generated without error, but it is something i would like to fix anyway, simply to keep the masses happy.
Would be very appreciative of any assistance or idea's on what's happening, or perhaps what's not happening here.
BTW: All have MS Office 2007, None do not have Administrator access. All XP machines work perfectly, all vista machines have these errors.
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 have a table that stores the criteria that the query is supposed to pull from so that when a user logs into my database, it reads that the person is part of a sepcific unit and then pulls that specific criteria and places it in a text box on a form. When they click a button on that form, it then opens the form that is connected to the query. The data type for the field I need criteria for is a number, so the criteria that shows up in the text box is this "1 or 3 or 5" so that it shows those specific numbers. If i put that directly into the query, it works just fine, but if I try to connect it from the text box in the form, it gives me a "data mismatch error" or an error explaining that criteria is too vast for access to pull the information. How do I make it read the information in there so that I dont have to create 1000 forms for each different unit.
Example:
Field: Unit Table: Master Log Data Criteria: 1 or 2 or 3
Works fine!
Field: Unit Table: Master Log Data Criteria: [Forms]![Selector]![Criteria]
The text box that is referenced in the criteria in the query says 1 or 2 or 3 and then I get an error... If the information is the same, why does it work when I place it in the query specifically, but not when its in the text box?
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!
I am working on a database that contains patient demographic information. I have a form that prompts the user to enter either the medical record number or part of the patient name. Once you click search it'll then display a 2nd form with a list box outputting the results. Then from there the user can click on one of the entries in the listbox and it'll display the full demographic information on a 3rd form.
Now my problem is on the search part, it completely ignores if I have a medical record number entered. It continues to search by name only.
In my query for med rec # I have
[Forms]![frmSearch]![txtHistn]
where txtHistn is the text box field passing into the query. And for patient name column in the query under OR I have the following:
Like "*" & [forms]![frmSearch]![txtPname] & "*"
So I am confused why it ignores the medical record number entirely.
I have a test database attached. This is just a sample with dummy data entered and not designed pretty. Through this up for another issue I had yesterday that has been resolved but now discovered this query one. The frmSearch is how it begins. If you search by Smith it'll bring up the two Smiths I have entered. If I leave the field blank and enter 1 for the medical record number it treats it as null and displays everything. How can I fix this?
And while on the topic of query, the true database I am working with resides on an Power I series (formerly AS400s) and only linking to their tables. The data is entered in all Caps in the tables. How can I force whatever the user enters into the search screen that it will automatically uppercase the letters before performing the search? Without having the user to remember to enter with their cap locks on.
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
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.
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?
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?
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?
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.
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...
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!
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.
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.
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.
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)
