G
Guest
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.
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 said: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 said: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