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

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.

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.

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

Ideally I would like to bind to two related tables:

TABLE1
table1_ID (PK)
participant_ID (FK to tblPID)
relationship
number_in_house

TABLE2
table2_ID (PK)
table1_ID (FK to table1)
occupation

the bound fields in the subform would be:
relationship
number_in_house
occupation

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.

Thanks.

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

tblPID
Participant_ID
...

tblRelation
Relation_ID
Participant_ID
Relation
Occupation

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

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.

Thanks.

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.

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!

Forced accept.

Computer101
Community Support Moderator