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.
I am trying to create an accounting program on access and i got stuck on the final touches
I'll explain my situation in brief. I created two tables
/1 for the invoices (invoice number, client name, total) /1 for the orders (order id, invoice number, subtotal)
And I made a relationship between those two and everything worked out well. I created a form/subform and put a text box in the subform to calculate the sum which worked correctly. And I exported the value of the sum to the main form and it's great.
The problem is that how can i make the Total field that belongs to the table invoice have a default value as same as the Expression (sum) so that the sum is stored with the invoice record.
I am fairly new to using access and am currently creating a database for a small bakery. They have new legislation whereby they have to list all their product ingredients and any allergens in the ingredients must be shown in bold.
one to input ingredients and a checkbox if it is an allergen
The other has a main form and subform to get ProductName, Photo and then a combo box in the subform to add ingredients.
Everything appears to be working ok but I have an issue with my subform. The combo box has no duplicates and lists the ingredients in alphabetical order as I require. However, if I view the subform separately from the main form I see that it is storing all the ingredients selected for each product. Therefore it is storing a lot of duplicate information.
New to Access and learning the hard way. Im sure this is an easy one, but sure would appreciate the help. I am trying to sum a group of cells and display the total, which i have done. But I also want to store this total in my database. So my question is, how do i move my calculated total to my field that has the "record source" bound back to my field in the database? Hope that makes sense. Thanks in advance.
I have a table of records which is is basically temp storage, once I'm done inputing information, an append SQL statement is run to move the data to its permament table, then I use a delete statement to remove the values in the temp table.
Now, I have a column called Result, which stores the values of a radio button group (1, 2, or 3). I have a series of queries which count the number of times 1 or 2 appear (3 in this case, doesn't matter since it represents N/A. 1 = True, 2 = False, 0 = Null).
I'm having trouble getting the result of said query to store in a table. The statement is as follows:
SELECT [qryGetFail]![CountFail]+[qryGetPass]![CountPass] AS Total
FROM qryGetFail, qryGetPass;
This returns the sum of both counts, so I know how many questions were answered either Pass or Fail, hence I know how to score record. Here is my Append SQL statement as it currently works:
INSERT INTO tblAuditResults ( Question, Result, Comment, ACAPS, AuditDate, TeamMember, Auditor ) SELECT tblQuestions.Question, tblQuestions.Result, tblQuestions.Comment, [Forms]![frmAuditCreate]![txtACAPS] AS Expr1, [Forms]![frmAuditCreate]![txtAuditDate] AS Expr2, [Forms]![frmAuditCreate]![cmbTeamMember] AS Expr3, [Forms]![frmAuditCreate]![txtAuditor] AS Expr4
FROM tblQuestions
WHERE (((tblQuestions.Product)="4" Or (tblQuestions.Product)=[Forms]![frmAuditSelect]![txtVarProduct]) AND ((tblQuestions.Function)="3" Or (tblQuestions.Function)=[Forms]![frmAuditSelect]![txtVarFunction]));
This stores everything I need it to. But I cannot for the life of me get the statement to accept a value from a qeury and store it. I have tried adding another expression but it always just prompts for the value instead of pulling the value from the qryGetCalc.
Is there a specific way to have an INSERT statement pull a value off an active query?
I know that it is bad form to store a calculated field in a table - normalization and all that - but I can see no other way around a need that I have.
I'm trying to make a database to store injury time for employees. If an employee is injured, he can do light work for a certain amount of time, but this light work need not be contiguous. But, the total time on light work must be known so as to see when it is used up.
For example:
Bob is injured on 4/1/06 and goes on light work on 4/2/06 until 4/5/06 (4 days). Bob is then taken off light work until 4/10/06 when he reinjures the same injury. This does not count as a new injury, because it is an aggravation of the old one. So, Bob goes back on light work on 4/10/06 until 4/15/06 (6 days).
So, I need to store Bob's total time on light work for this injury (10 days) so that I can keep a running total to check against the maximum for a single injury. All I can think of is to store the sum of days on light work as a field in Bob's injury record, but that means storing a calculated field in a table.
Am I missing an easy way to do this, or is there a method to do what I want that I am just not aware of?
I know it breaks all the rules to store a calculated field on a table, but I've painted myself into a corner with the way I built this particular database, not good, I have repeating groups and basically the whole database is built this way. I need one form to store a calculated value on a table to solve a problem on a report that encapsulates the total of all the repeating groups. Before you start the lecture, I know I messed up, but this is way to far down the road to start over. Any help would be greatly appreciated.
Fields in Query2 TestID from Table1 Select from Table 1
Run Query Can’t update “Select” field
So I’m thinking that I should create a 3rd query to update the summed Quantity Delivered into a new field in table 1. Which would in effect create a stored calculated field.
I understand the conventional wisdom of not storing calculated values on the table, but I have a need to do do so. On my Input Screen I have a generated Certificate # derived from 5 single value fields keyed onto the screen. This certificate # is unique to those 5 single-value fields which should not be repeated. I want to store it on my Table as the Primary key with "no duplicates" so that if a keyer keys the same 5 single-digit values , thus creating the same Certificate #, he will get a MsgBox back telling him he cannot add a duplicate entry.
I have a score marking template for an internal audit. A criteria is either met, or it is not. All of these questions have Yes/No data type. each question has a weighting. I have written a VBA function to work out the total score by using a bunch of If statements.I need to store the final result in a table, by entering the answers (yes or no) into a form.
I have a calculated control on a form that is summing other controls and then a subtotal on a subform. This is access 2007
The calculated control does not display the value until I enter a value other then the default in all of the source controls. If I hit the "refresh" button, the value appears in the calculated control for a moment, then disappears.
I have set the default of the source controls to be 0, so none are null.
the calc control value does not stay until I put a value in the subform and its subtotal thus has a value
I'm getting a Name(hash) error when I try to set up a control source to a calculated query.
I'd like to just be able to create a form and the write in the control source as it look neater (and it the right way to do it) then subforming a query for one text box.
I presently have tried in the expression build =[qryCallRunningAverage]![Average]
Here is an example of what I was talking about re: private message and storing calculated fields. Any help is much appreciated.
Also I received your email in regards to a sample secure database but I am getting a "Memory could not be read" error when I try to open it. Does this have anything to do with how it has been converted to 97 format or is it an error at my end? (I've been able to open other dbases from the forum).
Thanks,
Dearnne
PS Thanks also for your help with adding to option groups.
I know that as a general rule one should not store calculated values in a table, but if I need to include such a value on a number of forms, and calculating that value involves manipulating an ADO Recordset, does there come a point where the performance hit outweighs the design "quality"?
I know that it isn't usually best practise to store calculated fields in a table, but in my situation it is necessary, and I haven't really found a good answer for this after searching these and other forums.
Currently my database has one "Name" field (I didn't make it like this) which might look like this "Jonathan (John) Smith" Now I also have reason to grab the name like this "Smith, John" for other purposes.
I've set up a form for inputting new people with separate fields for firstname, preferredname, and lastname. I have the calculated fields:
(Later I will add in all the iif's for if there is no preferred name)
Upon finishing the form, the user hits Submit, and I would like to store the concatenated values into the table in their appropriate fields.
I can do this with a SQL INSERT INTO statement but I'd have to add all 25 or so fields from the form, and injecting user input directly into a SQL statement is not best practice either.
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.
In my DB i would like to make a form that displays different calculated values based on other forms and queries like income running cost etc., but i do not know how to refer in an unbound control to value of a control in another form. I get always #names? error. However it worked when loaded that form i refered to. Is there a way to do it without loading the refered form?
I have 3 values in unbound text boxes and a 4th text box that is calculating the sum of the first three text boxes, i need the result of the calculation to be stored in the field "Total Cost" that is also on this form.
Hi guys,I'm new to Access except using it years back but much has changed from the 2000 version to the 2007 version - it's slowly coming back to me though, as it's mostly just an interface change. Although I'm highly experienced in SQL (mySQL) and database design / structure from being a Web Programmer.I'm wanting to store a stock amount for products. Using 3 tables, for products, orders and sales. Here's a real basic layout:productsprodID | productName | productStockordersorderID | supplier | prodID | quantityBoughtsalessaleID | customer | prodID | quantitySoldThe products table is just a list of the products. Sales is where I log what I sell to customers and the orders are what I buy from my suppliers.What I'm wishing to do is use the products table to store the stock of that item so as soon as I make a sale, that quantity for that prodID is subtracted instantly from the productStock and when I make an order it's instantly added.Is this possible, or is there a good way to do this?All help appreciated! :)Thanks,Nick.
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.
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).
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.
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 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.
