Subform, of a Subform of a Form

[NJonge01]

I posted a similar problem weeks ago, and I received some great advice but none of which I was able to apply successfully. I suspect that part of the problem was I had already begun the work myself, and probably corrupted the scenario. I worked thru that situation with multiple forms.

I am about to start on a new project. Before I even begin, I welcome ANY advice.

I manage software design documents following an implementation of the RUP Methodology.

My database works wonderfully when all requirements are driven by a design document called a Use Case

A Use Case has 1 to many Flows. Each flow has multiple steps. Here's where I get my problem.

I want to view a single Use Case.

For each Use Case, I want to add 1 to many flows.

For each Flow, I want to add 1 to many steps.

I'd like to navigate through my application from use case to use case and then add, update Flows and Flow - Steps as I go.

So, the above lends itself to a form, with a subform, and another subform.

Any best practices I should implement from the get-go to avoid problems?

Thanks in advance for any help!

[Scott Price]

I posted a similar problem weeks ago, and I received some great advice but none of which I was able to apply successfully. I suspect that part of the problem was I had already begun the work myself, and probably corrupted the scenario. I worked thru that situation with multiple forms.

I am about to start on a new project. Before I even begin, I welcome ANY advice.

I manage software design documents following an implementation of the RUP Methodology.

My database works wonderfully when all requirements are driven by a design document called a Use Case

A Use Case has 1 to many Flows. Each flow has multiple steps. Here's where I get my problem.

I want to view a single Use Case.

For each Use Case, I want to add 1 to many flows.

For each Flow, I want to add 1 to many steps.

I'd like to navigate through my application from use case to use case and then add, update Flows and Flow - Steps as I go.

So, the above lends itself to a form, with a subform, and another subform.

Any best practices I should implement from the get-go to avoid problems?

Thanks in advance for any help!

Well, ordinarily, you will use three tables, given the above structure:

tblUseCase
UseCaseID AutoNumber PK
UseCase
FlowID Number FK

tblFlow
FlowID AutoNumber PK
Flow
FlowStepID Number FK

tblFlowStep
FlowStepID AutoNumber PK
FlowStep

The query to select the data you want from the above example looks something like this:

SELECT tblUseCase.UseCaseID, tblUseCase.UseCase, tblUseCase.FlowID, tblFlow.Flow, tblFlowStep.FlowStep FROM tblUseCase, tblFlow, tblFlowStep INNER JOIN tblUseCase ON tblUseCase.FlowID = tblFlow.FlowID (INNER JOIN tblFlow ON tblFlow.FlowStepID = tblFlowStep.FlowStepID)  WHERE tblUseCase.UseCaseID = Forms!frmUseCase!cboUseCaseSelect

You likely won't need to nest that many subforms, since you can use listboxes to do the same job a little more neatly. I.e. a combo box to choose the UseCase, a listbox to display the Flow(s), and a second listbox to display the associated Flowstep(s).

This is strictly off the top of my head, now! So don't go copying the code and pasting it without checking it carefully :-)

Hope this helps!

Regards,
Scott

[ADezii]

I posted a similar problem weeks ago, and I received some great advice but none of which I was able to apply successfully. I suspect that part of the problem was I had already begun the work myself, and probably corrupted the scenario. I worked thru that situation with multiple forms.

I am about to start on a new project. Before I even begin, I welcome ANY advice.

I manage software design documents following an implementation of the RUP Methodology.

My database works wonderfully when all requirements are driven by a design document called a Use Case

A Use Case has 1 to many Flows. Each flow has multiple steps. Here's where I get my problem.

I want to view a single Use Case.

For each Use Case, I want to add 1 to many flows.

For each Flow, I want to add 1 to many steps.

I'd like to navigate through my application from use case to use case and then add, update Flows and Flow - Steps as I go.

So, the above lends itself to a form, with a subform, and another subform.

Any best practices I should implement from the get-go to avoid problems?

Thanks in advance for any help!

I know it's not the recommended practice, and I'll probably get some negative comments on this approach, but to me the most intuitive, simplest, logical, and recognizable solution to entering multi-level, hierarchical data of this nature is to use the Datasheet ==> Sub-Datasheet ==> and Sub-Sub-Datasheet capability in Access. It is frowned upon because you are entering data at the lowest level (Table) and it is not buffered. If you are the only one who would be entering and/or modifying data, you may wish to consider this angle. On the other hand if you have multiple Users who would be doing the same, I would forget that I ever mentioned it. I am not eliminating the need for Forms as far as displaying the data, I am only mentioning this approach primarily for the use and intuitiveness with which you can enter data. If I'm populating Tables at 3 or more levels deep, this is exactly how I approach this situation. Now, I'll just sit back and watch the bullets fly!

[NJonge01]

Thank you for the replies! I do need to allow for multiple users. To that end, I've had some pretty good results from using a combination of a subform and list boxes. I'm really pleased with how this has come together.

thanks for the help Scott!

[Scott Price]

Thank you for the replies! I do need to allow for multiple users. To that end, I've had some pretty good results from using a combination of a subform and list boxes. I'm really pleased with how this has come together.

thanks for the help Scott!

You're quite welcome!

Good luck with the rest of your app.

Regards,
Scott