One more difficulty

[Eric IsWhoIAm]

Thank you, everyone, for your help earlier. I am having one more
inexplicable difficulty with my tables now.

Again, I have three tables: Courses (with an autonumber Course Number key,
and a test Course Name field); Instructors (with similar fields to Courses);
and finally, Courses and Instructors, which has fields Course Name (which
has a 1:1 relationship with Courses:Course Name), and Instructor Name (which
has a 1: many relationship with Instructors:Instructor Name, since although
each class will have one instructor, the instructor's name will show up many
times in the table).

My problem now is that, although the fields show up properly (Course Name
and Instructor Name are showing up as expected in table Courses and
Instructors' combo boxes), when I try to add a record, selecting a course
name and instructor name, anything that attempts to save the data results in
a "You cannot add or change a record because a related record is required in
table 'Courses'" error.

This confuses me; the Course Name info in the combo box is being pulled
directly from the Courses table, the Course Name field. It shows up exactly
the way it shows up in the Courses table. Why would it say that "a related
record is required in table 'Courses'", if it already contains that field
and data?

I had thought that maybe, just maybe, it would have the same problem with
table Instructors. I tried switching the order of the fields, so that it
analyzed the Instructor Name field first. No luck: The same error occurred.

I will continue trying to figure this out, but the problem seems to be
either that it thinks that the data that it pulled from table Courses is
actually not in table Courses (which makes no sense), or else that table
Courses should have an Instructor Name field inside of it (which would
defeat the purpose of what I'm trying to do). Any thoughts, anybody? Right
now, I'm open to any and all ideas!

Thank you,
Eric

 

[tina]

see my response in your previous thread on this topic.

hth

 

[Eric IsWhoIAm]

Tina,

Thank you again for your help! I deeply appreciate it. Making the changes of
adding the keys of the other two tables to my Courses and Instructors table
has worked! I will see if I can make it automagically fill in the Course ID
based on the Course Name, and likewise the Instructor ID based on the
Instructor Name. Thank you again for everything!

Gratefully,
Eric

 

[Pete D.]

You need to carefully review the relationships/indexes between the two
tables. It is telling you they are linked and your actions are not
supported due to the way they are linked.

 

[Eric IsWhoIAm]

Pete,

Thank you for your advice. I have already added the keys of the two
reference tables to my Courses and Instructors table, and changed the
relationships so that they references the keys. Now, when I select an
Instructor and a Course, it saves properly -- so long as the corresponding
Course ID and Instructor ID are properly selected.

The next (and last!) thing I want to do is to make it so that the Course ID
is properly updated whenever the Course Name is selected. Naturally, I want
to do the same thing for Instructor ID, but I can do that at my leisure,
once I know how to do the first. I have made a form which contains all
fields, so that when I get it to work, I will see it working properly. In
truth, I would like simply the underlying table column (Course ID or
Instructor ID) to be altered appropriately; it is not a combo box, since
it's simply an integer that relies on the corresponding table's AutoNumber
key. Once I get it to work, I want to alter the form so that the user only
sees the Course Name and Instructor Name portions, thus eliminating any need
for them to look at the underlying processes.

Do you (or anybody else) have any ideas for this?

Thank you all very much,
Eric

 

[Pete D.]

Use a combo box in the form and under source for combo box select the buton
to the right and create a query displaying what you want. Use the bound
column in the combo box to display the correct column from the query in the
form..

 

[Eric IsWhoIAm]

Pete,

I appreciate your advice; however, I am unsure if you truly understand my
problem. What I want is something similar to:

SELECT [Course Number] FROM Courses WHERE Courses.[Course Name] = [Courses
and Instructors].[Course Name];

I tried this query (or one very similar) in the Combo Box. The problem is,
it popped up a box asking me to input [Courses and Instructors].[Course
Name], even though I had already selected the Course Name in its combo box.
When I did, it then gave me the correct course number; however, even when I
later canceled and tried a new one, it would only show that number from then
on, no matter what Course Name I chose.

What I really want is, when I choose the Course Name in my Courses and
Instructors combo box, for Access to do the following:

--> Look in the Courses table.
--> Find the Course Name in the Courses table that corresponds to the Course
Name I chose in the Courses and Instructors form's combo box.
--> Find the Course Number that corresponds to the Course Name in that
table.
--> Fill in the Course Number (in the Courses and Instructors form or table)
with the Course Number it found in the previous step.

So, I know how to break down the procedure I want it to do, but I don't know
how to tell Access to do this.

Please, anybody: Can you help me to translate these instructions into a
procedure that Access will understand?

-- Eric

 

[Pete D.]

Your right, I don't! Have a nice day! Hopefully you'll listen to someone
that doesn't understand your problem but is still willing to hang in there
and try to help for free!

Pete,

I appreciate your advice; however, I am unsure if you truly understand my
problem. What I want is something similar to:

SELECT [Course Number] FROM Courses WHERE Courses.[Course Name] = [Courses
and Instructors].[Course Name];

I tried this query (or one very similar) in the Combo Box. The problem is,
it popped up a box asking me to input [Courses and Instructors].[Course
Name], even though I had already selected the Course Name in its combo
box. When I did, it then gave me the correct course number; however, even
when I later canceled and tried a new one, it would only show that number
from then on, no matter what Course Name I chose.

What I really want is, when I choose the Course Name in my Courses and
Instructors combo box, for Access to do the following:

--> Look in the Courses table.
--> Find the Course Name in the Courses table that corresponds to the
Course Name I chose in the Courses and Instructors form's combo box.
--> Find the Course Number that corresponds to the Course Name in that
table.
--> Fill in the Course Number (in the Courses and Instructors form or
table) with the Course Number it found in the previous step.

So, I know how to break down the procedure I want it to do, but I don't
know how to tell Access to do this.

Please, anybody: Can you help me to translate these instructions into a
procedure that Access will understand?

-- Eric

Use a combo box in the form and under source for combo box select the
buton to the right and create a query displaying what you want. Use the
bound column in the combo box to display the correct column from the
query in the form..

 

[Eric IsWhoIAm]

Pete,

I truly did appreciate your help. What I mentioned, I mentioned because it
had appeared that there wasn't any other way to handle it. However, I have
found a better, simpler way, through the help of not only you, but several
others on these Access newsgroups.

The fact that you're willing to try your best to help, even when you don't
know all the facts, says much about your integrity. I truly appreciate it,
and I hope that you don't feel that I was in any way being ungrateful. I was
merely trying to more accurately describe what my difficulty was.

-- Eric

Your right, I don't! Have a nice day! Hopefully you'll listen to someone
that doesn't understand your problem but is still willing to hang in there
and try to help for free!

Pete,

I appreciate your advice; however, I am unsure if you truly understand my
problem. What I want is something similar to:

SELECT [Course Number] FROM Courses WHERE Courses.[Course Name] =
[Courses and Instructors].[Course Name];

I tried this query (or one very similar) in the Combo Box. The problem
is, it popped up a box asking me to input [Courses and
Instructors].[Course Name], even though I had already selected the Course
Name in its combo box. When I did, it then gave me the correct course
number; however, even when I later canceled and tried a new one, it would
only show that number from then on, no matter what Course Name I chose.

What I really want is, when I choose the Course Name in my Courses and
Instructors combo box, for Access to do the following:

--> Look in the Courses table.
--> Find the Course Name in the Courses table that corresponds to the
Course Name I chose in the Courses and Instructors form's combo box.
--> Find the Course Number that corresponds to the Course Name in that
table.
--> Fill in the Course Number (in the Courses and Instructors form or
table) with the Course Number it found in the previous step.

So, I know how to break down the procedure I want it to do, but I don't
know how to tell Access to do this.

Please, anybody: Can you help me to translate these instructions into a
procedure that Access will understand?

-- Eric

Use a combo box in the form and under source for combo box select the
buton to the right and create a query displaying what you want. Use the
bound column in the combo box to display the correct column from the
query in the form..
Pete,

Thank you for your advice. I have already added the keys of the two
reference tables to my Courses and Instructors table, and changed the
relationships so that they references the keys. Now, when I select an
Instructor and a Course, it saves properly -- so long as the
corresponding Course ID and Instructor ID are properly selected.

The next (and last!) thing I want to do is to make it so that the
Course ID is properly updated whenever the Course Name is selected.
Naturally, I want to do the same thing for Instructor ID, but I can do
that at my leisure, once I know how to do the first. I have made a form
which contains all fields, so that when I get it to work, I will see it
working properly. In truth, I would like simply the underlying table
column (Course ID or Instructor ID) to be altered appropriately; it is
not a combo box, since it's simply an integer that relies on the
corresponding table's AutoNumber key. Once I get it to work, I want to
alter the form so that the user only sees the Course Name and
Instructor Name portions, thus eliminating any need for them to look at
the underlying processes.

Do you (or anybody else) have any ideas for this?

Thank you all very much,
Eric


You need to carefully review the relationships/indexes between the two
tables. It is telling you they are linked and your actions are not
supported due to the way they are linked.
Thank you, everyone, for your help earlier. I am having one more
inexplicable difficulty with my tables now.

Again, I have three tables: Courses (with an autonumber Course Number
key, and a test Course Name field); Instructors (with similar fields
to Courses); and finally, Courses and Instructors, which has fields
Course Name (which has a 1:1 relationship with Courses:Course Name),
and Instructor Name (which has a 1: many relationship with
Instructors:Instructor Name, since although each class will have one
instructor, the instructor's name will show up many times in the
table).

My problem now is that, although the fields show up properly (Course
Name and Instructor Name are showing up as expected in table Courses
and Instructors' combo boxes), when I try to add a record, selecting
a course name and instructor name, anything that attempts to save the
data results in a "You cannot add or change a record because a
related record is required in table 'Courses'" error.

This confuses me; the Course Name info in the combo box is being
pulled directly from the Courses table, the Course Name field. It
shows up exactly the way it shows up in the Courses table. Why would
it say that "a related record is required in table 'Courses'", if it
already contains that field and data?

I had thought that maybe, just maybe, it would have the same problem
with table Instructors. I tried switching the order of the fields, so
that it analyzed the Instructor Name field first. No luck: The same
error occurred.

I will continue trying to figure this out, but the problem seems to
be either that it thinks that the data that it pulled from table
Courses is actually not in table Courses (which makes no sense), or
else that table Courses should have an Instructor Name field inside
of it (which would defeat the purpose of what I'm trying to do). Any
thoughts, anybody? Right now, I'm open to any and all ideas!

Thank you,
Eric