I think I see your problem--it's in the keys. Neither the Events table nor the Entertainers table contains the complete key of the other. This makes the relationship between them many-to-many. Such queries can't be updateable.
To see why, consider that Events might have two rows as follows:
Code:
ContactID ReferenceNo
116 27
212 27
Similarly, Entertainers might have the following two rows:
Code:
ReferenceNo LineNo
27 1
27 2
The join between these tables will then have 4 rows for this reference number:
Code:
ContactID ReferenceNo LineNo
116 27 1
116 27 2
212 27 1
212 27 2
Now say you updated the first row, changing the ReferenceNo to 33. If this ReferenceNo column was the one from the Events table, Access would need to update Events.ReferenceNo. But now the Events.ReferenceNo no longer matches the Entertainers.ReferenceNo. Since only matching data should appear in this result table, the whole row must now disappear--and so must row 2, because it uses the same row from Events! Well, relational tables
just don't behave this way--rows don't disappear from them just because you updated a value. So updates are not allowed where a many-to-many relationship exists.
You might think that Access should automatically update Entertainers.ReferenceNo to 33 as well. Unfortunately, that would be no better. If it did that, row 3 of the result table would have to disappear, because it uses the same Entertainers row as row 1, which you just updated so it no longer matches Events.ReferenceNo in row 3. Again, the update in row 1 would force other rows to disappear.
Ultimately, the problem is either an incorrect selection of primary key columns, or in trying to do an ambiguous update in the subform. Not knowing what your tables represent in the real world, I can't be sure, but I think it's the latter. From a relational point of view, the Events table is a many-to-many association between customers and entertainers. But the fact that you call it "Events" suggests that you also intend it to represent events. It doesn't do that: an event is not defined by a combination of one customer with one entertainer. I suspect you're missing a table, and its corresponding key. You need to think out the real-world relationships (one-to-many's) in what you're trying to represent. Once you get the tables right, you should be able to see how the form/subform structure needs to change to accomodate it.
BTW, the effect of Inconsistent Updates is that you will be allowed to violate your relational integrity rules, which you set up in the Relationships window when you create the relationships. What this means is that, if you delete a row in a parent table, it won't cascade to the child tables, and if you update a foreign key, it will neither cascade nor be checked to ensure there is a related row in the parent table. In the example I gave above, using Inconsistent Updates prevents the problem of disappearing rows by allowing the rows with non-matching reference number (after you update it) to remain in the result table. Unfortunately, that means that as you update the result table, you're breaking matches left and right, and you will wind up with rows in one table that no longer have any match in the other. In reality, it's not too bad as long as you don't update the ReferenceNo column (the join column). The only problem is that when you update data in one row, values in other rows may "mysteriously" change as well. Use of an Inconsistent Updates query is not recommended. I've never found a valid use for one, in years of Jet database design. Rick Sprague
