• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

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 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27346994.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
0
giswest
Asked:
giswest
  • 6
  • 3
  • 3
2 Solutions
 
The_AntmanCommented:
Can you provide a specific record where the calcs fail?

Is there ever an occurrence where a property shows the calcs at one time but not another?

How can I observe the condition?
0
 
giswestAuthor Commented:
Antman,

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.
0
 
The_AntmanCommented:
Is this an example? subform
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
The_AntmanCommented:
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.
0
 
giswestAuthor Commented:
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.
0
 
Jeffrey CoachmanCommented:
1. Sometimes calculated field will appear "Blank" because the form is not done calculating...

2. Your subform contains a lot of calculated controls.
Why not create these as fields in the subform's recordsource?

3. Are you absolutely sure that these formulas are not displaying Blank, because they eveluate to Null?

Perhaps you can provide the *exact* steps to re-create this issue?

JeffCoachman
0
 
giswestAuthor Commented:
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_MasterForm.Requery.  

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!LandMix_Landuse_MasterForm.Form.AnalysisCode.Requery

However, I could never get that code to work so I tested the Form_LandMix_Landuse_MasterForm.Requery function and it worked.  When I rebuilt the database I went with the standard subform requery function (Forms!Sale_Management!LandMix_Landuse_MasterForm.Form.AnalysisCode.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.
0
 
giswestAuthor Commented:
Antman and Jeff,

Thank you for your input on this issue.
0
 
Jeffrey CoachmanCommented:
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.
0
 
giswestAuthor Commented:
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.
0
 
giswestAuthor Commented:
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.
0
 
Jeffrey CoachmanCommented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now