Form and Subform problem

First let me say that I really don't know how to put this problem into words.
So I am sorry if this is extremely confusing.

I have form that contains a subform with a continuous forms view. The
subform has 4 data fields in it. Data has been entered from the main form
into the subform.

When I try to pull a query it gives each record in the main form each of the
data entries that were made. For example, if I have 20 records in my
database and then enter into the subform of one of these records on my main
form, when I try to pull a query it gives each of the 20 records from the
main form all of the same records from the subform. So essentially if I have
20 records in the database and then I enter 2 records into the subform of one
of these main records, when I pull the query, it brings up 40 records.

The way I am setting up the query is this - First setting in the main form
and then linking it to the subform.

Please help!!!

As far as i understood your problem you have a main form that has some
fields, some questions about the main form
1)Does main form Control Source is a table?
2)Does subform control source is also a table?
If both answer YES then what is the relationship betwen these two
tables. It should be one to many,,, meaning one field of table in main
can have many records in subform table.
If you have it this way so far, then i think the better way of doing it
is to have an unbound combo box on your main and when u create the
combo box with the wizard slect option 3.
in the Master field put only the Primary key of the table1 and the same
Primary key should be as Foreign Key in table2 so put the same key in
the Child, in the subform control properties.
When you select one record in your combo box, it should populate every
field for that record in your main form as well as in the subform.

Good Luck
Moe

Annie,

Can you go to the design view of the query, select SQL from the View
menu, and copy/paste the SQL view of the query into your reply here.
Also let us know what tables/queries are the Record Source of the main
form and the subform.

--
Steve Schapel, Microsoft Access MVP


Annie Litchfield (UNF Honors Program) wrote:
First let me say that I really don't know how to put this problem into words.
So I am sorry if this is extremely confusing.

I have form that contains a subform with a continuous forms view. The
subform has 4 data fields in it. Data has been entered from the main form
into the subform.

When I try to pull a query it gives each record in the main form each of the
data entries that were made. For example, if I have 20 records in my
database and then enter into the subform of one of these records on my main
form, when I try to pull a query it gives each of the 20 records from the
main form all of the same records from the subform. So essentially if I have
20 records in the database and then I enter 2 records into the subform of one
of these main records, when I pull the query, it brings up 40 records.

The way I am setting up the query is this - First setting in the main form
and then linking it to the subform.

Please help!!!

A subform will normally be the "many" side of a "one-to-many" relationship.
The main form will be the "one" side. An example of this would be the main
form would have a company and the subform would list the products made by
that company.

In the query, you would place the two tables, tblCompanies and tblProducts,
and link them together on the CompanyID field in each table. If you create
the query with both tables but without the link, you'll get a Cartesian
product, which is you will get one copy of the many side for each record in
the one side. You set up the main form/subform the same way. You would
connect the tblCompanies table to the main form and the tblProducts table to
the subform. In the subform control on the main form (the control that holds
the subform) you would then set the Parent and Child Link Fields properties
to the field on each form that has the CompanyID. Once that is done, when
you go to a record on the main form, the subform will automatically display
those records from the tblProducts table that belong to the company
displayed on the main form.

--
Wayne Morgan
MS Access MVP


"Annie Litchfield (UNF Honors Program)" Annie Litchfield (UNF Honors
wrote in message
...
First let me say that I really don't know how to put this problem into
words.
So I am sorry if this is extremely confusing.

I have form that contains a subform with a continuous forms view. The
subform has 4 data fields in it. Data has been entered from the main form
into the subform.

When I try to pull a query it gives each record in the main form each of
the
data entries that were made. For example, if I have 20 records in my
database and then enter into the subform of one of these records on my
main
form, when I try to pull a query it gives each of the 20 records from the
main form all of the same records from the subform. So essentially if I
have
20 records in the database and then I enter 2 records into the subform of
one
of these main records, when I pull the query, it brings up 40 records.

The way I am setting up the query is this - First setting in the main form
and then linking it to the subform.

Please help!!!

Steve,
Thanks so much for replying. I think that I messed up my db even more while
I was awaiting the reply...lol. How much worse can this get?

I am just going to start with my original problem - even though the records
from the subform are linked to the right records on the subform, when I pull
a query it links each of the entries to the wrong record on the main form.
This is how the db is setup - each of the records on the form are student
records and then the subform includes what their cum GPA was and semester GPA
was for each semester. When I pull the query, the data in the subform comes
up but it links it to the wrong student.

This is the SQL View of the query:

SELECT Students.[Social Security Number], Students.[First Name],
Students.[Last Name], [Semester Progress].[Semester Dates], [Semester
Progress].[Semester GPA], [Semester Progress].[Cumulative GPA]
FROM Students INNER JOIN [Semester Progress] ON Students.ID = [Semester
Progress].ID;

Record source for the main form is Students and record source for the
subform is Semester Progress. Any advice would be greatly appreciated...

Thanks,
Annie


"Steve Schapel" wrote:

Annie,

Can you go to the design view of the query, select SQL from the View
menu, and copy/paste the SQL view of the query into your reply here.
Also let us know what tables/queries are the Record Source of the main
form and the subform.

--
Steve Schapel, Microsoft Access MVP


Annie Litchfield (UNF Honors Program) wrote:
First let me say that I really don't know how to put this problem into words.
So I am sorry if this is extremely confusing.

I have form that contains a subform with a continuous forms view. The
subform has 4 data fields in it. Data has been entered from the main form
into the subform.

When I try to pull a query it gives each record in the main form each of the
data entries that were made. For example, if I have 20 records in my
database and then enter into the subform of one of these records on my main
form, when I try to pull a query it gives each of the 20 records from the
main form all of the same records from the subform. So essentially if I have
20 records in the database and then I enter 2 records into the subform of one
of these main records, when I pull the query, it brings up 40 records.

The way I am setting up the query is this - First setting in the main form
and then linking it to the subform.

Please help!!!

Wayne,
Can you take a look at the reply I made to Steve Schapel. I am back to my
original problem which might make more sense.

Thanks,
Annie

"Wayne Morgan" wrote:

A subform will normally be the "many" side of a "one-to-many" relationship.
The main form will be the "one" side. An example of this would be the main
form would have a company and the subform would list the products made by
that company.

In the query, you would place the two tables, tblCompanies and tblProducts,
and link them together on the CompanyID field in each table. If you create
the query with both tables but without the link, you'll get a Cartesian
product, which is you will get one copy of the many side for each record in
the one side. You set up the main form/subform the same way. You would
connect the tblCompanies table to the main form and the tblProducts table to
the subform. In the subform control on the main form (the control that holds
the subform) you would then set the Parent and Child Link Fields properties
to the field on each form that has the CompanyID. Once that is done, when
you go to a record on the main form, the subform will automatically display
those records from the tblProducts table that belong to the company
displayed on the main form.

--
Wayne Morgan
MS Access MVP


"Annie Litchfield (UNF Honors Program)" Annie Litchfield (UNF Honors
wrote in message
...
First let me say that I really don't know how to put this problem into
words.
So I am sorry if this is extremely confusing.

I have form that contains a subform with a continuous forms view. The
subform has 4 data fields in it. Data has been entered from the main form
into the subform.

When I try to pull a query it gives each record in the main form each of
the
data entries that were made. For example, if I have 20 records in my
database and then enter into the subform of one of these records on my
main
form, when I try to pull a query it gives each of the 20 records from the
main form all of the same records from the subform. So essentially if I
have
20 records in the database and then I enter 2 records into the subform of
one
of these main records, when I pull the query, it brings up 40 records.

The way I am setting up the query is this - First setting in the main form
and then linking it to the subform.

Please help!!!

(Sorry this is so late, my Internet connection went down just before I
pressed the send button)

There needs to be a common field between the form and its subform. This
field should be unique for each record on the main form (i.e. no two
students can have the same value in this field). Once that is done, open the
form in design view, open the Properties dialog box, and click on the
subform ONE time. This will actually give the properties of the subform
control on the main form. This control is what holds the subform. If you
click twice, you'll be in the subform, not the control holding it. If that
happens, just click on the background of the main form and try again. If you
are at the subform control, the Data tab of the Properties dialog will have
the properties "Link Child Fields" and "Link Master Fields". These are the
names of the fields on each form (subform and main form) that I mentioned
above. Place the name of the common field form the subform in "Link Child
Fields" and the name of the common field from the main form in "Link Master
Fields". The fields may or may not have the same name on both forms. Save
the changes and open the form. They should now be synchronized.

--
Wayne Morgan
MS Access MVP


"Annie Litchfield (UNF Honors Program)"
soft.com wrote in message
...
Wayne,
Can you take a look at the reply I made to Steve Schapel. I am back to my
original problem which might make more sense.

I have looked at both the form and the subform and I'm pretty sure that
everything you suggested was already done. Let me try to give a little more
information...

The table for the subform is "Semester Progress," which has the following
fields in it:
ID (set as primary key)
Student ID
Semester Dates
Semester GPA
Cumulative GPA
Semester Credit Hours
Dean's List

The subform's "Link Child Fields" is set to Student ID and the "Link Master
Fields" is set to ID. Does something look wrong with this??

-annie


"Wayne Morgan" wrote:

(Sorry this is so late, my Internet connection went down just before I
pressed the send button)

There needs to be a common field between the form and its subform. This
field should be unique for each record on the main form (i.e. no two
students can have the same value in this field). Once that is done, open the
form in design view, open the Properties dialog box, and click on the
subform ONE time. This will actually give the properties of the subform
control on the main form. This control is what holds the subform. If you
click twice, you'll be in the subform, not the control holding it. If that
happens, just click on the background of the main form and try again. If you
are at the subform control, the Data tab of the Properties dialog will have
the properties "Link Child Fields" and "Link Master Fields". These are the
names of the fields on each form (subform and main form) that I mentioned
above. Place the name of the common field form the subform in "Link Child
Fields" and the name of the common field from the main form in "Link Master
Fields". The fields may or may not have the same name on both forms. Save
the changes and open the form. They should now be synchronized.

--
Wayne Morgan
MS Access MVP


"Annie Litchfield (UNF Honors Program)"
soft.com wrote in message
...
Wayne,
Can you take a look at the reply I made to Steve Schapel. I am back to my
original problem which might make more sense.

Annie,

I think this should be...
FROM Students INNER JOIN [Semester Progress] ON Students.ID =
[Semester Progress].[StudentID]

i.e. change ID to StudentID. It is the StudentID field in the Semester
Progress table that relates it to the ID of the student in the Students
table, no?

--
Steve Schapel, Microsoft Access MVP


Annie Litchfield (UNF Honors Program) wrote:
Steve,
Thanks so much for replying. I think that I messed up my db even more while
I was awaiting the reply...lol. How much worse can this get?

I am just going to start with my original problem - even though the records
from the subform are linked to the right records on the subform, when I pull
a query it links each of the entries to the wrong record on the main form.
This is how the db is setup - each of the records on the form are student
records and then the subform includes what their cum GPA was and semester GPA
was for each semester. When I pull the query, the data in the subform comes
up but it links it to the wrong student.

This is the SQL View of the query:

SELECT Students.[Social Security Number], Students.[First Name],
Students.[Last Name], [Semester Progress].[Semester Dates], [Semester
Progress].[Semester GPA], [Semester Progress].[Cumulative GPA]
FROM Students INNER JOIN [Semester Progress] ON Students.ID = [Semester
Progress].ID;

Record source for the main form is Students and record source for the
subform is Semester Progress. Any advice would be greatly appreciated...

Thanks,
Annie

That appears to be correct. I wonder if the index is corrupted on the field.
Have you done a Compact and Repair of the database? This will rewrite the
indexes on the fields. Primary keys are indexed fields. If the index is
corrupted it can send you to the wrong record.

--
Wayne Morgan
MS Access MVP


"Annie Litchfield (UNF Honors Program)"
soft.com wrote in message
...
I have looked at both the form and the subform and I'm pretty sure that
everything you suggested was already done. Let me try to give a little
more
information...

The table for the subform is "Semester Progress," which has the following
fields in it:
ID (set as primary key)
Student ID
Semester Dates
Semester GPA
Cumulative GPA
Semester Credit Hours
Dean's List

The subform's "Link Child Fields" is set to Student ID and the "Link
Master
Fields" is set to ID. Does something look wrong with this??