One table linked to three

[Dave]

Hi,

I have one table (tblsample) linked in a one-to-many relationship to
three other tables (tblHLA, tblPRA and tblXM). of course, the FK of
these three tables is the PK of the one table. Each row of tbl sample
may have a row in any of the three joined tables.

On my HLA subform, I would like to show only those records from
tblSample that also have entries in tblHLA.

Using a form based on tblSample with subform tblHLA, I also get the
records from tblSample that have entries in tblPRA. Any ideas?

Thanks,

Dave

 

[Dave]

Dave:

Base the parent form on a query which includes a subquery in its WHERE clause
to restrict the outer query to those rows where a matching row exists in
tblHLA:

SELECT *
FROM tblsample
WHERE EXISTS
    (SELECT *
     FROM tblHLA
     WHERE tblHLA.YourKey = tblSample.YourKey);

Ken Sheridan
Stafford, England

Thanks Ken. That worked beautifully!

So I put the tblHLA as a continuous form subform on form tblSample. I
can add a sample to the subform but it does not allow me to add a
corresponding record in the tblHLA subform.

Thanks, Dave

 

[Dave]

Dave:

Is the tblHLA subform's AllowAdditions property set to False (No)?  That
would prevent you adding a new tblHLA record, either after adding a new
sample record in the parent form or if adding another record in the subform
to an existing set.

Ken Sheridan
Stafford, England

[quoted text clipped - 33 lines]

- Show quoted text -

Thanks Ken. That worked beautifully!

So I put the tblHLA as a continuous form subform on form tblSample. I
can add a sample to the subform but it does not allow me to add a
corresponding record in the tblHLA subform.

Thanks, Dave

Ken,

The tblHLA subforms AllowAdditions is set to Yes. I don't know if it
makes a difference, but the tblHLA subform is in the footer section of
the tblSample form. I put it there so that I could have tblSample as a
continuous form.

Dave

 

[Dave]

Dave:

Is the tblHLA subform's AllowAdditions property set to False (No)?  That
would prevent you adding a new tblHLA record, either after adding a new
sample record in the parent form or if adding another record in the subform
to an existing set.

Ken Sheridan
Stafford, England

Dave:
[quoted text clipped - 33 lines]
- Show quoted text -
Thanks Ken. That worked beautifully!
So I put the tblHLA as a continuous form subform on form tblSample. I
can add a sample to the subform but it does not allow me to add a
corresponding record in the tblHLA subform.
Thanks, Dave

Ken,

The tblHLA subforms AllowAdditions is set to Yes. I don't know if it
makes a difference, but the tblHLA subform is in the footer section of
the tblSample form. I put it there so that I could have tblSample as a
continuous form.

Dave- Hide quoted text -

- Show quoted text -

Firgured it out. RecordSet type was set to Snapshot instead of
Dynaset. Works now.

Thanks,

Dave

 

[Dad]

Hi,

I have one table (tblsample) linked in a one-to-many relationship to
three other tables (tblHLA, tblPRA and tblXM). of course, the FK of
these three tables is the PK of the one table. Each row of tbl sample
may have a row in any of the three joined tables.

On my HLA subform, I would like to show only those records from
tblSample that also have entries in tblHLA.

Using a form based on tblSample with subform tblHLA, I also get the
records from tblSample that have entries in tblPRA. Any ideas?

Thanks,

Dave