If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem with calculated controls with data from subforms
Hi,
We have an application where one of the forms has several calculated fields. Some of the data comes from subforms, some use data in other calculated fields. The subforms are linked to the main form on the invoice number. This has worked for years, moving from Access 95 through 98 to 2000, but one client bought new computers running Win XP Home Edition and with Office XP, and now the calculated fields show up blank.. It appears to be an Access 2003 problem, because it works fine on Access 2000 machines running Win 98, 2000 and XP. I am wondering if it is a timing problem, since some calculated controls use the contents of other calculated controls. I also notice that when I move the main form from one record to the next, the controls takes a second or two to update. The new machines are also fast, 2.7 GHz. Main form, Invoice, based on the Invoice Header table, has several subforms, one of which is the Invoice Detail subform, based on the Invoice Detail table. .. The main form has one calculated control called SubTotal with the ControlSource as follows: =Nz([Forms]![Invoice]![Invoice Detail subform].Form!OrderTotal) where OrderTotal, in the form footer in this subform, is the sum of all invoice line item amounts. It has another calculated control called CalSalTax with the following ControlSource: =(Nz([SubTotal])*Nz([STRATE]))/100 where SubTotal is the first calculated control. There is another calculated control called CalTotal with the following ControlSource: =[SubTotal]+IIf(IsNull([CalSalTax]),0,[CalSalTax]) which depends on the two previous calcualted controls, SubTotal and CalSalTax. There is another calculated control, CalDeposits, which uses data from another subform, the ControlSource is: =IIf(Not IsNull([Child127].Form!tcal_Sum_Payment),[Child127].Form!tcal_Sum_Payment,0) where tcal_Sum_Payment in the form footer is the sum of all deposits in this subform Then there is a calculated control called CalIntPaid Installation paid) whith the following ControlSource: =IIf([InstalISpaid]=-1,Nz([INSTALCOST]),0) where InstalISpaid and INSTALCOST are based controls on the main form. Finally, there is the calculated control called DueStore with this ControlSource: =Nz([caltotal])-Nz([caldeposits])-Nz([CalIntPaid]) where caltotal, caldeposits and CalIntPaid are the calculated controls above. I tried calculating the second calculated control directly, but then all calculated controls show #Error. I am thinking about trying to calculate all the controls directly, but would like some opinions. Any help would be appreciated. Ragnar |
#2
|
|||
|
|||
Problem with calculated controls with data from subforms
I had this happen this week in a similar scenario (moving
to Access 2003 and Windows XP from Access 2002 and Windows 2000). First try "compact and repair." If that does not clear up the problem, then rebuild the database as follows: Create a blank database Import all the objects (tables, queries, forms, reports, macros, modules) from the old database. I did these things and it fixed my problem. -----Original Message----- Hi, We have an application where one of the forms has several calculated fields. Some of the data comes from subforms, some use data in other calculated fields. The subforms are linked to the main form on the invoice number. This has worked for years, moving from Access 95 through 98 to 2000, but one client bought new computers running Win XP Home Edition and with Office XP, and now the calculated fields show up blank.. It appears to be an Access 2003 problem, because it works fine on Access 2000 machines running Win 98, 2000 and XP. I am wondering if it is a timing problem, since some calculated controls use the contents of other calculated controls. I also notice that when I move the main form from one record to the next, the controls takes a second or two to update. The new machines are also fast, 2.7 GHz. Main form, Invoice, based on the Invoice Header table, has several subforms, one of which is the Invoice Detail subform, based on the Invoice Detail table. .. The main form has one calculated control called SubTotal with the ControlSource as follows: =Nz([Forms]![Invoice]![Invoice Detail subform].Form! OrderTotal) where OrderTotal, in the form footer in this subform, is the sum of all invoice line item amounts. It has another calculated control called CalSalTax with the following ControlSource: =(Nz([SubTotal])*Nz([STRATE]))/100 where SubTotal is the first calculated control. There is another calculated control called CalTotal with the following ControlSource: =[SubTotal]+IIf(IsNull([CalSalTax]),0,[CalSalTax]) which depends on the two previous calcualted controls, SubTotal and CalSalTax. There is another calculated control, CalDeposits, which uses data from another subform, the ControlSource is: =IIf(Not IsNull([Child127].Form!tcal_Sum_Payment),[Child127].Form! tcal_Sum_Payment,0) where tcal_Sum_Payment in the form footer is the sum of all deposits in this subform Then there is a calculated control called CalIntPaid Installation paid) whith the following ControlSource: =IIf([InstalISpaid]=-1,Nz([INSTALCOST]),0) where InstalISpaid and INSTALCOST are based controls on the main form. Finally, there is the calculated control called DueStore with this ControlSource: =Nz([caltotal])-Nz([caldeposits])-Nz([CalIntPaid]) where caltotal, caldeposits and CalIntPaid are the calculated controls above. I tried calculating the second calculated control directly, but then all calculated controls show #Error. I am thinking about trying to calculate all the controls directly, but would like some opinions. Any help would be appreciated. Ragnar . |
#3
|
|||
|
|||
Problem with calculated controls with data from subforms
Hi Ragner
I have had a similiar problem and only with 2003. Calculated fields that displayed ok in A97, 2000 & 2002 didn't work in 2003 and that some calculated controls showed blank until I moved the mouse over them. Apparently there seems to be many display problems in 2003 but to correct the problem in my case I carefully inserted a Me.Recalc in certain processes which seemed to fix the problem by "forcing" a recalculation of the controls. I don't know if this is helpful but just in case it is I thought I would mention it. -- Regards Ian Baker (If a=ian, b=jackaroo, c=net, d=au then me= ) - "Ragnar Midtskogen" wrote in message ... Hi, We have an application where one of the forms has several calculated fields. Some of the data comes from subforms, some use data in other calculated fields. The subforms are linked to the main form on the invoice number. This has worked for years, moving from Access 95 through 98 to 2000, but one client bought new computers running Win XP Home Edition and with Office XP, and now the calculated fields show up blank.. It appears to be an Access 2003 problem, because it works fine on Access 2000 machines running Win 98, 2000 and XP. I am wondering if it is a timing problem, since some calculated controls use the contents of other calculated controls. I also notice that when I move the main form from one record to the next, the controls takes a second or two to update. The new machines are also fast, 2.7 GHz. Main form, Invoice, based on the Invoice Header table, has several subforms, one of which is the Invoice Detail subform, based on the Invoice Detail table. . The main form has one calculated control called SubTotal with the ControlSource as follows: =Nz([Forms]![Invoice]![Invoice Detail subform].Form!OrderTotal) where OrderTotal, in the form footer in this subform, is the sum of all invoice line item amounts. It has another calculated control called CalSalTax with the following ControlSource: =(Nz([SubTotal])*Nz([STRATE]))/100 where SubTotal is the first calculated control. There is another calculated control called CalTotal with the following ControlSource: =[SubTotal]+IIf(IsNull([CalSalTax]),0,[CalSalTax]) which depends on the two previous calcualted controls, SubTotal and CalSalTax. There is another calculated control, CalDeposits, which uses data from another subform, the ControlSource is: =IIf(Not IsNull([Child127].Form!tcal_Sum_Payment),[Child127].Form!tcal_Sum_Payment,0) where tcal_Sum_Payment in the form footer is the sum of all deposits in this subform Then there is a calculated control called CalIntPaid Installation paid) whith the following ControlSource: =IIf([InstalISpaid]=-1,Nz([INSTALCOST]),0) where InstalISpaid and INSTALCOST are based controls on the main form. Finally, there is the calculated control called DueStore with this ControlSource: =Nz([caltotal])-Nz([caldeposits])-Nz([CalIntPaid]) where caltotal, caldeposits and CalIntPaid are the calculated controls above. I tried calculating the second calculated control directly, but then all calculated controls show #Error. I am thinking about trying to calculate all the controls directly, but would like some opinions. Any help would be appreciated. Ragnar |
#4
|
|||
|
|||
Problem with calculated controls with data from subforms
Thank you Chaplain Doug,
I tried both at the client's computer, to no avail. If I set the focus to each of the calcualted controls the numbers show, so I might try a routine that sets the focus. Another possiblity is to fill the controls from a recordset. The problem is, I have some reports that show the same problem, so either approach could involve some work. I think I will talk the client into getting 2002 on EBay (:-)). Ragnar |
#5
|
|||
|
|||
Problem with calculated controls with data from subforms
Thank you Ian,
This is my first contact with Access 2003, it is somewhat of a relief to hear that others have had problems too. Please see my reply to Chaplain Doug. In my case it is not enough to move the mouse over the controls, I have to set the focus to get any reaction.. I have thought about using some recalcs, and I will try that too, it should be the least likely to cause other problems. The problem in this application is that I am already using a lot of the form's events for other purposes, so I am worried I might trip up something else. For example, I tried a Requery for the main form in the OnRecord event and ended up with an oscillating form, probably because the Requery triggered the OnRecord event. Ragnar |
#6
|
|||
|
|||
Problem with calculated controls with data from subforms
Thanks again Ian,
Recalc did the trick, no apparent side effects! Ragnar |
#7
|
|||
|
|||
Problem with calculated controls with data from subforms
Hello Ian,
Just in case you are interested, I had to make a change. The Recalc apparently causes the form to be requeried and that tripped me up. I have a case where I go to this form from another form, and I move to a specific record. The requery messed this up. To get around this I tried just moving the focus to the blank controls and that worked. I had to set the focus to one of them twice, but it seems to be working. Ragnar |
#8
|
|||
|
|||
Problem with calculated controls with data from subforms
Hi Ragnar
Interesting as recalc does not cause the form to requery under standard circumstances. From the help on Recalc: "The Recalc method immediately updates all calculated controls on a form. Using this method is equivalent to pressing the F9 key when a form has the focus. You can use this method to recalculate the values of controls that depend on other fields for which the contents may have changed." Both Refresh and Requery do cause the form to requery with refresh having limitations in that it does not include deleted or new records. So there must be other processes happening that a recalc invokes resulting in the form being requeried which is why I mentioned placing a recalc "carefully" may resolve your issue. Having said all this it is hard to see exactly the whole picture. If you want you could strip out only the components that are in question into a new database, zip it up and email it to me for a look and I will see what I can do. If it helps what I use to open another form and display a selected record is: Scenario - A Purchase Order form and an Account Payable form. The account payable table has a field that contains the recordID of the purchase order that was used for the account payble record 1. Create a label on the Account Payable form 2. Make it a hyperlink by simply placing a space in the hyperlink address property 3. Place code in the OnClick event of the label (see step 4 for the reason of why I have included acDialog, "Select Record" in the following code): Dim stLinkCriteria As String stLinkCriteria = "[PurchaseOrderID]=" & Me!PurchaseOrderID DoCmd.OpenForm "Purchase Order", , , stLinkCriteria, , acDialog, "Select Record" 4. If you want only the relevant record to be displayed and nothing else then place the following code in the OnOpen event of the Purchase Order form: If Me.OpenArgs = "Select Record" Then Me.AllowAdditions = False 5. If there are a lot of calculated controls on the Purchase Order form and with the display problems of 2003 you would then insert a Me.Recalc in the OnLoad event of the Purchase Order form. My fingers were getting cold which is why I thought I would type the above :-) but an example of the above can be seen in my JackarooIT product on my website: If a=jackaroo, b=net, c=au then the web site is a.b.c Anyway I hope this helps and send me your problem if you want to. -- Regards Ian Baker (If a=ian, b=jackaroo, c=net, d=au then me= ) - "Ragnar Midtskogen" wrote in message ... Hello Ian, Just in case you are interested, I had to make a change. The Recalc apparently causes the form to be requeried and that tripped me up. I have a case where I go to this form from another form, and I move to a specific record. The requery messed this up. To get around this I tried just moving the focus to the blank controls and that worked. I had to set the focus to one of them twice, but it seems to be working. Ragnar |
#9
|
|||
|
|||
Problem with calculated controls with data from subforms
Thank you Ian,
I really appreciate your ideas on this. So far my latest fix seems to be working so I haven't worried about it. But, I don't like it when something does not work the way it ought too, so I may take another look at it when I get some time. But, Access' events can be a real minefield, and the main form uses many of them: On Current, Before Update, After Update, On Open, On Activate and On Got Focus, so there are a potential for mayhem here. If I get some spare time I might take you up on your offer, .but I just got through repairing a corrupted back-end file for another of the stores, so I am way behind right now. Windows on their server died and in the process it really did a number on the back-end. BTW, I did not design this application, my boss did and several other people have contributed along the line, in most cases by adding features or fixing problems, but without time to really figure out how it works. Ragnar |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Subreport 'pushes down' data next to it | Fred | Setting Up & Running Reports | 3 | June 30th, 2004 06:13 PM |
Problem creating a Data Access page | Ron | General Discussion | 0 | June 28th, 2004 09:30 PM |
Problem using subforms in a multiple database project | David G. | Using Forms | 0 | June 10th, 2004 04:12 PM |
Problem in Data area of Pivottable | Michael | Setting up and Configuration | 0 | February 5th, 2004 03:40 PM |
data query problem | aaron | Worksheet Functions | 3 | December 22nd, 2003 12:24 AM |