giswest
asked on
MS Access Database Subform Calculation Issue
I've been battling a perplexing issue with a database form for over a month now, and I was hoping that someone in the EE community might be able to provide some insight on the issue. Basically what I'm dealing with is a form that contains a subform. The main form allows a user to add property sale records to or edit property sale records already contained in the database. The subform allows a user to enter to land use information (acres, dollars/acre, etc.) related to the sale, and automatically calculates summary information predefined expressions. The problem is that the subform will frequently fail to perform the predefined calculations, resulting in the calculated text boxes just going blank. An even larger problem is that the error is so hit and miss that I can't pinpoint the exact cause. One minute everything works fine, and the next none of the calculations in the subform work. To make things worse, no error message is generated that would help me identify the cause of the issue. I've tried repairing the database and rebuilding its components (main form, subform, calcuations, etc.) to fix this issue, but nothing works.
The following outlines the things I've tried to resolve this issue.
1. I initally thought that the database wasn't working because the original database was created on a computer with Windows XP OS and MS Access 2007, the computers that most frequently experienced the problem were machines with Windows 7 and MS Access 2007. Based on an suggestion provided on EE, I tried to resolve the issue by upgrading the Windows 7 machines from MS Access 2007 to MS Access 2010 (https://www.experts-exchange.com/questions/27346994/MS-Access-2007-Database-built-on-Windows-XP-computer-doesn't-function-properly-on-Windows-7-Computer.html). This appeard to resolve the problem, but then the error began to occur again.
2. I eventually noticed that the error was also occuring on the Windows XP and MS Access 2007 machines, so I rebuilt the database and it's components multiple times from scratch on a machine running Windows XP and MS Access 2007 machine and a Windows 7 and MS Access 2007 machine. Same result. Based on this, I'm wondering if there is an issue with the subform or the manner in which I setup the subform calculations.
I've attached the database in question to this post. Please be advised that the database tables are normally linked to a shared database, but for the purposes of this post, I included the database tables in the actual database. I don't know that this will affect the issue I'm currently facing, but I thought that I would just put it out there. If anyone would like me to repost the database with links to a shared databse, I would be more than happy to do so.
I'm at total loss on this issue, and I hope that someone can help. Any input is greatly appreciated.
Database-v17.0.zip
The following outlines the things I've tried to resolve this issue.
1. I initally thought that the database wasn't working because the original database was created on a computer with Windows XP OS and MS Access 2007, the computers that most frequently experienced the problem were machines with Windows 7 and MS Access 2007. Based on an suggestion provided on EE, I tried to resolve the issue by upgrading the Windows 7 machines from MS Access 2007 to MS Access 2010 (https://www.experts-exchange.com/questions/27346994/MS-Access-2007-Database-built-on-Windows-XP-computer-doesn't-function-properly-on-Windows-7-Computer.html). This appeard to resolve the problem, but then the error began to occur again.
2. I eventually noticed that the error was also occuring on the Windows XP and MS Access 2007 machines, so I rebuilt the database and it's components multiple times from scratch on a machine running Windows XP and MS Access 2007 machine and a Windows 7 and MS Access 2007 machine. Same result. Based on this, I'm wondering if there is an issue with the subform or the manner in which I setup the subform calculations.
I've attached the database in question to this post. Please be advised that the database tables are normally linked to a shared database, but for the purposes of this post, I included the database tables in the actual database. I don't know that this will affect the issue I'm currently facing, but I thought that I would just put it out there. If anyone would like me to repost the database with links to a shared databse, I would be more than happy to do so.
I'm at total loss on this issue, and I hope that someone can help. Any input is greatly appreciated.
Database-v17.0.zip
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My three year old just woke from a late nap. It will be a couple of hours before I can refocus on this. I will not abandon this.
ASKER
Antman,
The example you provided is a record that doesn't have data entered for the Acre or Dollar_Acre columns. I encounter the problem when data is entered in these fields, and then the calulations for the Total Unit Value column (the far right hand column) and the Total Acres and Dollar/Acre text boxes (located in the form footer) just go blank.
No rush on a reply. I have several nieces and nephews, so I understand that family matters come first. I look forward to your input.
Thank you.
The example you provided is a record that doesn't have data entered for the Acre or Dollar_Acre columns. I encounter the problem when data is entered in these fields, and then the calulations for the Total Unit Value column (the far right hand column) and the Total Acres and Dollar/Acre text boxes (located in the form footer) just go blank.
No rush on a reply. I have several nieces and nephews, so I understand that family matters come first. I look forward to your input.
Thank you.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry that I haven't responded sooner, but I've been swamped this week. I just wanted to give you a quick update on the database.
After I created this post, I went back and rebuilt the database from scratch, one component at a time. While doing this I found something interesting. I found that when a requery of the landuse subform was executed (usually Analysis Code AfterUpdate or Sale Price AfterUpdateevent), the database would kick an error (could not find object) on the following requery function:
Form_LandMix_Landuse_Maste rForm.Requ ery.
Correct me if I wrong, but the requery shouldn't have never functioned properly on the original database, but for some reason it did. I remember running across this issue when I first built the database. Initally, whenever I needed to requery a text control (i.e. Analysis Code text) within the subform, I tried utilizing the following requery function (this a requery of the AnalysisCode text control):
Forms!Sale_Management!Land Mix_Landus e_MasterFo rm.Form.An alysisCode .Requery
However, I could never get that code to work so I tested the Form_LandMix_Landuse_Maste rForm.Requ ery function and it worked. When I rebuilt the database I went with the standard subform requery function (Forms!Sale_Management!Lan dMix_Landu se_MasterF orm.Form.A nalysisCod e.Requery) and now everything is working fine.
I summary, I don't know if any of the above info has anything to do with the problem I was experiencing, but I'm fairly certain that there was probably a deeper problem or bug related to the Landuse subform, which resulted in the failure of the calculated text controls.
I look forward to your responses, and I'll get some points handed out after I submit this post.
Thank you for your input.
After I created this post, I went back and rebuilt the database from scratch, one component at a time. While doing this I found something interesting. I found that when a requery of the landuse subform was executed (usually Analysis Code AfterUpdate or Sale Price AfterUpdateevent), the database would kick an error (could not find object) on the following requery function:
Form_LandMix_Landuse_Maste
Correct me if I wrong, but the requery shouldn't have never functioned properly on the original database, but for some reason it did. I remember running across this issue when I first built the database. Initally, whenever I needed to requery a text control (i.e. Analysis Code text) within the subform, I tried utilizing the following requery function (this a requery of the AnalysisCode text control):
Forms!Sale_Management!Land
However, I could never get that code to work so I tested the Form_LandMix_Landuse_Maste
I summary, I don't know if any of the above info has anything to do with the problem I was experiencing, but I'm fairly certain that there was probably a deeper problem or bug related to the Landuse subform, which resulted in the failure of the calculated text controls.
I look forward to your responses, and I'll get some points handed out after I submit this post.
Thank you for your input.
ASKER
Antman and Jeff,
Thank you for your input on this issue.
Thank you for your input on this issue.
Not sure but.
Depending on what version of Access you are using, certain syntax will/won't work.
So you may very well have some combination code and design elements that worked in one version, but does not work in another.
Depending on what version of Access you are using, certain syntax will/won't work.
So you may very well have some combination code and design elements that worked in one version, but does not work in another.
ASKER
I used MS Access 2007 throughout the process, so that was a constant. The only difference is that I rebuilt the database using a machine running Windows 7 instead of the original machine running XP. Not sure if that had any influence on the situation.
ASKER
Jeff,
I just wanted to revisit a suggestion you made in your previous post (ID: 37019891), about "creating these as fields in the subform's recordsource". Are you suggesting that I add the fields to the table and then calculate the values within the newly created fields?
I could create these fields as suggested, but I don't think that I would be able to calculate many of the summary values (i.e. Total Acres, Land Contribution, and Dollars / Acre) due to the way I've structured the LandMix_Landuse1 table. Could you please clarify.
Thank you.
I just wanted to revisit a suggestion you made in your previous post (ID: 37019891), about "creating these as fields in the subform's recordsource". Are you suggesting that I add the fields to the table and then calculate the values within the newly created fields?
I could create these fields as suggested, but I don't think that I would be able to calculate many of the summary values (i.e. Total Acres, Land Contribution, and Dollars / Acre) due to the way I've structured the LandMix_Landuse1 table. Could you please clarify.
Thank you.
No, I am suggesting simply changing the calculated "Controls" to calculated "Fields"
For example:
You have a table with a Price field and a Quantity field.
Instead of adding a control to the form with a control source of: =Price*Quantity
Make a query (or edit the Recordsource) to include this calculation
SELECT ProductID, Price, Quantity, Price*Quantity AS TotalPrice
FROM YourTable.
This will make TotalPrice just another field, so you can simply drop it into the form or report.
I am not saying that this will help in your situations specifically, but it sometimes simplifies form/report design.
JeffCoachman
For example:
You have a table with a Price field and a Quantity field.
Instead of adding a control to the form with a control source of: =Price*Quantity
Make a query (or edit the Recordsource) to include this calculation
SELECT ProductID, Price, Quantity, Price*Quantity AS TotalPrice
FROM YourTable.
This will make TotalPrice just another field, so you can simply drop it into the form or report.
I am not saying that this will help in your situations specifically, but it sometimes simplifies form/report design.
JeffCoachman
ASKER
I initially noticed the issue after adding a new record and then scrolling down to the subform, but then noticed that it would also occur when navigating through the records (i.e. previous record, next record, etc.). As far as a particular record, I've never noticed the behavior occuring based on a specific record. As previously mentioned, it's hit and miss in regards to when the error occurs. Sorry I can't be of more help.
I appreciate your response.