Queries Going Loopy - Help

[rfear]

The record source for a form can either be a table or a query right ?

So in theory I should be able to design a form that populates more than one table at once, the common link being the query.

So how come when I design a query to bring together 2 tables am I prevented from modifying the data in the query. The fields act as though they are locked despite being unquely named and traceable to a unique field in each table ?

If a query only references one table I'm fine, if I introduce another table and a link ( e.g. common ID number ) I'm prevented from changing any of the fields.

 

[odin1701]

You will probably see upon opening the form a message at the bottom that says "This Recordset is not updateable" or similar.

Regardless if you think it's fine, there is at least one field which it cannot uniqely identify based on your query. This is usually due to how the tables are joined.

Can you post a list of fields for each table, which are primary keys, and how they are listed/linked in the query?

If you have two fields in both tables which are named the same (other than the primary key), you can run into problems.

Really need to see more of the DB to be able to help though.

 

[rfear]

Ah ha.

The new tables I created had no primary keys - didn't see the point of defining them at the time.

Now that I have defined primary keys all seems to be stable again - at least I can update the fields in my query.

Reading the small print, primary keys are mandatory to define relationships between tables. Guess that is where I was going wrong.

My form ( with tabs ) is also displaying correctly, which was another issue.

Thanks.

P.S. Why is it with access that the error message it displays never seems to be the route cause of the problem ?