Using multiple subforms

[magicdds]

I have a form with the following subforms:

Subform1 - has a list of procedures that a client was charged for and the
fee for each procedure

Subform2 - is linked to subform1. It has the following fields:
PaymentPerInterval
NumberOfPayments
OptionGroup: To select if payments are to be made Monthly, Quarterly,
Annually, or Custom payment plan.

Subform3 - is linked to subform2. It has the following fields:
IntervalNumber
Due Date
AmountDue

When you click on a command button in subform2, records are created in
subform3 for each monthly or quarterly payment, to pay off the charge in
subform1. Each record is filled with the approprite due date and amountdue.

This all works fine for monthly, quarterly, and annual payment options
because the entire fee is divided into equal payments all at once. The
problem is with the custom payment option.

If there are two charges in subform1, one for $300 and a second for $1000
and the user wants to schedule a payment plan for the $1000 charge, the user
types in 5 payments of $100 in subform2, then 5 records are created in
subform3. Next the user could type in subform2, 10 payments of $50, and that
would create 10 additional records in subform3, to pay off the balance of the
$1000. But if inbetween typing the information for the $100 payments and the
$50 payments in subform2, the user clicks on the $300 charge in subform1, the
records in subform2 & 3, while still saved, have not been completed. The user
should not be able to leave the creation of a custom payment plan until it is
completed.

How can I prevent the user from navigating away from the creation of a
custom payment plan, until it is completed? This goes for navigating to
subform1 or any other text boxes or pages on the form.

Thanks for any ideas.
Mark

 

[Jeanette Cunningham]

Hi,
You use the Before Update event of a form to check for missing data entries.
If there is missing data, your code does
Cancel = True
and that stops the form from saving the data entered in that form.

If you can work out how to know when a custom plan is complete, you can stop
the user from saving an unfinished custom plan by using the before update
event of the form that creates the custom plan.

Jeanette Cunningham

 

[magicdds]

The problem is not that I don't want the records saved. The problem is that I
don't want the user to be able to leave the subform until all of the custom
payment plan has been created. ( Sort of like you can't get out of a modal
popup form until your finished with it. But here we are dealing with
subforms.)

 

[John W. Vinson]

The problem is not that I don't want the records saved. The problem is that I
don't want the user to be able to leave the subform until all of the custom
payment plan has been created. ( Sort of like you can't get out of a modal
popup form until your finished with it. But here we are dealing with
subforms.)

The problem is that typically a Form is based on the "one" side table of a one
to many relationship, and the subform on the "many". If you are (as you should
be!) enforcing referential integrity between those tables, then the mainform
record must be saved before you can start entering data into the subform;
otherwise there is no parent record ID to serve as a link. It's a chicken and
egg problem - you say you want to not save the mainform record until all the
subform records are complete; but you can't create the subform records in the
first place until the mainform record is saved.

The only way to *solidly* enforce this entails a lot of extra work: you would
need to have a second set of tables mirroring your actual tables, and bind
your forms to these "scratch" tables. You would then have a command button on
the form to post the scratch table data into the main tables (using append
queries); this button's code could check to ensure that all of the tables have
been filled in.

If I'm misunderstanding the situation please post back with a bit more detail
about your forms and tables.

 

[magicdds]

The problem is not about saving the records. I do want the records saved and
they are being saved correctly.

Here is the problem:
There are charges listed in subform1. The user selects the charge that the
client will pay with a payment plan. The user then types in subform2:
pay $100 per month for 5 months.
5 records are created in subform3:
1 5/1/08 $100
2 6/1/08 $100
3 7/1/08 $100
4 8/1/08 $100
5 9/1/08 $100
Then the user types in subform2"
pay $50 per month for 2 Months.
2 reords are created in subform3:
6 10/1/08 $50
7 11/1/08 $50

If the total charge is $1000, only $600 has been distributed in subform3 in
7 records. There is still $400 not accounted for. If the user now clicks on a
different area on the main form or on a different charge on subform1,
subform2 no longer has the focus and the payment plan has not been completed.
I want to prevent the user from leaving subform2 until all the records that
are needed in subform3 have been created, so that the entire $1000 charge has
been accounted for.

Thanks

 

[Jeanette Cunningham]

This would be much easier with just one subform.
Store the amount and the date all on the same subform.
You can put an unbound textbox on the subform to add up all the payment
amounts and check this against the total charge.
This will be much easier to manage than several subforms.

Jeanette Cunningham

 

[magicdds]

Unfortunately, the form has quite a lot going on. This problem I am
describing has to do only with a small part of the picture. In order to use
the data for reports and other calculations, the form and subform design must
remain as is. So the problem still remains: How can I prevent the user from
leaving subform2 until all the records that are needed in subform3 have been
created, so that the entire $1000 charge has been accounted for?

 

[Jeanette Cunningham]

The only other way that comes to mind is to use a form opened in acDialog
mode to add all records to cover the $1000 charge.
Unfortunately this would also need alterations to your form and subform
setup.

Jeanette Cunningham

Unfortunately, the form has quite a lot going on. This problem I am
describing has to do only with a small part of the picture. In order to
use
the data for reports and other calculations, the form and subform design
must
remain as is. So the problem still remains: How can I prevent the user
from
leaving subform2 until all the records that are needed in subform3 have
been
created, so that the entire $1000 charge has been accounted for?

 

[John W. Vinson]

Unfortunately, the form has quite a lot going on. This problem I am
describing has to do only with a small part of the picture. In order to use
the data for reports and other calculations, the form and subform design must
remain as is. So the problem still remains: How can I prevent the user from
leaving subform2 until all the records that are needed in subform3 have been
created, so that the entire $1000 charge has been accounted for?

If you're not actually setting focus to subform3, but only filling it in using
VBA code (append queries or the like), then you could put code in Subform2's
BeforeUpdate event. It could look in the table behind subform3 (not the
subform itself) using a totals query to calculate the total payment, and
cancel the form update if the job's not done.