Complex query that isn't updateable anymore

[Larsson]

I have a complex Select query that uses many left joins again one main table.
It works fine and I can update the main table.

But when I add one more table (with a left join) I can't update the main table any more.

I have tried remove one of the other tables, doesn't help.
I have tried to make the field primary key that I join with, doesn't help.

Somehow Access can't understand what to update any more.
Is there a way to tell Access that I am only interested in updating the main table?


The purpose of the query is to show the user a lot of information and let the user change one field in the main table.

Any thoughts?

 

[Golom]

Outer joins will not be updatable if you haven't included the primary key field(s) in the SELECT. Without primary keys, Access can't figure out which record to update.

 

[Larsson]

Well, I'm not interested in updating the outer joins, only the main table.
I did as you said, added the primary keys, but no change.
I can't still update the main table.

The query has one main table and eight tables that are joined with left join.
After I added the last table, I couldn’t update the main table anymore.

So it has to be something with that table.
The field in the main table is indexed with (Duplicates OK).
The join field in the table is also indexed with (Duplicates OK).
None of the fields is a primary key.

I have done similar joins with the other tables and that worked.

Any more thoughts?

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Larsson]

I have read that paper and it doesn't help.

But I have thought about the problem and will solve it through some code that runs on the forms On Current event.

Not so nice or fast, it is slower, but the users get the info they need.