Nested forms and subforms

[grnzbra]

I am building a database for a police firearms instructor which will be used to track officers' training and qualification shooting activities.

The several tables include:

Targets - list of different type of target by name (FBI, Q-Bottle, etc). Used as source for combo box.
StageTargets - Description of targets as they appear in a stage (Target 1,2,3,Robber,Hostage,Bystander, etc)
Stages - description of things to be done between commense fire and cease fire commands.
CourseOfFireStages - Links stages to particular course of fire.
CourseOfFire - A group of stages
Event - Gathering of officers to shoot one or more courses of fire.

Each of these has a many to many relationship to the one above and below it.

As I see this, one would load the target table first with the targets available.

Next a stage would be designed, with an entry in the stage table along with corresponding entries in the StageTarget table.
The course of fire table would be loaded with stages from the stage table.
(Actually, we would be adding a reference to the COF table to each record in the COFStages table and a reference to the Stages table in records of the StageTargets table, etc)
This would reqire, in each case, a form and subform.

However, the users want to be able to go at this from the other end. They agree that the Targets table would be loaded first because this loading would be a one time thing.
But then they want to open a new Course of Fire record and add newly designed stages as they go. When I tried to accomplish this, I ended up with a series of form and nested subforms as follows:

Event - form
COFs - subform 1
COFStages - subform 2
Stages - subform 3
StageTargets - subform 4
Targets - combobox on StageTargets

The key field for each subform shows up as a combox on the next higher form/subform. If one makes a selection in any one of them, all records at lower subforms snap into place very nicely. If one tries to enter something that is not in the combobox list, NotInList opens a series of form/subforms to enter new info.

To me this seems a very awkward way of doing things. Can anyone suggest a better way of doing this while designing from the COF level down?

 

[LittleSmudge]

What you describe in your text is not consistent with the statement
"Each of these has a many to many relationship to the one above and below it."

I can't see how you get a ManyToMany between Targets and StageTargets

and it seems like Stages and CourseOfFire need a ManyToMany between them.

Can you clarify please.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[grnzbra]

You're right. Actually the COF shoud be EventCOF and there is, in fact, a one to many between StageTargets and Targets, since COFTargets has a record for each target in the stage. Hence, there would be only one type of target for each target in the stage (Realisticly, I would expect that there would never be more than one type of target used in any one stage, or even one course of fire. But you never know; a training session might use different targets)

Also, the Stages subform shouldn't be there. It should be listed as
Facilities - subform 3b,
with
StageTargets - subform 3a.

Both are subforms of COFStages with
Targets - subform 4.

Facilities would be what range was being used. That is another thing that, like targets, would be loaded first.

It still seems like an awfully cumbersome way to do it. I was thinking of just having just the EventsCOF subform with a query joining all the tables. One could only select courses of fire to add to an event.

If one wanted to design a COF, one would open a COF form with an option box for a selection Create New or Select Existing for stages.