Dynamically create n fields based on user input

[Marvo]

Hi all - hoping you can help....



I run a charity pre-school and have invoices to send out. Some of the parents cant afford to pay the whole amount in one go, so we give them a payment plan. e.g. If an invoice is for a 6 week term we let them pay weekly. So I have a check box on my form to say "are you on a payment plan".
Then - I have an free form input box..."How many payments..." and the answer may for instance be '6'.

What I want to do now is dynamically create/display 6 date fields, to record what the agreed payment dates are.

but maybe the answer is 4, or 7, etc. So I need to create/display the correct number of payment date fields. And I'm just not that clever!

Thanks for your assistance
M

[June7]

That is not a practical approach. To even try to accomplish would involve VBA that modifies table/form/report design.

What you can maybe do is have a fixed number of textboxes (maybe 10) on form/report and then expression in each textbox calculates a value based on the payment plan.

[orange]

I agree that your approach is questionable. I recommend you define what you are trying to do, build a model and do some testing before getting into the nuts and bolts of Access. You need a plan -something you can describe in plain English.
Instead of thinking -- I need "...create/display the correct number of payment date fields..",(spreadsheet ) I suggest you adjust to Payment, PaymentDate with a record for each Payment(database).
You know the details better than readers do.
Good luck.

[Marvo]

Thanks chaps. Have gone with the fixed number of textboxes approach for payment dates and a datasheet subform to record the payments.
I was trying to make the system foolproof for non IT minded folks. If there are only 6 payments I wanted to make sure they don't put in 7 payment dates! Would be nice to dynamically create the right number of boxes but never mind. Thanks for your input and help.

[Micron]

You could have a main form for the person who owes with a subform that you limit to n records. I have done this with a bill of lading form that had the main data plus a subform datasheet that I needed to limit to a maximum of 6 rows. I think this approach is more normalized than what you are doing (the subform details were in a separate table). You could even control the number of subform rows using a control on the main form.