not updateable recordset

[quacka]

First I really don't know a correct title...

I have 3 tables, that should be connected to each other. But something goes wrong with the thirth table. I really don't see what I do wrong. Maybe one of you can help me!

Tbl1:
ID (autonumber)
customer (number, long integer)
conn_A (number, long integer)
(and a few minor important fields)

(customer is to connect with a customer-table)

tbl2:
ID (autonumber)
conn_A (number, long integer)
Translation (text)

tbl3:
ID (autonumber)
conn_A (number, long integer)
Conn_B (text)

Conn_B is to connect with another table (no problem with that).

The values: (of course started with an automatically ID-number) (to make it a bit easier to read I added a "C","A", or "B" before the number..)

tbl1:
c1 A1
c1 A2
c1 A3
c2 A1
c2 A2
c3 A2
c4 A1
c4 A2
c4 A3

tbl2:
A1 - txt
A2 - txt2
A3 - txt3

tbl3:
A1 - B1
A1 - B2
A2 - B3
A2 - B4
A2 - B5
A3 - B6
A3 - B7


I can connect tbl1 and tbl2 (one to more-relation). I can use it in queries, I can change data via those queries.
The same for tbl2 and tbl3 (again one to more).
But when I try to connect tbl1, tbl2 and tbl3 in one query I get the correct data, but I cannot change data. So I get a non-updateable recordset.

What do I do wrong? And more important: how to correct it?

Thanks for your help!

 

[mikebaldam]

Hi, its a bit awarkward to see how they are linked fully...

But it sounds like Table 3's data has some lookups on Table 1 and Table 2, so in a query with all three open you cant update any of the details, as they'd have a knock-on effect in the other tables.

Prehaps the best thing to do is to update them individually and only list them in the relationship design.

If the query has any total details or other aggregiate functions you'll not be able to update it.

Hope this helps
Also if loaded onto a form, check the forms properties, the recordtype is set to dynaset.

 

[quacka]

I think you are right.
The problem was I tried to create a delete-query. I wanted to delete data from another table, based on this query. But this wasn't possible.
I thought it had something to do with not being updateable of the data.

But I now think the problem is that I try to delete data, based on the data that I want to delete (I think that is what you try to say with your knock-on effect): So not possible.

I will now try something different (a bit hard for me to explain: so I will try to do it and when it will not work I can always try to explain it..)

 

[Banana]

If you want to be able to delete a parent record and *all* child records that are related to it, you need to specify "Cascade Delete" for the relationship defined between two tables. To do so, go to relationship windows, double click on the line joining two table then check the Cascade Delete box.