Confusion with forms and related subforms

  • Thread starter Thread starter Lee Ann
  • Start date Start date
L

Lee Ann

Hello,

I'm trying to figure out the concept of embedding subforms into main forms.
I have main tables, 4 junction tables, and a few look-up tables (not all
look-ups are listed below). If I'm reading it correctly, the junction tables
which relate to the main tables should be in the form of a subform placed
within the main form they relate to? I'm lost on the fact that I believe I
have a subform which appears should be on another subform which should then
be on a form. If this is correct, the form looks extremely busy and not user
friendly, and just plain incorrect. Is it true, based on the relationships,
that the PK and FK on the related tables should be joined between the subform
and main form?

These are the tables I have:

TblIncident
IncidentnumberID (PK)
misc. fields

TblSuspect
SuspectID (PK)
misc. fields

TblOfficer
OfficerBadgeNumberID (PK)
OfficerLastName
OfficerFirstName

TblIncidentOfficer
IncidentOfficerID (PK)
IncidentNumberID (FK)
OfficerBadgeNumberID (FK)

TblIncidentSuspect
IncidentSuspectID (PK)
IncidentNumberID (FK)
SuspectID (FK)

TblIncidentSuspectCharges
IncidentSuspectChargesID (PK)
IncidentSuspectID (FK)
ChargesID (FK)

TblForceUsed
ForceUsedID (PK)
OfficerBadgeNumber (FK)
TypeofForceID (FK)

TblCharges
ChargesID (PK)
Charges

Thanks in advance for any assistance.
 
Lee Ann -

A subform is used to help show the 'many' side of a one-to-many
relationship. For many databases, this is a straight-forward relationship.
It gets more complicated with the many-to-many relationships such as those in
your database. While the theory still holds, you have to adjust it to your
needs.

For example, you might want to show suspects with all their incidents. The
suspect table would be the recordsource for the main form, and Incident
information would be used in the subform. This Incident information would
normally be a query based on the IncidentSuspect table joined to the Incident
table.

You could also show Officers on a main form with Incidents on a subform this
way, and many other one-to-many relationships.

I have a feeling you want to show everything about an incident on one form.
In that case, the Incident table would be used for the main form, and you may
have several subforms - one for the officers, one for the suspects. The
suspect subform would probably have a recordsource of a query based on
TblIncidentSuspect joined with TblSuspect, and would be linked to the main
form through the IncidentNumberID. This suspect main form may include a
subform of its own to show the charges against the suspects. This
sub-subform would probably have as its record source a query that joins the
tblCharges to the tblIncidentSuspectCharges, and would be linked to the
IncidentSuspect subform based on the IncidentSuspectID field. Similarly, the
Officer subform might have a subform of it's own to show the Force Used.
This data would come from a query based on the the TblForceUsed joined to the
TypeOfForce table. Trying to do this will point out that you need to change
your TblForceUsed table - instead of OfficerBadgeNumber, you need the
IncidentOfficerID as the foreign key.

Hope that helps!
 
Thanks for the quick response - regarding the point you bring up reference
the officerbadgenumber in the forceused table, I had posted my table design a
couple weeks ago and based on the fact that there could be more than one
officer involved, sometimes using more than one type of force, the tables
were set up that way. Hopefully that is correct for that scenario.

Your reply makes me wonder if I'm looking at the forms incorrectly. When
needed, I expect each of these fields to be used (i.e., there will never be
an incident where no officer is involved, no suspect, no type of force,
etc.). I guess that's why I'm looking at putting everything on a form for
the user to input the data. With your second paragraph, it would seem most
forms/subforms are done for people to "view" the data already in the records.


Your advice is appreciated and I'll give it a shot.
 
Lee Ann -

I think the table does need updating, as it tells what type of force was
used by what officer, but it doesn't indicate which incident this is for.
You could add the IncidentNumberID to this table as a foreign key if you
don't want to switch the OfficerBadgeNumber to the IncidentOfficerID. If you
don't do one of these, then when you pull up an Officer, then through his
OfficerBadgeNumber you can find all the types of force used, but you won't be
able to say which force was used in which incident.

As for your other question, for simple relationships you can use the same
forms for data entry and for viewing. You can change form properties to
allow the form only for adding new records in one case and for only viewing
results in another. The fact that a query is the recordsource instead of a
table does not change the ability to add, delete, or update records in
itself, but some queries will not allow adds or updates, for example to the
'one' side of a one-to-many relationship. This makes sense from a data
integrity standpoint.

In your complex case, you will need to make some adjustments to straight
data-entry of all the data. Normally, from the Incident point of view, you
would want to add a new incident on the main form, and after it is saved, you
would want to add the officers and suspects to the incident. Normally, you
would do this in the Officer and Suspect subforms. You would select each
officer from a drop-list based on TblOfficer,each a new record in the
subform. Similarly, you would select each suspect from a drop-list based on
TblSuspect in the Suspect subform. If the suspect drop-list does not contain
the suspect (because this is a new suspect), then you would want to pop up a
form to fill out the new suspect's information. This would update the table
TblSuspect and when done, it could place the new suspect onto the Suspect
subform on the Incident form.

Remember that each Officer you entered now needs one or more records added
to TblForceUsed, and each IncidentSuspect that you added needs one or more
records added to TblIncidentSuspectCharges. This is where the sub-subforms
come in...

I hope that helps!
 
Back
Top