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?
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.


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

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Office 365 home questions 7 65
Programmer 14 48
Ms Access VBA Variables 6 26
object variable or with block not set 6 28
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 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