Form and query don't work together

[robert693]

I have a form based on a query. The query is based on three tables. One is a table that hold info on hospitals and universities. Another is one that holds info on contacts at those institutions. The third joins the two and holds additional info from a survey that the contact sends in. I have a form based on this query. In it I am able to enter info for both the contact and the survey. When I enter info for the contact it works fine. But when I get to a field that pertains to the survey it does not allow me to and a message appears on the task bar below that says "cannot add record(s); join key of table 'Survey' not in recordset. Can anyone help with this?

 

[allanon]

Seems to me that it want you to select the keys in your query. I am not really sure but the first thing I would try is go to the query and select the keys in the servey table.

 

[grnzbra]

Check Access Help for "Not Updatable". There is a list of types of queries that are updatable and not updatable. There seem to very few that are updatable. Your query might be one of these. (Have you tried to update the query?)

 

[zambezibill]

I suspect your table structure needs a little tweaking.

First, please explain what your join table is doing. Is there a many-to-many relationship (1 institution can have many contacts and 1 contact may be related to many institutions) between the Institution and Contact tables?

If this is the case, then you do need a join table linking the 2 prior tables. The join table should have its own unique key field (an auto number field will serve) and foreign key fields corresponding to the primary key fields in the other 2 tables. (if there isn't a many-to-many relationship, yopu don't need a join table).

The survey information (assuming it has a one-to-many relationship with other data) should then be maintained in an entirely different table. This table will then presumably be related to data in the other tables in some fashion (your post doesn't give any indication how).

Once you have the data and table structure better normalized, the query construction will go much smoother.

One-to-one relationship (1 table)
One-to-many relationship (2 tables)
Many-to-many relationship (3 tables including a join table)

Cheers, Bill