Accounting system query

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 :-)

FJ,

FJquestioner wrote:
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.

When I run the [Receipts Subform Invoices and Balances] query using the
criteria you suggested, I get 11 records, 1 each for 11 different
Invoices. However, in none of these 11 records is there anything in the
RECEIPTID field. I guess this is because there is no entry in the
Receipt Allocations table for any of these 11 Invoices? Well, RECEIPTID
is supposed to be the basis of the relationship between the main form
and the subform, according to the Link Master Fileds / Link Child Fields
properties. So the subform will not show any records. Even if there
were linking RECEIPTID data, it seems to me that the subform would
(almost?) always only show one record. I am not sure exactly, but there
seems to be a fundamental flaw in the logic here somewhere.

I tried it like this...
1. Remove the three [Forms]![Receipts]!... criteria from the [Receipts
Subform Invoices and Balances] query.
2. Set the subform's Link Master Fileds / Link Child Fields properties to:
ASSIGNORID;DEBTORID;CURRENCYID

The result of this, of course, is for the subform to show the 11
Receipts Subform Invoices and Balances records whenever the main form
contains the matching data. So this applies for example to main form
records 25-31. And one record (Invoice 4698) is shown in the subform
for all main form records where Debtor is SB and Assignor is Lifemed. I
am not sure if this is really what you are after - if so, it seems
strange to me to have the subform showing the same records for multiple
main form records, but hey - it's your database :-).

--
Steve Schapel, Microsoft Access MVP

Once again you've bailed me out! I can't believe I missed the obvious error
of having the RECEIPTID field as the parent/child relationship!! I had tried
doing what you did by using the DEBTORID, ASSIGNORID and CURRENCYID as the
parent/child relationship but foolishly I left the RECEIPTID field in there
too so naturally it didn't work.

So by simply removing the RECIEPTID from the parent/child relationship the
form works just fine and most importantly, it no longer crashes !!!

I feel like a complete idiot but it sure is nice to have an objective set of
eyes looking at things because sometimes its easy to miss the forest for the
trees.

So thanks once again.

BTW, I saw your profile on the MVP webiste and I notice that you live in NZ.
What a great place that is! I visited there for 3 weeks back in 2002 while
living in Tokyo and absolutely loved it. In fact I made several steps toward
seeking immigration papers but in the end decided to return to my homeland of
Canada. However, I still think I might end up there some day.....


"Steve Schapel" wrote:

FJ,

FJquestioner wrote:
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.

When I run the [Receipts Subform Invoices and Balances] query using the
criteria you suggested, I get 11 records, 1 each for 11 different
Invoices. However, in none of these 11 records is there anything in the
RECEIPTID field. I guess this is because there is no entry in the
Receipt Allocations table for any of these 11 Invoices? Well, RECEIPTID
is supposed to be the basis of the relationship between the main form
and the subform, according to the Link Master Fileds / Link Child Fields
properties. So the subform will not show any records. Even if there
were linking RECEIPTID data, it seems to me that the subform would
(almost?) always only show one record. I am not sure exactly, but there
seems to be a fundamental flaw in the logic here somewhere.

I tried it like this...
1. Remove the three [Forms]![Receipts]!... criteria from the [Receipts
Subform Invoices and Balances] query.
2. Set the subform's Link Master Fileds / Link Child Fields properties to:
ASSIGNORID;DEBTORID;CURRENCYID

The result of this, of course, is for the subform to show the 11
Receipts Subform Invoices and Balances records whenever the main form
contains the matching data. So this applies for example to main form
records 25-31. And one record (Invoice 4698) is shown in the subform
for all main form records where Debtor is SB and Assignor is Lifemed. I
am not sure if this is really what you are after - if so, it seems
strange to me to have the subform showing the same records for multiple
main form records, but hey - it's your database :-).

--
Steve Schapel, Microsoft Access MVP

Very good, Jeff. Best wishes with the rest of the project.

Glad you liked New Zealand. I am actually Australian, but have been
living here since 1976. My daughter has just spent a year in Canada
(Calgary), and it was a great experience for her.

--
Steve Schapel, Microsoft Access MVP

FJquestioner wrote:
Once again you've bailed me out! I can't believe I missed the obvious error
of having the RECEIPTID field as the parent/child relationship!! I had tried
doing what you did by using the DEBTORID, ASSIGNORID and CURRENCYID as the
parent/child relationship but foolishly I left the RECEIPTID field in there
too so naturally it didn't work.

So by simply removing the RECIEPTID from the parent/child relationship the
form works just fine and most importantly, it no longer crashes !!!

I feel like a complete idiot but it sure is nice to have an objective set of
eyes looking at things because sometimes its easy to miss the forest for the
trees.

So thanks once again.

BTW, I saw your profile on the MVP webiste and I notice that you live in NZ.
What a great place that is! I visited there for 3 weeks back in 2002 while
living in Tokyo and absolutely loved it. In fact I made several steps toward
seeking immigration papers but in the end decided to return to my homeland of
Canada. However, I still think I might end up there some day.....

Steve,

On the bottom of my Receipts form I have a button which opens a different
form called NFI. I'd like to create a macro such that each time the NFI form
is closed, it triggers a requery of the query underlying the Receipts subform.

However, a macro that is triggered by the close of the NFI form will only
manipulate fields within that same form.

How would I set this up?

Thanks,

Jeff


"Steve Schapel" wrote:

Very good, Jeff. Best wishes with the rest of the project.

Glad you liked New Zealand. I am actually Australian, but have been
living here since 1976. My daughter has just spent a year in Canada
(Calgary), and it was a great experience for her.

--
Steve Schapel, Microsoft Access MVP

FJquestioner wrote:
Once again you've bailed me out! I can't believe I missed the obvious error
of having the RECEIPTID field as the parent/child relationship!! I had tried
doing what you did by using the DEBTORID, ASSIGNORID and CURRENCYID as the
parent/child relationship but foolishly I left the RECEIPTID field in there
too so naturally it didn't work.

So by simply removing the RECIEPTID from the parent/child relationship the
form works just fine and most importantly, it no longer crashes !!!

I feel like a complete idiot but it sure is nice to have an objective set of
eyes looking at things because sometimes its easy to miss the forest for the
trees.

So thanks once again.

BTW, I saw your profile on the MVP webiste and I notice that you live in NZ.
What a great place that is! I visited there for 3 weeks back in 2002 while
living in Tokyo and absolutely loved it. In fact I made several steps toward
seeking immigration papers but in the end decided to return to my homeland of
Canada. However, I still think I might end up there some day.....

Jeff,

That is not correct. You can specify anything you like in the Control
Name argument of the Requery action, for example
[Forms]![Receipts]![NameOfSubform]

--
Steve Schapel, Microsoft Access MVP

FJquestioner wrote:
Steve,

On the bottom of my Receipts form I have a button which opens a different
form called NFI. I'd like to create a macro such that each time the NFI form
is closed, it triggers a requery of the query underlying the Receipts subform.

However, a macro that is triggered by the close of the NFI form will only
manipulate fields within that same form.

How would I set this up?