Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2007-11-13
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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?
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.


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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

715 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