Closing an Empty Form
in [Queries]
|
From: Steve Schapel on 23 Sep 2006 17:13 Sleuthing's definitely right! I don't think I've encountered anything like this before. Just as an experiment, can you temporarily alter the query by removing the three criteria that reference the main form, i.e. [Forms]![Receipts]![DEBTORID] and the other two, and see what happens? -- Steve Schapel, Microsoft Access MVP FJquestioner wrote: > Steve, > > Not sure if this will help but this is what appears on the top of the error > report I get when the system crashes. > > AppName: msaccess.exe AppVer: 10.0.6771.0 ModName: msaccess.exe > ModVer: 10.0.6771.0 Offset: 000a90ad > > > To answer your questions... > > The problem occurs whether I close the main form using the X or the macro > driven Close button within the form. > > I don't know for sure that the system would prompt for all 3 of the > combo-box fields because as you noted, the system crashes after I cancel the > first prompt. However, if I re-arrange those fields within the query, the > prompt is for whichever field is left-most within the query (currently it > prompts for [Forms]![Receipts]![DEBTORID] ). > > Also, I recently bought a new computer and loaded Office onto it (including > Access). I was having the problem of the promt appearing upon closing the > form on my old computer but I don't recall it causing a system crash. > However, I've re-installed Office using the "repair" mode at least 3 times > and the crashing problem persists. > > I'm using WinXP and I also did a system restore at one point when I was > loading programs onto the new computer. That caused the Outlook program to > malfunction. However, the error notice identified the problem file (one of > the duplicate application files created by the restore function) and once I > deleted that file it worked fine. > > So I suppose its possible that the Prompt problem is a programming problem > and the crash problem could be a software bug from my installation. However, > all other features of Access and all other Office programs seem to be working > fine. > > But if I can eliminate the prompts then I needn't worry about the crashes. > > Does any of this help with yur sleuthing?
From: FJquestioner on 23 Sep 2006 17:53 I removed the 3 criteria as suggested and had no problem closing the form. If I removed them one at a time, the system crashed each time until all 3 were gone. Also, if I open the query itself (with the related forms closed) I get the same prompt for DEBTORID. However, when I click Cancel, the query closes with no problem. So the crash problem arises only when any of the 3 criteria are within the query and does not arise when the query is closed in isolation. ?????? "Steve Schapel" wrote: > Sleuthing's definitely right! I don't think I've encountered anything > like this before. Just as an experiment, can you temporarily alter the > query by removing the three criteria that reference the main form, i.e. > [Forms]![Receipts]![DEBTORID] and the other two, and see what happens? > > -- > Steve Schapel, Microsoft Access MVP > > FJquestioner wrote: > > Steve, > > > > Not sure if this will help but this is what appears on the top of the error > > report I get when the system crashes. > > > > AppName: msaccess.exe AppVer: 10.0.6771.0 ModName: msaccess.exe > > ModVer: 10.0.6771.0 Offset: 000a90ad > > > > > > To answer your questions... > > > > The problem occurs whether I close the main form using the X or the macro > > driven Close button within the form. > > > > I don't know for sure that the system would prompt for all 3 of the > > combo-box fields because as you noted, the system crashes after I cancel the > > first prompt. However, if I re-arrange those fields within the query, the > > prompt is for whichever field is left-most within the query (currently it > > prompts for [Forms]![Receipts]![DEBTORID] ). > > > > Also, I recently bought a new computer and loaded Office onto it (including > > Access). I was having the problem of the promt appearing upon closing the > > form on my old computer but I don't recall it causing a system crash. > > However, I've re-installed Office using the "repair" mode at least 3 times > > and the crashing problem persists. > > > > I'm using WinXP and I also did a system restore at one point when I was > > loading programs onto the new computer. That caused the Outlook program to > > malfunction. However, the error notice identified the problem file (one of > > the duplicate application files created by the restore function) and once I > > deleted that file it worked fine. > > > > So I suppose its possible that the Prompt problem is a programming problem > > and the crash problem could be a software bug from my installation. However, > > all other features of Access and all other Office programs seem to be working > > fine. > > > > But if I can eliminate the prompts then I needn't worry about the crashes. > > > > Does any of this help with yur sleuthing? >
From: Steve Schapel on 23 Sep 2006 18:21 FJ, Well, it makes sense that you would get the parameter prompts when you try to open the query, since the Receipts form is not open at the time, so the value of [Forms]![Receipts]![DEBTORID] cannot be evaluated. What I can't figure out is why Access is trying to evaluate the query when you close the form, and why the app crashes. Are you able to email the file to me? If so, zipped for preference, and send to steves at mvps dot org I'll see how it goes on my computer :-) -- Steve Schapel, Microsoft Access MVP FJquestioner wrote: > I removed the 3 criteria as suggested and had no problem closing the form. If > I removed them one at a time, the system crashed each time until all 3 were > gone. > > Also, if I open the query itself (with the related forms closed) I get the > same prompt for DEBTORID. However, when I click Cancel, the query closes with > no problem. > > So the crash problem arises only when any of the 3 criteria are within the > query and does not arise when the query is closed in isolation. >
From: Steve Schapel on 24 Sep 2006 14:52 FJ, Ok, I have looked at your database. I was not really able to run your application in its existing form, as there are References set to files I don't have for example MSPFCTL1, but I think I would have the same problem happening here. To be honest, I haven't been able to completely wade my way through your design at the moment. It is quite involuted. For example, you have a query [Receipts Subform Invoices & Balances] as the Row Source of a combobox on the subform, and this same query is the Record Source of the subsubform. I have used a subsubform in the Footer section of a subform before, but only by controlling the subsubform's data via the Link Master Fields and Link Child Fields properties - in other words the subsubform is directly related many-to-one to the subform. It is not clear whether this is the case with your subsubform. In fact, in the data you supplied, I could not find any example where there was any subform data at all related to any main form record. In my experience, it is very unusual to have a Record Source for a subform which is a query which specifically references main form controls in its criteria. It is true that when you close the main form, the main form unloads and closes before the subform, and the subform in turn before the subsubform. Still, I did not expect that this would cause a crash. There would be a kludgy way to work around the problem. You could use a SetValue action in a macro which is applied on the Unload event of the main form, to set the Record Surce of the subsubform to "" and this would solve the immediate problem. However, if it was mine, I would be re-visiting the overall design and concept of the form. Try to find a way such that the subform records are defined acccording to the value of the main form's primary key (or other unique index), and the subsubform records are defined acccording to the value of the subform's primary key (or other unique index). By the way, as an aside, I should mention that it is not a good idea to usew characters such as & or - or # as part of the name of fields or controls or database objects. -- Steve Schapel, Microsoft Access MVP Steve Schapel wrote: > FJ, > > Well, it makes sense that you would get the parameter prompts when you > try to open the query, since the Receipts form is not open at the time, > so the value of [Forms]![Receipts]![DEBTORID] cannot be evaluated. What > I can't figure out is why Access is trying to evaluate the query when > you close the form, and why the app crashes. > > Are you able to email the file to me? If so, zipped for preference, and > send to steves at mvps dot org I'll see how it goes on my computer :-) >
From: FJquestioner on 25 Sep 2006 16:15
Thanks for the comments Steve. I wasn't sure whether you were able to get the file running or not however, I'll assume so...... As you've no doubt surmised, the main form "Receipts" records cheques received from a given debtor to pay the invoices of a given company called an Assignor. What I was attemtping to do with the subform was extract the open invoices that matched the criteria of DEBTORID and ASSIGNORID from the main form and then calculate and display the original invoice amount, the total of any payments received to date and the current balance due for each such open invoice. (This is what the query "Receipts Subform Invoices & Balances" does). I would then allocate the portion of the total cheque receipt in the main form to the appropriate open invoices within the subform. I would then indicate whether that was the closing receipt and if so, whether the invoice had been paid in full or whether the final payment was incomplete (eg. the debtor took a discount). However, I was unable to figure out how to display within the subform, the original invoice amount, the total of any payments received to date and the current balance due for each open invoice other than through the unweildy combo-box approach. I'm unsatisfied with this current design, especially the fact that once an invoice is selected I can no longer see the Past Payments and Balance sections and once the cursor moves to the next invoice I can no longer see the invoice # within the combobox (which is why I had to insert the invoice number twice!). As for the subsubform, its sole purpose was to allow me to me double click in the Allocation field of the subform and have the outstanding balance automatically appear. This is because most of the time, the payments received for a given invoice are for its outstanding balance. I found myself having to type this number manually (wasting time and creating errors). Using a subsubform was the only way I could see doing this while maintaining the "continous form" format. So I've e-mailed you a re-designed form. The main Receipt form is the same as before. The subform no longer attempts to display the balance on open invoices. The subsubform displays the balance of the current invoice record and when I double click on the Allocation field in the subform, this balance is autpmatically inserted. My only problem is that the subform and subsubform work fine by themselves but when they are inserted into the Main form (using ReceiptID as the parent/child link) they don't work. I'm sure its something simple I'm missing but it escapes me. The data is such that DEBTORID "13 = TGH" and ASSIGNORID "13 = Lifemed" and CURRENCYID "1=CAD" will make the query and subforms work fine on their own. However, when these same values are entered into the main Receipts form I get nothing hapeening in the subforms. I hope this redesign (if I get it working) will resolve the crash problem while keeping the goals of the original design. Any insights you have would be greatly appreciated. Thanks again. "Steve Schapel" wrote: > FJ, > > Ok, I have looked at your database. I was not really able to run your > application in its existing form, as there are References set to files I > don't have for example MSPFCTL1, but I think I would have the same > problem happening here. > > To be honest, I haven't been able to completely wade my way through your > design at the moment. It is quite involuted. For example, you have a > query [Receipts Subform Invoices & Balances] as the Row Source of a > combobox on the subform, and this same query is the Record Source of the > subsubform. I have used a subsubform in the Footer section of a subform > before, but only by controlling the subsubform's data via the Link > Master Fields and Link Child Fields properties - in other words the > subsubform is directly related many-to-one to the subform. It is not > clear whether this is the case with your subsubform. In fact, in the > data you supplied, I could not find any example where there was any > subform data at all related to any main form record. > > In my experience, it is very unusual to have a Record Source for a > subform which is a query which specifically references main form > controls in its criteria. > > It is true that when you close the main form, the main form unloads and > closes before the subform, and the subform in turn before the > subsubform. Still, I did not expect that this would cause a crash. > > There would be a kludgy way to work around the problem. You could use a > SetValue action in a macro which is applied on the Unload event of the > main form, to set the Record Surce of the subsubform to "" and this > would solve the immediate problem. However, if it was mine, I would be > re-visiting the overall design and concept of the form. Try to find a > way such that the subform records are defined acccording to the value of > the main form's primary key (or other unique index), and the subsubform > records are defined acccording to the value of the subform's primary key > (or other unique index). > > By the way, as an aside, I should mention that it is not a good idea to > usew characters such as & or - or # as part of the name of fields or > controls or database objects. > > -- > Steve Schapel, Microsoft Access MVP > > Steve Schapel wrote: > > FJ, > > > > Well, it makes sense that you would get the parameter prompts when you > > try to open the query, since the Receipts form is not open at the time, > > so the value of [Forms]![Receipts]![DEBTORID] cannot be evaluated. What > > I can't figure out is why Access is trying to evaluate the query when > > you close the form, and why the app crashes. > > > > Are you able to email the file to me? If so, zipped for preference, and > > send to steves at mvps dot org I'll see how it goes on my computer :-) |