Dynamic amount of subforms (or controls)

[sharpnova]

I have a form with company level information. At the bottom of the form I would like to have a subform (or just a set of input boxes) that contain information about a department.

And when they start entering information into one of the department level fields, another subform (or set of input boxes) is created below it.

And so on ad infinum.

This way they can add as many department level records as they want because the amount of inputs available is always one greater than the amount they've actually edited.

Naturally, being able to reference these fields/subforms with an array would be necessary so I could loop through the array and do a series of update/insert queries.

How could I go about doing this.

 

[jzwp22]

What you describe should be handled with records not fields or controls that ever expand. If a company has many departments that is a one-to-many relationship. If you want to record various notes about a deparment that is yet another one-to-many relationship. Based on the limited info you have provide, the table structure would look something like this:


tblCompany
-pkCompanyID primary key, autonumber
-txtCompanyName
-txtAddress
etc.

tblCompanyDepartments
-pkCoDeptID primary key, autonumber
-fkCompanyID foreign key to tblCompany
-txtDeptName


tblCompanyDepartmentNotes
-pkCoDeptNoteID primary key, autonumber
-fkCoDeptID foreign key to tblCompanyDepartments
-dteNote (date field)
-txtNote

Relative to forms you would use a form/subform/sub-subform design.

Can you provide a little more detail of what type of info about a department you are trying to capture?

 

[sharpnova]

The table structure you described is exactly what I'm using.

All I'm concerned with at this point is getting the form to work in a certain way.

Is the normal solution for something like this to have the form be tied to the company table and that form have a subform that is both "continuous" and tied to the departments table?

That's the way I figured out and have functional atm.

 

[jzwp22]

I would base the main form on the company table. I would then base a subform on the department table (single form view not continuous). Then within the subform have a subform (sub-subform) that is based on the tblCompanyDepartmentNotes table. I would typically have this sub-subform in datasheet view.

 

[sharpnova]

I think you may be suggesting this because I didn't explain what I"m trying to do very well.

A company may have multiple departments. And as soon as they start entering information for one department (HR for example), then another set of fields will activate so they can enter yet another department if they wish (accounting for example)

This is why I'm using continuous forms.

Or am I misunderstanding you?

 

[jzwp22]

If you wanted to enter a second department related to the company that would be a second record, so you would have to migrate to a new record. In either datasheet view or continuous form view, that would be another row. However, if you want to add details such as a series of notes about a department that would be in the sub-subform and each note would be a record. The department form cannot be in either datasheet or continuous form view if you wanted to add notes about a department.

 

[sharpnova]

I want all department records visible at once, one after another, vertically.

So is the way I found to do it correct then?

 

[jzwp22]

If you want all the departments visible at once then you have to use either datasheet view or continuous form view for the subform. You will not be able to use a subform within the department form for adding details about each department.

 

[sharpnova]

Yeah and that's fine. There is currently no lower level entity than a department.

(this app doesn't actually concern companies or departments. those are just placeholders for what the app really covers that i used for the sake of asking this question. what it actually does, i can't discuss due to NDA)