Updatable One-Many query - new 'one' table row (KB928023)

[pondlife]

In Acc2010 we are experiencing this error with a simple one-to-many updatable query, with the following message:

The current field must match the join key '?' in the table that serves as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table.​

The query is wrongly attempting to add a new row to the 'one' table when it adds a new row to the 'many' table.

KB928023 acknowledges this as an Acc2007 problem. It says the problem arises if the output list includes a 'complex' field (sic) from the one table. Our output list includes only one field from the one table: a non-complex long integer ID field, with 'Allow Multiple Values' set to False (and no other properties that might make this a 'complex' field).

Have any other forum members seen this behaviour? Is there a solution? (We need the field from the one table to be able to filter by that field.)

 

[boblarson]

Need a better explanation (and maybe you can upload a few screenshots here to show us what you have). Normally for a one to many, you don't have both tables in the query. Why do you have both tables in the query to update?

 

[pondlife]

Thanks for your reply Bob. This is a development project so I'm told I can't provide screenshots, sorry. The query is of this form:

SELECT tblMany.ManyID, tblMany.[OtherFields], tblMany.OneID, tblOne.TypeID
FROM tblOne INNER JOIN tblMany ON tblOne.OneID = tblMany.OneID;​

We need both tables so we can filter on tblOne.TypeID.

 

[boblarson]

A form with a one to many really should be a main form with the one table and the many table should be a subform on it. The OneID field would be the one using the Master/Child link.

But you can attempt to go into the query, right-click on the gray area where the tables show up and then select PROPERTIES. Then find the RECORDSET TYPE property and you can select DYNASET (INCONSISTENT UPDATES) instead of just Dynaset. That may work for you.

 

[Pat Hartman]

I would suggest removing the complex fields from the table. You can easily replicate the functionality without the hassle. I NEVER use the complex data types because they cannot be upsized to SQL Server or any other RDBMS so keep that in mind if portablity may be important in the future. They are an abomination caused by trying to integrate Access with Sharepoint.

 

[boblarson]

I would suggest removing the complex fields from the table. You can easily replicate the functionality without the hassle. I NEVER use the complex data types because they cannot be upsized to SQL Server or any other RDBMS so keep that in mind if portablity may be important in the future. They are an abomination caused by trying to integrate Access with Sharepoint.

 

[pondlife]

select DYNASET (INCONSISTENT UPDATES) instead of just Dynaset. That may work for you.

We have tried inconsistent updates, but the results were unpredictable.

 

[boblarson]

We have tried inconsistent updates, but the results were unpredictable.

Which is why I didn't suggest it first. You really should have it as a main form, subform instead. Then you should be good to go. The thing to remember is that you can format your subform so it doesn't necessarily look so much like a subform (although with multiple records it is more difficult to format not to look like one.

 

[pondlife]

I would suggest removing the complex fields from the table. You can easily replicate the functionality without the hassle. I NEVER use the complex data types because they cannot be upsized to SQL Server or any other RDBMS so keep that in mind if portablity may be important in the future. They are an abomination caused by trying to integrate Access with Sharepoint.

Thanks Pat. Although the One table contains several fields with complex data types, these are not used in the join or the field projected from the One table. Both those are Long Integers.

(Incidentally, is there a definition anywhere of which data types are counted as 'complex' data types?)

 

[pondlife]

Which is why I didn't suggest it first. You really should have it as a main form, subform instead. Then you should be good to go. The thing to remember is that you can format your subform so it doesn't necessarily look so much like a subform (although with multiple records it is more difficult to format not to look like one.

Overall we have a one-many-one relationship. The first 'one' table is the recordsource of the main form, the many-one query that is giving the problem is the recordsource of a (continuous form) subform.

 

[boblarson]

Overall we have a one-many-one relationship. The first 'one' table is the recordsource of the main form, the many-one query that is giving the problem is the recordsource of a (continuous form) subform.

Sorry but I cannot grasp how your data can have this type of relationship. I'm trying to think of an example but it is eluding me. Can you describe (without giving away any secrets) what each part represents? For example, my one is my house, my many is the persons in the house, and the one which also relates to all of the people in the house would be...?

If you really have a one-to-many-to-one then you just have two one-to-many relationships and would have each one be a main form and the many be a junction table for each with a subform for each.

But finding out what your data is like might be helpful in giving advice.

 

[pondlife]

The main form 'one' table is a pricelist, the 'many' table is the prices in the pricelist, and the second 'one' table is the products whose prices feature in the pricelist. Products have a type that we need to filter by. Does that help clarify?

 

[boblarson]

The main form 'one' table is a pricelist, the 'many' table is the prices in the pricelist, and the second 'one' table is the products whose prices feature in the pricelist. Products have a type that we need to filter by. Does that help clarify?

Actually it does. And you really only have a one-to-many situation. The only other wrinkle is that you probably have a price history.

So, anyway, the structure would normally be that you would have the products as the main form (one table) and the prices are the many (subform).

 

[pondlife]

Actually it does. And you really only have a one-to-many situation. The only other wrinkle is that you probably have a price history.

So, anyway, the structure would normally be that you would have the products as the main form (one table) and the prices are the many (subform).

Except that we have many pricelists for the same products (different suppliers, different circumstances).

What's concerning is not the underlying reasons for the design we have but the surprising behaviour that the design produces. KB928023 describes the same surprising behaviour in the presence of a complex field projected from the 'one' table, which is not present in our design.

What would be the best way to explore this with Microsoft?

 

[Pat Hartman]

You do have a complex field in the table even though you didn't select it in the query. Keep in mind that the ACE team had to completely rewrite their processing logic to handle complex fields and they also gave you brand new SQL syntax to use when referencing the fields. The price is simply too high for something I can do easly enough myself with standard SQL.

Contact their product support team. They will ask for a credit card but if it turns out to be an Access bug, they will not charge you. They may have a hot fix that solves the problem or you may be SOL. Personally, as I have said, the complex data types don't bring anything to the party so I don't use them.

 

[pondlife]

Thanks Pat. Understood (though I had to look up 'SOL'). I'll contact MS Support and report back here if anything useful transpires.
Incidentally, the KB928023 article is clear that it has to be the field projected into the query from the one table that, if complex, causes the trouble. (ie the simple presence of complex fields in the one table, unused in the query, shouldn't cause the issue - but I guess that's the potential bug).