Add Row(s) to my subform based on Combobox selection

[kashif73]

Hi,

I have a subform (datasheet view) on my Mainform, where a user can enter information. I need help on how to display the required number of rows in my subform based on the number selected in the dropdown box on my mainform. Please see the attached GIF. for example if a user selects 5 from the dropdown then automatcially the subform below should display 5 rows for entering data, with a default value of "UNKNOWN" in each column.
many thanks if someone can help me here.

[FishVal]

I guess this stuff could be used for it. However it will require some effort from you to adapt the concept to your application.

Regards,
Fish

[kashif73]

Thks Fish.

I am not an expert on VBA or access. So would need your help here.
I just want to add rows to my subform when I select some number from the combobox on my main form. By default we have 1 row of record to add data to in the subform, but i want to display the rows based on my combobox selection.
Thxs again.

[NeoPa]

You'll need to provide more information before you can expect any sort of step-by-step help here. This question is missing most of the required info.

As an aside, why is this done as a subform? Are you aware that forms can have header and footer sections?

[FishVal]

@kashif73
Well. I have a version adapted for your application. Unfortunately attachments are not working currently. So I'll post it later.

[kashif73]

Hi NeoPa,

Sorry couldn't explain it better. firstly i am not aware of footer & header sections on a FORM. Would appreciate your help on it.

Here's what I am trying to do. I am capturing info on violation of human rights done in some areas in my database & then I also capture information in a sub form on person(s) like there religion, ethnicity & age, who were abused.

I have a main form called ADD EVENT, where i enter details of violations. This info goes to TABLE 1.

Then in my sub form , PERS INFO, I enter the personnel details of those people who were abused. This subform saves info in TABLE 2. I save this info in a seperate table since there could be more than 1 person, whose info can be entered for a particular violation. Both tables are linked to each other.

I just want to add rows to my subform when I select some number from the combobox on my main form. By default we have 1 row of record to add data to in the subform, but i want to display the rows based on my combobox selection.

[kashif73]

Thxs Fish. I'll be waiting.

[FishVal]

@kashif73
Ok. Looks like problem with Attachment was actualy a browser problem.

• The following based on assumption that your main form / subform are feeded by 2 tables with 1-to-many relationship.
• To use the method you need to add an auxiliary table which holds a set of sequential natural numbers [1,2,3, ... N]. BTW, the same table could be advantageously used as your combobox rowsource.
• The table on the "many" side has to get an additional field which stores a row ordinal in rowset rendered in subform. The aim of the field is certainly not to persist row ordinal (though, depending on your requirements, it may be a desired feature) but to produce an updateable query to feed subform.
• There is some code behind the forms to mimick main form / subform interface since Access native master/child relation is disabled.

[NeoPa]

@kashif73
This posts adds a lot of the info missing from the original :)
@kashif73
From what you've since added, I guess it is not appropriate, but when viewing a form in Design mode you can select to show or hide the Header/Footer from the View menu.
@kashif73
How?

Please provide table details - including fields and link info.
@kashif73
Are you sure?

This seems to me like a problem waiting to happen (Adding records without info is likely to be a problem. How much of one is hard to say without the Metadata info).

Why not have the items entered as you add new ones? You could include code to restrict it to the number selected if you wish.

[kashif73]

@FishVal
Dear Fish,

Many thanks for your help. This is exactly what I was looking for. Awesome.
best regards,

[kashif73]

The following based on assumption that your main form / subform are feeded by 2 tables with 1-to-many relationship.
To use the method you need to add an auxiliary table which holds a set of sequential natural numbers [1,2,3, ... N]. BTW, the same table could be advantageously used as your combobox rowsource.
The table on the "many" side has to get an additional field which stores a row ordinal in rowset rendered in subform. The aim of the field is certainly not to persist row ordinal (though, depending on your requirements, it may be a desired feature) but to produce an updateable query to feed subform.
There is some code behind the forms to mimick main form / subform interface since Access native master/child relation is disabled.

Hi Fish, Reference your solution (AddRows.zip). Can there be a way that I can display UNKNOWN as default value in the field 'txtchild'. for example if I select 8 from the combo box, then in my frmChild I should get 8 rows(which I am getting) but with UNKNOWN as default value for txtchild.

I have tried to set UNKNOWN as default value in the field properties but it isn't working. Likewise also tried in the AfterUpdate event of cmbNN but didnt work.
Thanks.

[FishVal]

Hi, Kashif.

The provided solution is not suited for this.

Kind regards,
Fish.

[kashif73]

so can there be another way of doing it? any hint?