Works in the query, but not the form!!

[Jacquie]

I have two tables -- xTeachers and xSchools. Linking field is SchoolName, and
it is set up with referential integrity with Schools (1) and Teachers (many)
relationship.

Here's the thing -- I open the query and I can add a teacher and a school.
Alliance High School shows up in the drop down list.

I open the form, and Alliance High School DOES NOT show up in the list. If I
try to pick something from the list, I get this message:


The current field must match the join key <name> in the table that serves as
the 'one' side of one-to-many relationship. (Error 3341)
You have tried to enter a value into the join field in the many-only table
of an outer join with no corresponding value in the join field of the "one"
side table.

The message includes the following instruction: Enter a record in the "one"
side table with the desired key value, and then make the entry with the
desired join field in the "many-only" table.


I am bamboozled. I've compacted and repaired the database several times, but
I still get the same error. Any ideas? Thanks!!

 

[J_Goddard via AccessMonster.com]

Hi -

In your query, what type of relationship have you used to join the two tables?
You should set it up such that schools with no teachers are still shown - an
outer join.

I suggest though, you shouldn't be adding schools and teachers through a
query like that anyway. Use a separate form for each - after all, you will
be adding teachers far more often than you will be adding schools. In the
form to add (or update) the teacher information, you can have a combo box
which lists the schools from the xSchools table.

Hope this helps

John

 

[Jacquie]

It's set up to show all teachers and only schools that have teachers
assigned. The Alliance High School has a teacher assigned, so it should show
up. And it does ... in the query, but not the form. This form was working for
3 months before today.

 

[Jacquie]

The query is set up to show all teachers and only those high schools which
have teachers assigned . Alliance High School has a teacher assigned, so it
should show up --- and it does, in the query but not the form. This form was
working for 3 months before it started acting strangely today.