How do I stop a subform from repeating records?

C

Craig

A very important subform is repeating records. Users make a series of combo
box selections that determine what appears in the master field, which has 10
characters. The child field is a primary key with up to 15 characters, the
first 10 being identicle to the master field. The subform is a justified
form.

The subform works fine, except that it often (not always) displays each
record 6 times. So, if there are 6 records that match the master field, 36
are shown. When you advance through them, each record is repeated 6 times.
 
D

Dirk Goldgar

Craig said:
A very important subform is repeating records. Users make a series
of combo box selections that determine what appears in the master
field, which has 10 characters. The child field is a primary key
with up to 15 characters, the first 10 being identicle to the master
field. The subform is a justified form.

The subform works fine, except that it often (not always) displays
each record 6 times. So, if there are 6 records that match the
master field, 36 are shown. When you advance through them, each
record is repeated 6 times.
Click to expand...

What's the recordsource query of the subform? It sounds like the
subform maybe based on a query that joins two or more tables, and
sometime one of those tables has 6 records that match the join key.
That would result in 6 copies of the resulting record.
 
C

Craig

Yes, the subform is based on a query, and yes it does join tables together.
Is there a way to stop all the copying?
 
C

Craig

Yes, the subform is based on a query that joins tables. Is there a way to
fix it without redesigning everything?
 
D

Dirk Goldgar

Craig said:
Yes, the subform is based on a query, and yes it does join tables
together. Is there a way to stop all the copying?
Click to expand...

My point was that, when you join tables in a query, records on one side
of the join that have multiple matches on the other side will be
repeated as many times as they have matches. I'm not sure, but I think
that may be what's going on with your subform.

Please post the recordsources for both the main form and the subform.
If the recordsource is a stored query, please post the SQL of that
query. With luck, that will let me tell you where to look for the cause
of the problem.
 
C

Craig

The part of the database I'm having this problem with is for lesson plans. I
work at a Canadian Forces trade school. I have very little training on
Access, so if I say anything stupid, give you too much/not enough/wrong info,
or don't understand something . . . my apologies in advance.

I built the main form with a series of 4 unbound combo boxes that use VBA to
limit subsequent combo boxes. The sources are a query (qryLessonPlans) that
accesses three different tables. The first combo selects a course from table
#1 (CrseData), then the 2nd and 3rd boxes select phase of the course and main
topic from table #2 (POData), and the last box selects a specific subject
from table #3 (EOData). These selections result in an unbound list box that
shows a unique subject code from table #3. This code is the Master Field.
The SQL for the list box is:

SELECT DISTINCT qryLessonPlans.eoPOEOID
FROM qryLessonPlans
WHERE (((qryLessonPlans.cdCrse)=[Combo16].[value]) AND
((qryLessonPlans.poPhase)=[Combo18].[value]) AND
((qryLessonPlans.poPONumber)=[Combo20].[value]) AND
((qryLessonPlans.eoEONumber)=[Combo22].[value]));

The subform in question (sbfPresentations) is based on a query
(qryPresentations). The subforms's Child Field is a unique lesson code,
which uses the same subject code as is in the Master Field, but adds 1-5
characters at the end. The SQL for qryPresentations is:

SELECT [qryLessonPlans].[eoPOEOID], [10tblLessons].[lTP],
[10tblLessons].[lTPDesc], [10tblLessons].[lLessonID],
[10tblLessons].[lLessonName], [10tblLessons].[lLessonDesc],
[10tblLessons].[lPeriods], [10tblLessons].[lSlides],
[10tblLessons].[lDatePost], [10tblLessons].[lDateReview],
[10tblLessons].[lAuthor], [10tblLessons].[lReviewer],
[10tblLessons].[lNotes], [10tblLessons].[lSlide],
[10tblLessons].[lLessonLink], [10tblLessons].[lDocLink],
[10tblLessons].[lZipLink]
FROM (05tblEOData INNER JOIN qryLessonPlans ON
[05tblEOData].[eoPOEOID]=[qryLessonPlans].[eoPOEOID]) INNER JOIN 10tblLessons
ON ([05tblEOData].[eoPOEOID]=[10tblLessons].[eoPOEOID]) AND
([qryLessonPlans].[eoPOEOID]=[10tblLessons].[eoPOEOID]);

I tried changing the opening from SELECT to SELECT DISTINCT, but there are
memo, OLE, and hyperlink fields. I tried removing those fields from the
query and having the form draw them straight from the table, but they won't
play that way.

Help?

Craig.
 
D

Dirk Goldgar

Craig said:
The part of the database I'm having this problem with is for lesson
plans. I work at a Canadian Forces trade school. I have very little
training on Access, so if I say anything stupid, give you too
much/not enough/wrong info, or don't understand something . . . my
apologies in advance.

I built the main form with a series of 4 unbound combo boxes that use
VBA to limit subsequent combo boxes. The sources are a query
(qryLessonPlans) that accesses three different tables. The first
combo selects a course from table #1 (CrseData), then the 2nd and 3rd
boxes select phase of the course and main topic from table #2
(POData), and the last box selects a specific subject from table #3
(EOData). These selections result in an unbound list box that shows
a unique subject code from table #3. This code is the Master Field.
The SQL for the list box is:

SELECT DISTINCT qryLessonPlans.eoPOEOID
FROM qryLessonPlans
WHERE (((qryLessonPlans.cdCrse)=[Combo16].[value]) AND
((qryLessonPlans.poPhase)=[Combo18].[value]) AND
((qryLessonPlans.poPONumber)=[Combo20].[value]) AND
((qryLessonPlans.eoEONumber)=[Combo22].[value]));

The subform in question (sbfPresentations) is based on a query
(qryPresentations). The subforms's Child Field is a unique lesson
code, which uses the same subject code as is in the Master Field, but
adds 1-5 characters at the end. The SQL for qryPresentations is:

SELECT [qryLessonPlans].[eoPOEOID], [10tblLessons].[lTP],
[10tblLessons].[lTPDesc], [10tblLessons].[lLessonID],
[10tblLessons].[lLessonName], [10tblLessons].[lLessonDesc],
[10tblLessons].[lPeriods], [10tblLessons].[lSlides],
[10tblLessons].[lDatePost], [10tblLessons].[lDateReview],
[10tblLessons].[lAuthor], [10tblLessons].[lReviewer],
[10tblLessons].[lNotes], [10tblLessons].[lSlide],
[10tblLessons].[lLessonLink], [10tblLessons].[lDocLink],
[10tblLessons].[lZipLink]
FROM (05tblEOData INNER JOIN qryLessonPlans ON
[05tblEOData].[eoPOEOID]=[qryLessonPlans].[eoPOEOID]) INNER JOIN
10tblLessons ON ([05tblEOData].[eoPOEOID]=[10tblLessons].[eoPOEOID])
AND ([qryLessonPlans].[eoPOEOID]=[10tblLessons].[eoPOEOID]);

I tried changing the opening from SELECT to SELECT DISTINCT, but
there are memo, OLE, and hyperlink fields. I tried removing those
fields from the query and having the form draw them straight from the
table, but they won't play that way.
Click to expand...

I'm not sure what all these tables are and how they are related, but the
joins in that query look a bit skewed to me. My first question is, what
happens if you replace the recordsource of the subform with the simple
table, "10tblLessons"?
 
Top