Form and Subform problem

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.

--
Steve Schapel, Microsoft Access MVP


Wayne Morgan wrote:
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.

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

"Steve Schapel" wrote:

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.

--
Steve Schapel, Microsoft Access MVP


Wayne Morgan wrote:
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.

Good to hear of your success. Thanks for letting us know, Annie. Best
wishes with the rest of your project.

--
Steve Schapel, Microsoft Access MVP


Annie Litchfield (UNF Honors Program) wrote:
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.

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

"Steve Schapel" wrote:

Good to hear of your success. Thanks for letting us know, Annie. Best
wishes with the rest of your project.

--
Steve Schapel, Microsoft Access MVP


Annie Litchfield (UNF Honors Program) wrote:
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,

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


Annie Litchfield (UNF Honors Program) wrote:
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?

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" wrote:

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


Annie Litchfield (UNF Honors Program) wrote:
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?