Microsoft Access - Help on storing a calculated field from form in table
Asked By L Williams on 26-Jun-12 03:50 PM
This question concerns a calculated field on frmNCC. At the request of the dbase users, they wanted this form as part in Access instead of in Excel where it belongs. So I slaved over the myriad calculations and came up with a beautiful, perfectly functioning form... UNTIL... I remembered that I need to be have that total field [TotalNCCCost] in tblNCC because it needs to be downloaded into a spreadsheet.
Assumptions and attempts: I assume I need to create a field in tblNCC called TotalNCCCost and make that the control source for this field. And I tried that. I then moved the calculation into the AfterUpdate of the form. This caused conflict with the Me.Recalc expressions. I tried deleting the Me.Recalcs and the form ceased to function.
I am at a loss and I am under a serious time crunch. They want to go live with this puppy next week. I would greatly appreciate any help you have to offer. I am an Access novice -- especially when it comes to VBA -- so please be as explicit as possible with your advice. Thank you.
Here is a copy of the file with all of the data removed for confidentiality reasons. http://www.eggheadcafe.com/fileupload/1717688819_Nonconformance dBase no data.zip
wally eye replied to L Williams on 26-Jun-12 07:12 PM
A couple tricks to get you there:
Rename your existing TotalNCCCost to TotalNCCCostCalc, and create a new TotalNCCCost control. Hide the TotalNCCCost control (I normally change the background color to red, to highlight the fact that it is hidden). Then, create a Form_BeforeUpdate event:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.TotalNCCCost = Me.TotalNCCCostCalc
End Sub
I know you don't have time to work with this before you go live, but it is not a good model to have all your fields spelled out in this manner. Normally, I would recommend having a couple tables, say tblSupportPersonnel:
SptPrsID AutoNumber
SptPrsDesc Text - Description of support personnel
SptPrsRate Double
and tblNCCPersonnel:
SptPrsID Long - Foreign Key from tblSupportPersonnel
Qty Long
Hrs Long
TotHrs Long
Rate Double
TotalAmt Double
Or something like that. When you want to add a new support personnel, you would just add an entry to the tblSupportPersonnel. Unfortunately, it does take a lot more work up front, and is a bit more complicated. The benefits over time will add up, though.
L Williams replied to wally eye on 26-Jun-12 08:32 PM
I was beginning to ffear something like that. #1 -- Gee, it would have been a lot simpler to leave this bugger in Excel to begin with. and #2 - There really ought to be a way to tell Access, "Hey, that field there; put it over there."
Thanks for your help.
Pat Hartman replied to L Williams on 28-Jun-12 08:24 PM
I agree. There is definitely a problem with your schema. In spite of that, there is no reason to store a calculated value. Modify the query you use as the RecordSource of the form to do the calculations.
Start by Removing the calculated columns from the table.
Select ..., [LineOpTtl]*[LineOpRate] As LineOpCost, [LineLdrTtl]*[LineLdrRate] As LineLdrCost, ....
From ...
Once you've fixed the query, you can bind LineOpCost, LineLdrCost, etc to controls. When the other values are changed, Access will calculate these.
Export the query rather than the table.
i have tables items, order and bill. . i want that when user select any item or items then its price and grand total should display on main form. . guide me In the order details subform, add a new to a new record to see the mainform control update with the new subtotal. keywords: Microsoft Access, Sum, control source, grand total description: total of rows in access i have tables items, order and bill. . i want
I'm trying to get a running total, but running into some problems. The issue I have is this: I have tables that tblTotal). The issue I'm having is that when I try to use a running total expression it uses the primary ID and not the actual cost the ID is linked understand where 2 and 6 came from. . .help us help you. . sincerely, maurice maglalang keywords: Microsoft Access, Microsoft, data tables description: Running Total I'm trying to get a running total, but running
i have tables items, order and bill. . i want that when user select any item or items then its price and grand total should display on main form. . guide me Hi, I think it depends on how the segment to populate its Price from Item table corresponding to the Item selected. The Grand total (I guess it is the total price of Items selected) could be calculated something like, SELECT SUM(Price) FROM <Item table 1) The above is applicable for single selection as well. Hope these assist. keywords: SUM, Microsoft Access, Check, Box, Click, Item, grand total, price description: total i have tables items, order and
When using Access I have a problem. How do I combine multiple entries into a single line item multiple line items I want ot to show only one Sales Order Number with a total. The fields are PO Number, Sales Order Number, Unit Price and Sum of 2% Fee it from the GROUP BY list. keywords: MIN, SUM, Sales Order Number, Sales Orders description: Access When using Access I have a problem. How do I combine multiple entries into a single line item
Multiple Queries for Microsoft Access Report DataBase
I am using Microsoft Access XP. I have three queries that I would like an Access Report to pull data from. I have been getting difficulty putting these together. They all each company number, also broken down by month in a crosstab pivot query. 3) the total number of active file numbers broken down by month in a crosstab pivot query. I all lined up. I would like the results to be exported to Excel when completed. Access Reports Discussions Office (1) Excel (1) XP (1) EnrollmentID (1) PersonID (1) ClassID (1) Report It all starts with the data. . . More info, please. . . - - Regards Jeff Boyce www.InformationFutures.net Microsoft Office / Access MVP http: / / mvp.support.microsoft.com / Microsoft IT Academy Program Mentor http: / / microsoftitacademy
Hi, Is it possible to access a tables data over multiple joins? For instance, for each row of Table A, I want the associated data from Table D. Is this possible in access? If it's not, could you suggest an alternative? Please see the example. Many Thanks ID = Table C.ID LEFT JOIN Table D ON Table C.ID = Table D.ID Access does support multiple joins and check the query you have which should be working. . . let the associated identifier. Table D then has specific items related to Table A (e.g. Total number of 'problems'). I want to sum up the problems for each association. This is why I need to access information in Table D from Table A. Please help! My mind is blown! Many Thanks
I'm trying to create a pie chart in a report in access - the chart is part of a total reward statement for employees so needs to update with every change in employee. I can Hope this helps. keywords: Chart, report description: Creating a pie chart in a report in Access I'm trying to create a pie chart in a report in access - the chart is part of a total reward statement for empl
Yes, I'm just getting around to this, and have the following questions (so far): An Access db relies upon a good number of Oracle tables. In order to query them and create a nice, bulletproof data processing routine for the user. But I can't have Access "failing to connect" willy-nilly. Is there concensus on whether it would be advantageous to an export mechanism that you could just dump the entire set of tables out to Access? I know SQL Server has this capability. I've been developing with Access since 1994. I fell in love with Access because of its ability to "Access" various ODBC data sources. Very few of my applications
MOM 2005 & SQL 2005 Failure Windows Server
momdas and password, enabled error reporting then hit install. I then received the following error: Microsoft Operations Manager 2005 Setup Failed to create MOM Database. Error Code: -214217900 (CREATE DATABASE failed without a problem? Michael R. Mastro II MOM Discussions MSI (1) PROPERTY (1) Action (1) Microsoft (1) Files (1) Operations (1) Manager (1) Value (1) Hi Michael, Do you have installed Reporting SP1 on that machine too? Then take a look at this KB http: / / support.microsoft.com / kb / 896861 / Regards Anders Bengtsson [MCSE, MCSA, MCP] | anders AT contoso.se | http: / / www 10:42:35:843]: PROPERTY CHANGE: Adding INSTALLDIR property. Its value is 'C: \ Program Files \ Microsoft Operations Manager 2005 \ '. MSI (c) (58:94) [10:42:35:843]: PROPERTY CHANGE: Adding WEBMOM_DIR.6C0006A8_1C97_45AD_A6C2_F11AA079E4CD property. Its value is 'C: \ Program Files \ Microsoft Operations Manager 2005 \ WebConsole \ '. MSI (c) (58:94) [10:42:35:843]: PROPERTY CHANGE: Adding WEBCTRL_DIR.6C0006A8_1C97_45AD_A6C2_F11AA079E4CD property. Its value is 'C: \ Program Files \ Microsoft Operations Manager 2005 \ WebConsole \ webctrl_client \ '. MSI (c) (58:94) [10:42:35:843]: PROPERTY CHANGE: Adding WEBCTRL_10_DIR.6C0006A8_1C97_45AD_A6C2_F11AA079E4CD property. Its value is 'C: \ Program Files \ Microsoft Operations Manager 2005 \ WebConsole \ webctrl_client \ 5_0 \ '. MSI (c) (58:94) [10:42:35:843]: PROPERTY CHANGE: Adding WEBCTRL_10_treeimages_DIR.6C0006A8_1C97_45AD_A6C2_F11AA079E4CD property. Its value is 'C: \ Program Files \ Microsoft Operations Manager 2005 \ WebConsole \ webctrl_client \ 5_0 \ treeimages \ '. MSI (c) (58:94) [10:42:35:843
Dear Microsoft - UAC (.png & wmp) issues should be addressed. Windows 7
Dear Microsoft - UAC (.png & wmp) issues should be addressed. Windows 7 I have been reading posts here are having problems with .png files and Windows Media Player. This is a suggestion to Microsoft to review the UAC for some issues it has been causing. A few months ago is experiencing this issue, please post. Good Luck!!! Jim - -- -- -- -- -- -- -- - This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http: / / windowshelp.microsoft.com / communities / newsgroups / en-us / default.mspx?mid = e94f2a2e-f44a-4d97-a2a0-5b1cc9930c3e&dg = microsoft.public.windows.vista.administration_accounts_passwords Windows Vista Accts Passwords Discussions Internet Explorer 7 (1) Windows Update host of Vista related errors and this is one of the more irritating ones. Please Microsoft fix this rather thane relying on your users to work around it by having to