Is it possible to automatically create subforms from number selected in a form field?

[Remedial]

Okay, I've created a form which has a field for the data entry person to input the number of children that the perspective client has. I will also need the names and the DOB of each one of those children. Now, is it possible for a subform or subforms to be created with fields for the Last Name, First Name and DOB of the children in the exact number that ther person selects for the amount of children in the home?

I know it sounds confusing, so let me explain. So, after the data entry person puts, let's say, 3 kids in the home, either a subform with 3 records, maybe in datasheet view, for each one of the kids is created or 3 successive subforms are created (with each to be completed one after the other) for each one of the kids. The trick would be, the number of records (in the subform in datasheet view) or successive subforms would depend on the number selected in the "number of children in household" field.

I hope I've explained myself adequately. Any suggestions would be appreciated. Thank you.

 

[Remedial]

Did I maybe post this in the wrong forum? Please let me know. Or maybe I'm just asking for too much.

 

[georgedwilkinson]

I think you might not be getting responses because this request doesn't fit the mold of what a good request is, from the responder's point of view.

My answer is "yes". As you can tell, that is not a very good answer, which is why I didn't respond the first time I saw this thread.

Truth is, you'll need to do a goodly amount of VBA coding to make this come about. My assumption is that you probably don't know VBA well enough to do that (or you would be asking a different question). I further assume that my fellow's assumptions are the same. Which means that I, or the responder, would have to baby step you through the answer.

Unfortunately, most of us have real jobs that require us to be available to those who are paying us. Since my perception (again, by extension, my associates perception is assumed) is that this request will burn up a lot of time, nobody has been willing to take this on through completion.

Please don't be dismayed. Think of a better question or ask the one you've asked in a better way. Because my answer is "yes" as stated earlier. And as a followup, no, I cannot in the time available to me provide you with a complete solution.

 

[Mike375]

What George says is correct. It is extremely difficult to show someone something when at least the basics are not there.

I think what you probably want are extra records created in a subform and the number of records added to match the number entered for children.

An easy way to do that is with a macro that opens the form at a new record, then sets the value of the required field or fields with the data you want and the macro does this the number of times equal to the number entered for children. It would be done with a RunMacro action. That means a macro is used to run another macro and "conditions" in this the case the number entered for children determines how often it runs.

Search about on Google, Access Help for opening forms with a macro action and also SetValue action. Make a little macro like this and then you can save it as a module and see what it looks like in code.

 

[Remedial]

I think you might not be getting responses because this request doesn't fit the mold of what a good request is, from the responder's point of view.

My answer is "yes". As you can tell, that is not a very good answer, which is why I didn't respond the first time I saw this thread.

Truth is, you'll need to do a goodly amount of VBA coding to make this come about. My assumption is that you probably don't know VBA well enough to do that (or you would be asking a different question). I further assume that my fellow's assumptions are the same. Which means that I, or the responder, would have to baby step you through the answer.

Unfortunately, most of us have real jobs that require us to be available to those who are paying us. Since my perception (again, by extension, my associates perception is assumed) is that this request will burn up a lot of time, nobody has been willing to take this on through completion.

Please don't be dismayed. Think of a better question or ask the one you've asked in a better way. Because my answer is "yes" as stated earlier. And as a followup, no, I cannot in the time available to me provide you with a complete solution.

Understood. And I will admit that I do not have much VBA coding experience. Naturally, if this is an arduous task and would require a unique VBA code to be written (i.e., I could not glean the process from an example code that does something similar), I would not expect someone to hold my hand unless they are willing to do such. So, don't get me wrong, that is not my purpose here.

I am quite the autodidact and am more than willing to undertake learning the steps needed to complete this task on my own. At the least I would just ask for a finger pointing me in the right direction (i.e., a book, website, tutorial, etc...).

And, I can understand your concern over lack of time because I too have a "real job" and definitely would not be playing with Access as a hobby.

Thank you for your assistance.

 

[Remedial]

What George says is correct. It is extremely difficult to show someone something when at least the basics are not there.

I think what you probably want are extra records created in a subform and the number of records added to match the number entered for children.

An easy way to do that is with a macro that opens the form at a new record, then sets the value of the required field or fields with the data you want and the macro does this the number of times equal to the number entered for children. It would be done with a RunMacro action. That means a macro is used to run another macro and "conditions" in this the case the number entered for children determines how often it runs.

Search about on Google, Access Help for opening forms with a macro action and also SetValue action. Make a little macro like this and then you can save it as a module and see what it looks like in code.

This response is very helpful. I "theoretically" know how to create a RunMacro and will give it a try. I guess my question would have been better stated if I asked whether this task is better completed using a macro or VBA and, if it can be done with a macro, what type of macro setup would be best.

Thanks a lot. I'll let you know how it works for me.

 

[georgedwilkinson]

I believe I also have learned from Mike's response. I'll try it out myself.

 

[Mike375]

Most actions that are carried can be done by either macro or code. However code can perform functions that can't be done with macros. Macro are like the motor mechanic who uses the parts he can buy. Code would be a mechanic who can make his own parts.

If you are new to Access and want help on the forums then you should should learn what code looks like and the reason is that most answers on the forum will be for code. Although in many case, perhaps most, the same thing can be done with macros the way macros and code are made is different.

 

[Remedial]

Okay, so I've decided that I'll create a subform with 6 rows with 3 columns, one of each for child last name, last name and date of birth. The first row will automatically be visible but the additional 5 rows will have their visibility property altered upon entry of the number 2 or more (up to 6) in the NumberOfChildrenInHome field. Does that sound feasible?

I do have one last question though: Is there a list explaining all of the commands available under the RunCommand macro action?

Thanks.

 

[Pat Hartman]

Subforms in continuous or datasheet view can handle an "infinite" number of rows and unless you enter dummy rows with code and set the form to not allow additions which I don't recommend at all, there is no way to control the numer of visible rows.

The problem with what you are trying to do is the loose ends so I will tell you what I would do.
I would dispense with the count entirely unless forced. If the user insisted on entering a count and validating it then in the BeforeInsert event of the subform, I would use DCount() to count the existing rows for this parent and if the number was >= the specified number of dependents, I would cancel the insert and give the user a message that the specified number of dependents had already been added. That handles preventing them from adding more dependents than the count specified.

Making sure that they enter all the records that they indicated they would is more of a problem. To handle this with the minimum amount of code, I would add a Complete flag to the parent record. The Complete flag would be set to false when the record was created. I would add a button to the parent form to "finalize" the record. The button would contain code to count the records and compare the count to the specified count. If they were equal the Complete flag would be set to true. To close the loop on that, you would need to change the subform to also check the Complete flag in the BeforeInsert event and cancel the insert if the Complete flag is set to true. And finally, to allow updates after the fact, in the AfterUpdate event of the dependents count field, I would prompt the user to be sure he really wanted to change the field and then set the Complete Flag to false so the edits would have to be redone.

 

[Remedial]

Subforms in continuous or datasheet view can handle an "infinite" number of rows and unless you enter dummy rows with code and set the form to not allow additions which I don't recommend at all, there is no way to control the numer of visible rows.

The problem with what you are trying to do is the loose ends so I will tell you what I would do.
I would dispense with the count entirely unless forced. If the user insisted on entering a count and validating it then in the BeforeInsert event of the subform, I would use DCount() to count the existing rows for this parent and if the number was >= the specified number of dependents, I would cancel the insert and give the user a message that the specified number of dependents had already been added. That handles preventing them from adding more dependents than the count specified.

Making sure that they enter all the records that they indicated they would is more of a problem. To handle this with the minimum amount of code, I would add a Complete flag to the parent record. The Complete flag would be set to false when the record was created. I would add a button to the parent form to "finalize" the record. The button would contain code to count the records and compare the count to the specified count. If they were equal the Complete flag would be set to true. To close the loop on that, you would need to change the subform to also check the Complete flag in the BeforeInsert event and cancel the insert if the Complete flag is set to true. And finally, to allow updates after the fact, in the AfterUpdate event of the dependents count field, I would prompt the user to be sure he really wanted to change the field and then set the Complete Flag to false so the edits would have to be redone.

Great suggestion. Here's the expression that I came up with:

DCount («expr», «domain», «criteria») = Forms![GeneralForm]![NumberOfChildrenInHome]

But, I am lost at a certain step:

For my expression, which function do I use to count the amount of rows. I know that there is a "Count" function available, but I'm not sure of how to specify exactly what it should count. Also, for my domain, since I'm creating this expression in the BeforeInsert property of my subform, do I still have to create an expression leading back to that subform, i.e.:

[Children Names and DOB]![ID]

Or is there an easier way to get the subform to perform the DCount function on itself? Maybe by leaving the domain field blank, etc...

I hope I expressed myself adequately. If not, I can clarify.

Thanks so much for your guidance.

 

[Pat Hartman]

I sometimes add a Count() field to the footer of a subform. That allows me to check the count as I am adding records. You can't us = though since in the BeforeInsert event you haven't yet committed the current record.

If Me.footerCountfield < Forms![GeneralForm]![NumberOfChildrenInHome] Then
Else
Cancel = True
Msgbox "You have already entered the specified number of dependents.",vbokOnly
Me.Undo
Exit Sub
End If

 

[Remedial]

I sometimes add a Count() field to the footer of a subform. That allows me to check the count as I am adding records. You can't us = though since in the BeforeInsert event you haven't yet committed the current record.

If Me.footerCountfield < Forms![GeneralForm]![NumberOfChildrenInHome] Then
Else
Cancel = True
Msgbox "You have already entered the specified number of dependents.",vbokOnly
Me.Undo
Exit Sub
End If

Thanks a lot Pat. Your knowledge has been priceless.

 

[Pat Hartman]