Try again - relationships.gif (0/1)

[David G.]

I'm stumped and hope someone can point me in the right direction.

My goal is a form with subform that allows records to be added or
edited in the subform (which is set to either datasheet or continuous
forms view). Fields in the form header (or body) are combo boxes whose
values are used to filter the subform's record source.

I've attached a gif image of tables (with field names) and
relationships that I've set up.


The tables starting with "jtbl" are join tables for many-to-many
relationships.

definitions:
1. Material = defines a substance
2. Test Type= defines a test (any given material can have a several
test types.)
3. jtblMaterialTestType = holds the design spec value for each test
type for each Material

4. Sample = a Material extracted at a specified time/date.
5.jtblSampleTestType = holds the measured value for each test type for
each sample.

Main Form (record source = tblSample)
-Header
--a couple of unbound combo boxes linked to tblMaterial fields which
will be used in an SQL statement for the cboSample combo box control
in the form detail section.

-Detail
--cboSample combo box. bound to tblSampleID, record source=
tblMaterial where field values match the combo box values located in
the form header.
--other text box, or combo box controls bound to fields in the
tblSample table used to uniquely identify a sample.
--sub form control. record source ????(something like a combined
jtblMaterialTestType and jtblSampleTestType)
If I used jtblMaterialTestType and child/parent linked
jtblMaterialTestType.tblMaterialID to tblSample.tblMaterialID I would
get a list of test types and their target values for that sample. I
want this so the user knows what tests are to be performed and what
the expected values should be.
This doesn't allow the user to enter the measured test values.
how can I create a record source for the subform? Is my table design
part of the problem.
THANKS!
David G.
THANKS!
David G.

 

[Arvin Meyer [MVP]]

Your attachment is not connected, and no one in there right mind opens
Internet attachments anyway. I've tried to discern your problem, but
understanding seems to depend upon the attachment. I suggest you try repost
with a *concise* description of your problem, beyond what I've left below.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I'm stumped and hope someone can point me in the right direction.

My goal is a form with subform that allows records to be added or
edited in the subform (which is set to either datasheet or continuous
forms view). Fields in the form header (or body) are combo boxes whose
values are used to filter the subform's record source.

 

[dweeber62]

Thanks very much for your comments. I am going to spend some time
digesting them so I understand them. In the mean time, I consolidated
all my information in a single post which I sent out around 1pm local
time.

If you would take a quick look at that post, and offer any additional
comments I would be greatly thankful.

David:

I had a nagging doubt at the back of my mind that there was a flaw in the
model I'd suggested, so I had another look. Right enough, it does allowfor
an anomaly, in that it would be possible for a test type to be entered into
the ST table for a sample to whose material that test type does not apply.
The restriction on the test types combo box's RowSource caters for this,but
doing so solely at form level is not really good enough. A better model
would to ditch the ST relationship and instead create a SMT relationship
between MT and S:

S---<SMT>---MT

The table modelling the SMT relationship would thus have two overlapping
foreign keys, SM (SampleID and MaterialID) referencing S and MT (MaterialID
and TestTypeID) referencing MT.

Although the primary key of Samples is SampleID, SampleID and MaerialIDis
a candidate key (any column or columns in conjunction with the primary must
be a candidate key of course), so can be referenced by the SM foreign key in
SMT. By enforcing the relationships only test types applying to the sample's
material can be entered in the SMT table.

The interface would be the same, but the subform within the Sample parent
form would now be based on the SMT table, and the subform control's
LinkMasterFields and LinkChildFields properties would now each include both
columns, so would be SampleID;MaterialID.

Ken Sheridan
Stafford, England

Hidi Ho,
The Dweeber

 

[David G.]

Ken:
I studied your comments and I believe you understand my intentions.
Your model seems to address almost everything I shooting for with the
following exception:

I wanted the subform to list all applicable test types for that
sample. (Exactly what you could get from the MT join table limited to
one M ID.) Your method, if I understand it correctly, would show no
records in the subform (except for a blank record), and require the
user click on combobox control whose record source is limited to
applicable tests only. Test Types (and results) could be added easily
enough, but the user would not know if all tests completed without
clicking the combobox and comparing the list of available tests with
the tests shown in the subform records.

As I see it, the problem is creating records for the tests in the ST
table. I thought I might add a command button that would add a set of
records to the ST table using an appendquery based on the MT table
where M ID field= S.M ID field. (This would only happen when a new
sample was being created.)

Your revised comments are much appreciated. I'm not familiar with some
of your terminology, so I will have to do some research. Like
overlapping keys, and candidate keys.

Thanks again for your comments!

Thanks very much for your comments. I am going to spend some time
digesting them so I understand them. In the mean time, I consolidated
all my information in a single post which I sent out around 1pm local
time.

If you would take a quick look at that post, and offer any additional
comments I would be greatly thankful.


Hidi Ho,
The Dweeber

THANKS!
David G.

 

[dweeber62]

Ken:
Thank you so much for your help. My form is displaying the correct
information. I was really stuck on the table design. I owe you!

Sincerely,
David

Firstly, as regards the terminology, overlapping foreign keys simply means
that each key is made up of more than one column, but each shares (in this
case) one of the columns. A candidate key is any column or combination of
columns which uniquely identify a row in a table. All of a table's columns
are by definition a candidate key as in a legitimate table no row can have
the same values in all columns. When a single column is a table's primary
key then this in combination with any other column will uniquely identify
each row of course as the values in the single primary key column must be
distinct, so any combination of column or columns with a primary key is a
candidate key.

To automatically insert rows for all the relevant test types into the subform
when a new sample record is created in the parent form then the column in
which the measured test results are entered should either accept Nulls (i.e.
not be 'Required'), or should be 'Required' and have a DefaultValue of zero
in the table design. You can then execute an SQL statement in code in the
parent form's AfterInsert event procedure to insert rows into the SMT table
(I'll stick with the abbreviated names here for simplicity, but you can use
more expanded names of course in reality. The ST table no longer existsof
course with my revised model). What this need to do is insert the SampleID
and MaterialID values from the parent form's current record along with each
TestTypeID value for the material in question. The code would be along these
lines:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' insert rows into SMT table
strSQL = "INSERT INTO SMT(SampleID,MaterialID,TestTypeID) " & _
"SELECT " & Me.SampleID &"," & Me.MaterialID & ", TestTypeID " &_
"FROM MT WHERE MaterialID = " & Me.MaterialID

cmd.CommandText = strSQL
cmd.Execute

' requery subform to show new rows
Me.sfcSMT.Requery

where sfcSMT is the name of the subform control in the parent form's Controls
collection which houses the subform, not the name of the underlying form
object, unless of course both have the same names.

The way it works is that the values of the SampleID and MaterialID from the
parent form's current record are built into the string expression, but the
TestTypeID column from the MT table is referenced by name. This means that
the SQL statement (which is the equivalent of an 'append' query) will insert
one row into SMT for each row in the MT table with the current MaterialID
value, so the same values of SampleID and MaterialID will be inserted into
each row, but in each case a different value of TestTypeID will be inserted.

Don't forget that if any of your table or field names contain spaces or
special characters then you need to wrap their names in square bracket when
referencing them in code, e.g. [Test Type ID].

Ken Sheridan
Stafford, England

Ken:
I studied your comments and I believe you understand my intentions.
Your model seems to address almost everything I shooting for with the
following exception:

I wanted the subform to list all applicable test types for that
sample. (Exactly what you could get from the MT join table limited to
one M ID.) Your method, if I understand it correctly, would show no
records in the subform (except for a blank record), and require the
user click on combobox control whose record source is limited to
applicable tests only. Test Types (and results) could be added easily
enough, but the user would not know if all tests completed without
clicking the combobox and comparing the list of available tests with
the tests shown in the subform records.

As I see it, the problem is creating records for the tests in the ST
table. I thought I might add a command button that would add a set of
records to the ST table using an appendquery based on the MT table
where M ID field= S.M ID field. (This would only happen when a new
sample was being created.)

Your revised comments are much appreciated. I'm not familiar with some
of your terminology, so I will have to do some research. Like
overlapping keys, and candidate keys.

Thanks again for your comments!

Thanks very much for your comments. I am going to spend some time
digesting them so I understand them. In the mean time, I consolidated

[quoted text clipped - 36 lines]

Hidi Ho,
The Dweeber

THANKS!
David G.

Hidi Ho,
The Dweeber