Populate subform with amount of fields indicated in the value entered into a textbox control on main form

Posted on 2007-11-13
Last Modified: 2013-12-25
I want to populate a subform with the amount of textbox controls indicated by the numeric value entered in a textbox on the main form. Precisely, three bound text fields will need to populate for every unit entered in the main texbox, e.g. an entry of "3" will populate 3 textBox1 fields, 3 textBox2 fields and 3 textBox3 fields. I assume the event would be scripted from an "on dirty" event procedure, but I have no idea how this would be scripted or set up on the form/subform. Any insight would be appreciated.
Question by:devabarry
LVL 19

Expert Comment

ID: 20277799
are the subform textbox controls bound or unbound? if bound then just update the relevant table and refresh the subform.
if unbound then update the subform controls in the afterupdate event of the textbox control in your main form.

Author Comment

ID: 20277847
the subform controls will be bound, although I haven't created the subform yet. I'll research the approaches you mention, as they are new to me, but if you wish to elaborate... :-). Just to confirm, will your suggestion address the need for the subform to populate with the appropriate number of textfields based on the numeric input in the relevant form field? Isn't there some scripting required? Thanks.
LVL 27

Expert Comment

ID: 20279577
What table/query will your sub-form be bound to?

Author Comment

ID: 20286534
Ideally I would like to bind to two related tables:

table1_ID (PK)
participant_ID (FK to tblPID)

table2_ID (PK)
table1_ID (FK to table1)

the bound fields in the subform would be:

The idea is that the user enters a number in the main form indicating how many relations are in the household. For instance if a person lives with their mother, father and sister, the user would enter '3' for three types of relations. This number would then populate three rows of text fields bound to the above-described columns. I.e., whatever numeric value is entered in the main form field populates that many rows in the subform.

LVL 27

Expert Comment

ID: 20289510
Bound fields are exactly that - they relate directly to records in a table.
To get 3 sets of bound fields in your subform if the number requested in your mainform is 3, you would need to add three records to the table that the subform is bound to, one for each of the relations.  

I'm not sure about your table design though -  it might help if you put meaningful names to Table1 and Table2, that often helps avoid confusion.

It looks like your design is tblPID identifies Participants, Table1 is 'Relationships' and Table3 is 'Occupations'
So Participants have Relations and Relations have an Occupation.
The Number_inHouse doesn't seem to belong there, since that can be deduced from the number of Relations a participant has.
Are you going to record >1 occupation for a Relation?, if not then Occupation belongs with the Relation The design would then look like



If you want to stick with the entry of a number of relations then the approach would be to loop round an Insert statement in the after update event of the number field. (Care sould have to be taken not to let this number be changed, otherwise you'd need more logic to avoid too many records being inserted.) Something like:

Private Sub txtNumberRelationships_AfterUdate()
   Dim i as Integer
   for i = 1 to me.txtNumberRelationships
      docmd.runsql "Insert into tblRelation(Participant_ID) Values(Me.txtParticipant_ID)
   next i
End Sub
- this assumes that Relation_ID is an autonumberfield
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


Author Comment

ID: 20294064
Perhaps I have misunderstood the term 'bound'. I don't want to bind the fields to existing records, I want to associate the fields with columns in the database so that data are saved to the appropriate columns. So with 3 relations indicated, I am expecting that 3 new records will be entered, not tying them to ones that already exist. I hope I'm making sense here.

Table1 and Table2 are not the actual table names. Precisely, Relation and Occupation.

The number_in_house db field in table Relations gets its value from the main form field that is also determining the number of field sets required on the subform, so I would say it is required. Hopefully this also makes sense.

I will try out your code suggestion in case it in fact does what I need and get back to you.


Author Comment

ID: 20294980
A related question: can a form access more than one table through the control source property? I.e., can one field be bound to one table while another field is bound to a different table? I haven't seen a way to change the table in the properties box or elsewhere. For instance, on the main form I discuss above, the textfield accepting the number of relations needs to be bound to the Relations table, while everything else is bound to the Demographics table.

Author Comment

ID: 20295367
My apologies, I am mistaken about the number_in_house field being the same as the initial number field on the main form. The initial number will be the total occupants of house, while the number_in_house will be different for each relation (mother, father, sister, brother, whatever). Sorry if I caused any confusion; it's only because I'm confused!
LVL 27

Accepted Solution

MikeToole earned 500 total points
ID: 20297050
<while the number_in_house will be different for each relation (mother, father, sister, brother, whatever)> What then is the meaning of the number_in_house field?

<can a form access more than one table through the control source property?>  Only if the tables are linked together in a query and the query is made the data source of the form. Binding a form to a datasource is designed to make updating easier, change the data in a control and, when the record is saved, the data is changed in the database. You can bind individual controls to an expression and this can get data from anywhere you like - including the database - but these controls are then read-only.

<I don't want to bind the fields to existing records, I want to associate the fields with columns in the database so that data are saved to the appropriate columns.>
If there are no existing rows in the data source for a form (in this case your sub form) that match the criteria for display (in this case the the Participant_id on the main form) then Access will display a single record consisting of all the fields in the form's record source, ready for data to be entered for a new row.  (This is actually a simplification of the reality since in some situations additions will not be allowed and no empty row will show)
This is the normal approach to adding a master record on a main form and several related records in a sub form.

Is there a particular reason you want to enter the number of realations in the main form? And, is there a particular reason you want to store this number?

Another design requirement question: will it ever be possible for more than one person in a household to be a Participant?

Expert Comment

ID: 20943123
Forced accept.

Community Support Moderator

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now