Solved

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

Posted on 2007-11-13
11
577 Views
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.
0
Comment
Question by:devabarry
11 Comments
 
LVL 19

Expert Comment

by:frankytee
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.
0
 

Author Comment

by:devabarry
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.
0
 
LVL 27

Expert Comment

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

Author Comment

by:devabarry
ID: 20286534
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.
0
 
LVL 27

Expert Comment

by:MikeToole
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

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
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:devabarry
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.

Thanks.
0
 

Author Comment

by:devabarry
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.
0
 

Author Comment

by:devabarry
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!
0
 
LVL 27

Accepted Solution

by:
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?
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20943123
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 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

10 Experts available now in Live!

Get 1:1 Help Now