Form and Subform problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
Program)@discussions.microsoft.com> wrote in message
news:[email protected]...
 
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
 
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
 
(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)"
 
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
 
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?
 
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)"
 
Wayne,

My understanding is that the forms are working well, and the info you
have been looking at would confirm that. My understanding is that Annie
was than having trouble getting her query based on the related tables to
work for her. I think she was joining the tables on the wrong field -
have a look at my reply on the other "sub-thread" and see what you think.
 
OH MY GOD! IT WORKED!!! Thank you so much! I have been working on this for
over 3 days. Thank you both for all of your help. :)

-Annie
 
Good to hear of your success. Thanks for letting us know, Annie. Best
wishes with the rest of your project.
 
Steve,
One last question/problem - when that particular query is deleted and then
someone tries to create a new one, they will have to fix the SQL view
everytime? Is there a way to prevent that? For my other subforms, my db
users can just create and delete queries without having to adjust this. Most
of my db users here only know the bare minimum but still need to be able to
manipulate queries and so forth. Is it possible to fix this?

-annie
 
Annie,

This doesn't need to get changed in the SQL view of the query. It can
be done in design view. It is simply a matter of drag'n'drop from the
ID field in the Students table to the StudentID field in the other
table. I think what has happened is that Access has "helpfully"
automatically put the Join between the tables in there for you when you
create the query, based on a "guess" because both tables contain an ID
field and at least one of them is a primary key. This problem would be
solved by re-naming the primary key field of the Students table to
StudentID... but this might then involve making changes in forms or
other queries to tidy this up. However, there should probably also be a
Relationship defined (Tools|Relationships menu) between these two
tables, and this should be based on [Students].[ID] = [Semester
Progress].[StudentID]. If you set this up, I am pretty sure the
"problem" would also disappear, i.e. the default join in a new query
would be based on the Relationship definition.

Just for the record, I would never in my wildest dreams allow database
users to have access to query design.
 
Steve,
Now this is exactly what I needed. It resolved my problem entirely.

Side note -- In my wildest dreams I would like for my db users NOT to have
access to query design. I have no real authority here. I just pray every
night in my real dreams that nothing bad happens. Thank god for backup, eh?
:)

Sincerely grateful for all of your help,
Annie

Steve Schapel said:
Annie,

This doesn't need to get changed in the SQL view of the query. It can
be done in design view. It is simply a matter of drag'n'drop from the
ID field in the Students table to the StudentID field in the other
table. I think what has happened is that Access has "helpfully"
automatically put the Join between the tables in there for you when you
create the query, based on a "guess" because both tables contain an ID
field and at least one of them is a primary key. This problem would be
solved by re-naming the primary key field of the Students table to
StudentID... but this might then involve making changes in forms or
other queries to tidy this up. However, there should probably also be a
Relationship defined (Tools|Relationships menu) between these two
tables, and this should be based on [Students].[ID] = [Semester
Progress].[StudentID]. If you set this up, I am pretty sure the
"problem" would also disappear, i.e. the default join in a new query
would be based on the Relationship definition.

Just for the record, I would never in my wildest dreams allow database
users to have access to query design.

--
Steve Schapel, Microsoft Access MVP

Steve,
One last question/problem - when that particular query is deleted and then
someone tries to create a new one, they will have to fix the SQL view
everytime? Is there a way to prevent that? For my other subforms, my db
users can just create and delete queries without having to adjust this. Most
of my db users here only know the bare minimum but still need to be able to
manipulate queries and so forth. Is it possible to fix this?
Click to expand...
Click to expand...
 
Back
Top