Linking Subforms

[howie]

Hi, the person I am making this database for wants me to build a form that
allows adding, editing and deleting data from the database. Basically, the
database stores and tracks jobs hazard analysis forms. I have a formID that
correlates all data from subsections of the JHA (like all the hazards for a
job, all the personal protective equipment used for a job, all the training
necessary for a job, etc.). Anyway, the form that I am trying to make has to
have 7 subforms (the number of subsections of the JHA) because for each
subsection there can be many entries. For example, a job can present many
hazards and each of those hazards that belong to that particular job will
have the same formID.

My problem comes in when I am trying to add a new JHA into the database.
The table that correlates to each sub-form needs the formID so that the data
can be stored properly and be retrieved later. However I only want the user
to have to enter it once and not 7-8 times how can I get the formID from the
one place on my form and be able to store it in each of my 7 tables? Should
that one place be on the main form, would it make it easier? Or can it be in
one of the subforms?

 

[Dirk Goldgar]

Hi, the person I am making this database for wants me to build a form that
allows adding, editing and deleting data from the database. Basically, the
database stores and tracks jobs hazard analysis forms. I have a formID
that
correlates all data from subsections of the JHA (like all the hazards for
a
job, all the personal protective equipment used for a job, all the
training
necessary for a job, etc.). Anyway, the form that I am trying to make has
to
have 7 subforms (the number of subsections of the JHA) because for each
subsection there can be many entries. For example, a job can present many
hazards and each of those hazards that belong to that particular job will
have the same formID.

My problem comes in when I am trying to add a new JHA into the database.
The table that correlates to each sub-form needs the formID so that the
data
can be stored properly and be retrieved later. However I only want the
user
to have to enter it once and not 7-8 times how can I get the formID from
the
one place on my form and be able to store it in each of my 7 tables?
Should
that one place be on the main form, would it make it easier? Or can it be
in
one of the subforms?

Although it is not required, most commonly subforms are related to the
current main-form record by what are called the Link Master Fields and Link
Child Fields. These are set on the Data tab of the property sheet of the
subform control. The Link Master Fields property identifies one or more
fields or controls on the main form that are the main form's link to the
related subform records, while the Link Child Fields property identifies one
or more fields on the subform that are the subform's link to the related
main-form record. The subform only shows those records that have link child
field(s) equal to the main form's link master field(s), and whenever a new
record is created on a subform, the value(s) of the link master field(s) is
automatically copied to the corresponding link child field(s). Note, by the
way, that the

From the sound of it, your formID should the Link Master Field on the main
form and the Link Child Field on the subforms. Assuming that this field has
a value on the main form, you won't need to explicitly set its value on the
subforms, as the subform linkage should handle that automatically.

 

[BruceM]

Let's say you have something like this:

tblJHA (Table)
JHA_ID
JHA_Description
etc.

tblHazard
HazardID
JHA_ID
Hazard_Description
etc.

Create a relationship between JHA_ID in the two tables (one-to-many, as
there can be many Hazards for each JHA record). Create a form based on
tblJHA, and another based on tblHazard (it will be the subform). One way to
create the subform is to drag it from the database window onto the main
form. Another way is to use the toolbox to add a subform control to the
main form. Set the Source Object of the subform control to the form based
on tblHazard. In either case, set the Link Child and Link Master properties
of the subform control to JHA_ID. (Remember, the subform control is the
"box" containing the subform, not the subform itself.)

From what I understand there are six other tables along the same lines as
tblHazard. Proceed in the same way for each.

Once the Link Child and Link Master fields are set there is no need for the
user to add the linking field manually.

 

[howie]

Ok thanks for the help guys
--
howie

Let's say you have something like this:

tblJHA (Table)
JHA_ID
JHA_Description
etc.

tblHazard
HazardID
JHA_ID
Hazard_Description
etc.

Create a relationship between JHA_ID in the two tables (one-to-many, as
there can be many Hazards for each JHA record). Create a form based on
tblJHA, and another based on tblHazard (it will be the subform). One way to
create the subform is to drag it from the database window onto the main
form. Another way is to use the toolbox to add a subform control to the
main form. Set the Source Object of the subform control to the form based
on tblHazard. In either case, set the Link Child and Link Master properties
of the subform control to JHA_ID. (Remember, the subform control is the
"box" containing the subform, not the subform itself.)

From what I understand there are six other tables along the same lines as
tblHazard. Proceed in the same way for each.

Once the Link Child and Link Master fields are set there is no need for the
user to add the linking field manually.