none
Form with subform permanently showing "Calculating" in status bar RRS feed

  • Question

  • Hello,

    I have a main form containing two sub-forms. The main form is unbound. Subform 1 is related to table 1; Subform 2 to Table 2.
    The tables contains the following values: Table 1 have an X-ID, a field description, a field with date, etc. Each record is unique. (similar to orders). Table 2 has one or more subrecords of each record in Table 1 (compare with order details). Nu toont subform 1 de records uit tabel 1. Subform 2 is with a control field associated with the first sub form, so that subform 2 shows only the records with the same X-ID of the first subform. Subformulier 2 is based on a cumulative query with group totals. I made a count in the formfooter of subform 2, which I take over to the main form. When I open the main form, all information is displayed correctly. In the status bar constantly is schowing the message "Calculate". Only when I remove the counting fields in the formfooter of subform 2 the message "Calculating" disappears.

    How can I fix this? I need to use the total counts, thus permanently removing the census is no solution.

    I use Ms Access 2010

    Kind Regards,

    Henk 

    Wednesday, September 23, 2015 6:23 PM

Answers

  • Hello Tom,

    Thanks for your effort! This has helped me in the right direction. I'm already behind whereby the problem is caused.
    It's not because I'm the "Control" use, but because the condition in this field causes the error. Because I load in the main form the subforms dynamic, depending on the user's choice, I thought I would do well to make dependent the link field ("Control") or the "Source Object" exists or not. ''; [Subfrm01] [Form] [VerzdExp_ID]; 0) "This is to prevent error. I've used this before with success, without problems. However there was about normal selection queries. It is strange that it only goes wrong when the subform is based on a group/summing-qyery, as currently in sf2.
    I suspect it's because I so an IIF function field use "Control". Is this explained? Can I prevent this and still use a dependent condition in this field?
    You have to see this against the background that I have quite a large database application designed where what I run up against the limits of open database links (Error #: 3048). That's why I me something into this matter and thought to have a possible solution by working more with dynamic forms, which subforms are loaded only when there is real need. In this way, the number of open links can be significantly reduced. Only now you bump into other things, such as the problem: how do I then get a "dynamic" control field? IIF if I can not use, I'm afraid I have another problem. Would you herein once again like to think with me? Possibly you have an appropriate solution from your experience for me?
    I have my original dbtest adjusted by adding "-01" and put back into dropbox available so you can inspect it. (Same link as yesterday)

    The only solution I see currently is, the condition in the Control field also change dynamically depending on the source object of subfrm-1, something like:

    Select Case Nz(Me.Subfrm01.SourceObject, "")
    Case "x"
        Me.Control.ControlSource = "=[Subfrm01].[Form]![VerzdExp_ID]"
    Case "y"
        Me.Control.ControlSource = "=[Subfrm02].[Form]![Other_ID]"
    Case Else
        Me.Control.ControlSource = "=[Subfrm01].[Form]![VerzdExp_ID]"
    End Select

    Especially thank you for your thinking so far. Hopefully you can help me even further.

    Kind Regards,

    Henk





    Henk,

    To solve your issue with dynamically chaning the sub-forms, what you might consider is using a universal ID name in your sub-form 1 record source by setting an alias in the underlying query.  You could call it something like MasterID.  You can do this in all forms that will be shown in the sub-form 1 control.  Then in your sub-form 2.Parent property, you would set the value to "[VRZDEXPDHfdSub].[Form]![MasterID]." 

    In your forms that will be displayed in the sub-form 2 control, do the same thing, create a universal alias for the associated child id in the underlying query.

    Examples:

    Sub-Form 1 Record Source:

    SELECT VerzendExp.VerzdExp_ID AS MasterID, VerzendExp.Lidgroepnr, LidgroepVRD.Lidgroep, VerzendExp.VRKCatID, VerkoopCategorie.VRKCat_Omschrijving, VerzendExp.Boekjaar, VerzendExp.Expeditiedatum, VerzendExp.Omschrijving, Nz([SomVanAantal_Netto],0) AS Nettototaal, Nz([SomVanAantal_Extra],0) AS Extratotaal, Nz([SomVanAantal_Bruto],0) AS Brutototaal
    FROM ((((VerzendExp LEFT JOIN LidgroepVRD ON VerzendExp.Lidgroepnr = LidgroepVRD.Lidgroepnr) LEFT JOIN VerkoopCategorie ON VerzendExp.VRKCatID = VerkoopCategorie.VRKCatID) LEFT JOIN (SELECT VerzendExpCumExempl.VerzdExp_ID, Sum(VerzendExpCumExempl.Aantal_Netto) AS SomVanAantal_Netto FROM VerzendExpCumExempl GROUP BY VerzendExpCumExempl.VerzdExp_ID)  AS Tbl1 ON VerzendExp.[VerzdExp_ID] = Tbl1.[VerzdExp_ID]) LEFT JOIN (SELECT VerzendExpCumExempl.VerzdExp_ID, Sum(VerzendExpCumExempl.Aantal_Extra) AS SomVanAantal_Extra FROM VerzendExpCumExempl GROUP BY VerzendExpCumExempl.VerzdExp_ID)  AS Tbl2 ON VerzendExp.[VerzdExp_ID] = Tbl2.[VerzdExp_ID]) LEFT JOIN (SELECT VerzendExpCumExempl.VerzdExp_ID, Sum(VerzendExpCumExempl.Aantal_Bruto) AS SomVanAantal_Bruto FROM VerzendExpCumExempl GROUP BY VerzendExpCumExempl.VerzdExp_ID)  AS Tbl3 ON VerzendExp.[VerzdExp_ID] = Tbl3.[VerzdExp_ID];

    Sub-Form 2 Record Source:

    SELECT VerzendExpCumExempl.VerzdExp_ID AS ChildID, VerzendExpCumExempl.VERZEND_ID, Verzendwijze.VERZEND_Omschrijving, Sum(VerzendExpCumExempl.Aantal_Netto) AS SomVanAantal_Netto, Sum(VerzendExpCumExempl.Aantal_Extra) AS SomVanAantal_Extra, Sum(VerzendExpCumExempl.Aantal_Bruto) AS SomVanAantal_Bruto
    FROM VerzendExpCumExempl LEFT JOIN Verzendwijze ON VerzendExpCumExempl.VERZEND_ID = Verzendwijze.VERZEND_ID
    GROUP BY VerzendExpCumExempl.VerzdExp_ID, VerzendExpCumExempl.VERZEND_ID, Verzendwijze.VERZEND_Omschrijving;


    Hope this is helpful to you.

    Regards,

    RM



    Saturday, September 26, 2015 3:06 PM
  • Hello Edward,

    By Tom pointed in the right direction, I have found the cause actually own the hang of ms access in 'Calculate'. Using the IIF condition in a link field on the main form causes this when the subform is a GROUPING / Summing query. Then RunningManHD has several good options, which I've used to solve this problem.
    I have removed in the mainform in the "thecontrol" field (LINKFLD) and solved by the suggestion of RunningManHD. so:
    Subform 2 direct link to the subform 1 and put in the OnCurrent of subfrm 1 requery a commission (Me.Parent.Subform2.Requery)
    When a subform is based on a normal select query, the linkfield on the mainform can be used, although the suggestion of RunningManHD is faster (Requery).

    Hopes i help you

    Kind regards,

    Henk

    • Marked as answer by HenkK64 Wednesday, September 30, 2015 9:09 AM
    Wednesday, September 30, 2015 9:08 AM

All replies

  • Typically "Calculating" would give way to "Ready" (Klaar? in Dutch) once the calculations are done. Do you perhaps have a circular calculation where one formula depends on the result of another which depends on the first? Or perhaps there is VBA code involved that causes events to re-run continuously. I don't think we can find this out without you posting a version of your form, stripped down to the bare essentials, to a public place like a free OneDrive account.

    It appears from your description you're doing everything right, but obviously there must be a bug somewhere.


    -Tom. Microsoft Access MVP

    Thursday, September 24, 2015 3:26 AM
  • Hello Tom,

    I am very grateful that you want to help me.

    Have from my existing db copied the essentials for a test db. Form 2 has two subforms: Subformulier 1 is based on a normal select query. The total of which is neatly displayed. Form 2 is based on a sum qyery. Then it goes wrong. 'Calculate' remains constantly in the bar at the bottom of the window and it is indeed counted continuously.

    I work in my apps often counts, but mostly as with frm 1. In this case, however, I have the totals have a shipping method as form 2 do this, then I want to give the user a total. I think I make a mistake. Now the forms and code to a totally empty db copied and the problem also occurs there, I am afraid that there is a bug. Are you very curious findings. See you accommodate reaction with interest.

    Kind regards,

    Henk

    I have insert the link to my db
    Friday, September 25, 2015 10:24 AM
  • Link is missing.

    -Tom. Microsoft Access MVP

    Friday, September 25, 2015 1:05 PM
  • Tom i cannot put the link in this message.

    Can you help me? I get a message when a want to drop the link in this message that body message can not contain links there they can't verify my account. But where can i do this??

    Henk

    Friday, September 25, 2015 1:34 PM
  • Tom,

    Participation in this forum is free anyway?

    At first I thought it was thus something went wrong because it really is not possible to insert a link in the message. But what Internet search found more people to have this problem. One way or another, an account must be verified first. Only thing I can do it itself is not clear to me. I really do not know what I should do now to get this resolved.
    I would link to you can e-mail?

    Kind regards,

    Henk

    Friday, September 25, 2015 3:44 PM
  • https://www.dropbox.com/sh/fi5ktc0qmmt0o2h/AAB85azP1XXN84n93V3fssD4a?dl=0

    Friday, September 25, 2015 6:44 PM
  • Tom,

    I had not seen the verification email in my mailbox. Just found. Everything is fixed now. Sorry, Tom, for the inconvenience.
    Here still the link to the test db. I'm very curious in your opinion.
    Thanks in advance!  https://www.dropbox.com/sh/fi5ktc0qmmt0o2h/AAB85azP1XXN84n93V3fssD4a?dl=0

    kind regards,

    Henk

    Friday, September 25, 2015 6:48 PM
  • I got the file and will take a look.

    -Tom. Microsoft Access MVP

    Saturday, September 26, 2015 2:09 AM
  • Got it. See new version here: http://1drv.ms/1iB2rq6

    I shortened the object names for readability only. Then created new parent form _frmTest_sf1_sf2

    The only real thing I changed is the control name from "Control" to "theControl" since the former is a Reserved Word. That made it work in my test form.

    Alas, not in your form - don't know why in the hour or so I worked on this, but at least I am showing a way forward.


    -Tom. Microsoft Access MVP

    Saturday, September 26, 2015 3:11 AM
  • Got it. See new version here: http://1drv.ms/1iB2rq6

    I shortened the object names for readability only. Then created new parent form _frmTest_sf1_sf2

    The only real thing I changed is the control name from "Control" to "theControl" since the former is a Reserved Word. That made it work in my test form.

    Alas, not in your form - don't know why in the hour or so I worked on this, but at least I am showing a way forward.


    -Tom. Microsoft Access MVP

    Hi Tom,

    Are you saying that you were able to get rid of the problem in your test?  I downloaded both his DB and yours and both consistently say Calculating....  In fact, once you open one of the forms that causes this, no matter what else you open in this file or any other, all forms come up showing the same thing in the status bar.  In order for the problem to go away, Access has to be closed and re-opened. 

    I did some further testing by first opening one of his forms, then closing it.  I then created a new blank form with no record source and saved it as Form1.  When I open Form1, like all others, it shows calculating.  I then closed the database and opened it back up.  Then I tried opening Form1 again to see normal results in the status bar.  Then I opened one of his forms again and close it.  Then opened Form1, and again, Calculating....

    The next test I did was to close the database and reopen it.  I then opened Form1 in design view and copied all the controls from his second subform.  I then saved the form and closed it.  Then I re-opened it, and again, Calculating.... 

    So now I closed the database and reopened it.  Opened Form1 in design view, removed the control source Sum() in the footer.  Closed the form, then reopened it, all works normal.  Put the Sum() back, and again, Calculating....

    It doesn't bother me as much that this form says calculating with the Sum() involved, but when an empty form says it following having opened the other first, that's cause for some concern.

    I don't know what else to say on this... 

    Saturday, September 26, 2015 3:45 AM
  • My form _frmTest_sf1_sf2 does not exhibit the Calculating problem.

    -Tom. Microsoft Access MVP

    Saturday, September 26, 2015 4:27 AM
  • Hello Tom,

    Thanks for your effort! This has helped me in the right direction. I'm already behind whereby the problem is caused.
    It's not because I'm the "Control" use, but because the condition in this field causes the error. Because I load in the main form the subforms dynamic, depending on the user's choice, I thought I would do well to make dependent the link field ("Control") or the "Source Object" exists or not. ''; [Subfrm01] [Form] [VerzdExp_ID]; 0) "This is to prevent error. I've used this before with success, without problems. However there was about normal selection queries. It is strange that it only goes wrong when the subform is based on a group/summing-qyery, as currently in sf2.
    I suspect it's because I so an IIF function field use "Control". Is this explained? Can I prevent this and still use a dependent condition in this field?
    You have to see this against the background that I have quite a large database application designed where what I run up against the limits of open database links (Error #: 3048). That's why I me something into this matter and thought to have a possible solution by working more with dynamic forms, which subforms are loaded only when there is real need. In this way, the number of open links can be significantly reduced. Only now you bump into other things, such as the problem: how do I then get a "dynamic" control field? IIF if I can not use, I'm afraid I have another problem. Would you herein once again like to think with me? Possibly you have an appropriate solution from your experience for me?
    I have my original dbtest adjusted by adding "-01" and put back into dropbox available so you can inspect it. (Same link as yesterday)

    The only solution I see currently is, the condition in the Control field also change dynamically depending on the source object of subfrm-1, something like:

    Select Case Nz(Me.Subfrm01.SourceObject, "")
    Case "x"
        Me.Control.ControlSource = "=[Subfrm01].[Form]![VerzdExp_ID]"
    Case "y"
        Me.Control.ControlSource = "=[Subfrm02].[Form]![Other_ID]"
    Case Else
        Me.Control.ControlSource = "=[Subfrm01].[Form]![VerzdExp_ID]"
    End Select

    Especially thank you for your thinking so far. Hopefully you can help me even further.

    Kind Regards,

    Henk





    Saturday, September 26, 2015 8:54 AM
  • My form _frmTest_sf1_sf2 does not exhibit the Calculating problem.

    -Tom. Microsoft Access MVP

    Hi Tom,

    I just wanted to follow up and let you know that I was able to see your form work as expected.  What I had to do was reboot my machine which hadn't been rebooted for several weeks.  I know that there are some other issues related to leaving the machine and Access running for long periods like this.

    After the reboot, testing other forms, following the display of the OP's problem main form, did not result in the status bar showing the status "Calculating...," unlike they did before.

    I will continue to play with this a bit and report back with any other findings.

    Thanks,

    RM

    Saturday, September 26, 2015 1:35 PM
  • Hi Tom,

    Following up again.  It looks like you also changed the control source of theControl by removing the IIF() function.  That seems to be the real culprit here.  When it's there, the control is constantly re-evaluating the test, thus causing the status "Calculating..." to appear in the status bar.

    For all readers,

    Henk is using a Textbox control on the main form to show the id value of sub-form 1.  The Textbox control is named theControl here. 

    In the sub-form 2 control, the Parent property is set to "theControl."  The Child property is set to a related id in the underlying form.

    This works fine, but appears to be a little slow in the update.

    I recommend that sub-form 2.Parent be set to sub-form 1.related id directly.  In Henk's application this is "[VRZDEXPDHfdSub].[Form]![VerzdExp_ID]."  Then in the underlying form for sub-form 1, use the Form_Current() procedure to requery the Parent-form.sub-form 2. 

    Example:

    Private Sub Form_Current()
        
        On Error GoTo Form_Current_Err
        
        Me.Parent.VRZDEXPDHfdSub_CumVznd.Requery
            
    Form_Current_Exit:
        Exit Sub
        
    Form_Current_Err:
        Resume Form_Current_Exit
        
    End Sub

    This will run a bit faster than the previous method for updating the related sub-form.

    Hopefully this information is helpful to all.

    Best Regards,

    RM





    • Edited by RunningManHD Saturday, September 26, 2015 2:34 PM
    Saturday, September 26, 2015 2:27 PM
  • Hello Tom,

    Thanks for your effort! This has helped me in the right direction. I'm already behind whereby the problem is caused.
    It's not because I'm the "Control" use, but because the condition in this field causes the error. Because I load in the main form the subforms dynamic, depending on the user's choice, I thought I would do well to make dependent the link field ("Control") or the "Source Object" exists or not. ''; [Subfrm01] [Form] [VerzdExp_ID]; 0) "This is to prevent error. I've used this before with success, without problems. However there was about normal selection queries. It is strange that it only goes wrong when the subform is based on a group/summing-qyery, as currently in sf2.
    I suspect it's because I so an IIF function field use "Control". Is this explained? Can I prevent this and still use a dependent condition in this field?
    You have to see this against the background that I have quite a large database application designed where what I run up against the limits of open database links (Error #: 3048). That's why I me something into this matter and thought to have a possible solution by working more with dynamic forms, which subforms are loaded only when there is real need. In this way, the number of open links can be significantly reduced. Only now you bump into other things, such as the problem: how do I then get a "dynamic" control field? IIF if I can not use, I'm afraid I have another problem. Would you herein once again like to think with me? Possibly you have an appropriate solution from your experience for me?
    I have my original dbtest adjusted by adding "-01" and put back into dropbox available so you can inspect it. (Same link as yesterday)

    The only solution I see currently is, the condition in the Control field also change dynamically depending on the source object of subfrm-1, something like:

    Select Case Nz(Me.Subfrm01.SourceObject, "")
    Case "x"
        Me.Control.ControlSource = "=[Subfrm01].[Form]![VerzdExp_ID]"
    Case "y"
        Me.Control.ControlSource = "=[Subfrm02].[Form]![Other_ID]"
    Case Else
        Me.Control.ControlSource = "=[Subfrm01].[Form]![VerzdExp_ID]"
    End Select

    Especially thank you for your thinking so far. Hopefully you can help me even further.

    Kind Regards,

    Henk





    Henk,

    To solve your issue with dynamically chaning the sub-forms, what you might consider is using a universal ID name in your sub-form 1 record source by setting an alias in the underlying query.  You could call it something like MasterID.  You can do this in all forms that will be shown in the sub-form 1 control.  Then in your sub-form 2.Parent property, you would set the value to "[VRZDEXPDHfdSub].[Form]![MasterID]." 

    In your forms that will be displayed in the sub-form 2 control, do the same thing, create a universal alias for the associated child id in the underlying query.

    Examples:

    Sub-Form 1 Record Source:

    SELECT VerzendExp.VerzdExp_ID AS MasterID, VerzendExp.Lidgroepnr, LidgroepVRD.Lidgroep, VerzendExp.VRKCatID, VerkoopCategorie.VRKCat_Omschrijving, VerzendExp.Boekjaar, VerzendExp.Expeditiedatum, VerzendExp.Omschrijving, Nz([SomVanAantal_Netto],0) AS Nettototaal, Nz([SomVanAantal_Extra],0) AS Extratotaal, Nz([SomVanAantal_Bruto],0) AS Brutototaal
    FROM ((((VerzendExp LEFT JOIN LidgroepVRD ON VerzendExp.Lidgroepnr = LidgroepVRD.Lidgroepnr) LEFT JOIN VerkoopCategorie ON VerzendExp.VRKCatID = VerkoopCategorie.VRKCatID) LEFT JOIN (SELECT VerzendExpCumExempl.VerzdExp_ID, Sum(VerzendExpCumExempl.Aantal_Netto) AS SomVanAantal_Netto FROM VerzendExpCumExempl GROUP BY VerzendExpCumExempl.VerzdExp_ID)  AS Tbl1 ON VerzendExp.[VerzdExp_ID] = Tbl1.[VerzdExp_ID]) LEFT JOIN (SELECT VerzendExpCumExempl.VerzdExp_ID, Sum(VerzendExpCumExempl.Aantal_Extra) AS SomVanAantal_Extra FROM VerzendExpCumExempl GROUP BY VerzendExpCumExempl.VerzdExp_ID)  AS Tbl2 ON VerzendExp.[VerzdExp_ID] = Tbl2.[VerzdExp_ID]) LEFT JOIN (SELECT VerzendExpCumExempl.VerzdExp_ID, Sum(VerzendExpCumExempl.Aantal_Bruto) AS SomVanAantal_Bruto FROM VerzendExpCumExempl GROUP BY VerzendExpCumExempl.VerzdExp_ID)  AS Tbl3 ON VerzendExp.[VerzdExp_ID] = Tbl3.[VerzdExp_ID];

    Sub-Form 2 Record Source:

    SELECT VerzendExpCumExempl.VerzdExp_ID AS ChildID, VerzendExpCumExempl.VERZEND_ID, Verzendwijze.VERZEND_Omschrijving, Sum(VerzendExpCumExempl.Aantal_Netto) AS SomVanAantal_Netto, Sum(VerzendExpCumExempl.Aantal_Extra) AS SomVanAantal_Extra, Sum(VerzendExpCumExempl.Aantal_Bruto) AS SomVanAantal_Bruto
    FROM VerzendExpCumExempl LEFT JOIN Verzendwijze ON VerzendExpCumExempl.VERZEND_ID = Verzendwijze.VERZEND_ID
    GROUP BY VerzendExpCumExempl.VerzdExp_ID, VerzendExpCumExempl.VERZEND_ID, Verzendwijze.VERZEND_Omschrijving;


    Hope this is helpful to you.

    Regards,

    RM



    Saturday, September 26, 2015 3:06 PM
  • Hi RM,

    You give several interesting options that I next Monday to take a closer hope. I hope next week to respond further, after I did this for myself worked out and tested.You will hear from me.Thank youfor the contributions so far.

    Kind regards,

    Henk

    Saturday, September 26, 2015 4:57 PM
  • Hi RM,

    You give several interesting options that I next Monday to take a closer hope. I hope next week to respond further, after I did this for myself worked out and tested.You will hear from me.Thank youfor the contributions so far.

    Kind regards,

    Henk

    Hello RM,

    The reason why I am uring link fields (Control) on my mainforms to synchronize subforms was that I believe that in previous versions of ms access is was not possible to use a direct link from one subform to another subform. It also says the article of FMS: http://www.fmsinc.com/MicrosoftAccess/Forms/Synchronize/LinkedSubforms.asp.
    Now I have tested your suggestion. It works perfectly! Moreover, indeed, it works faster. The user will now hardly see that data in the subform 2 are updated when another record in subform 1 is selected.
    Thank you for your suggestions and help! It has helped me a lot!
    I continue today get to work.

    Kind regards,

    Henk

    Monday, September 28, 2015 8:39 AM
  • What about my problem with "Calculating": this is so really caused by including the IIF condition in the Link field (Control).
    This occurs only when the relevant subform has a GROUPING / Summing query.
    Whether this can be explained, I do not know. It is the cause of the problem that continues to calculate a form.

    Regards,

    Henk

    Monday, September 28, 2015 8:44 AM
  • Hi KenkK64,

    >> What about my problem with "Calculating":  this is so really caused by including the IIF condition in the Link field (Control).

    I am not sure whether your issue has been resolved. If your issue has been resolved, I suggest you mark the helpful reply or it would be appreciated if you could share us your solution.

    If your issue has not been resolved. It would be helpful if you could share us a simple steps to reproduce your issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, September 30, 2015 5:58 AM
  • Hello Edward,

    By Tom pointed in the right direction, I have found the cause actually own the hang of ms access in 'Calculate'. Using the IIF condition in a link field on the main form causes this when the subform is a GROUPING / Summing query. Then RunningManHD has several good options, which I've used to solve this problem.
    I have removed in the mainform in the "thecontrol" field (LINKFLD) and solved by the suggestion of RunningManHD. so:
    Subform 2 direct link to the subform 1 and put in the OnCurrent of subfrm 1 requery a commission (Me.Parent.Subform2.Requery)
    When a subform is based on a normal select query, the linkfield on the mainform can be used, although the suggestion of RunningManHD is faster (Requery).

    Hopes i help you

    Kind regards,

    Henk

    • Marked as answer by HenkK64 Wednesday, September 30, 2015 9:09 AM
    Wednesday, September 30, 2015 9:08 AM