I have a Form called Books driven by the table calle Books. The Books table has an autonumber field for the primary key. There is an Topics, also with an autonumber primary key, and a BookTopics table with an autonumber primary key and a foreign key field for each of the other two tables key fields.
The Books form has a subform called BookTopics, to allow more than one topic for a book, which is driven by a query which joins BookTopics and Topics by the TopicID fields. The parent child relationship between these two is on the BookID field
I have another subform, unbound and not visible. When one clicks on the select records button on the main form, the unbound subform is made visible with a series of field matching the main form's fields plus an Topics field. Criteria are entered in these fields and a where clause is created which is used as the filter property of the form.
A selection in the authors field loops through the records in the BookTopics table and selects all books having a TopicID matching the selection from the criteria subform, which are entered in an IN(BookID1, BookID2, etc.) The problem shows up on one topic for which there are so many books the the filter string containing the BookID for every book of that topic is so long that I get an error message saying the string was too big.
As an alternative, I tried changing the Topics criteria process to merely include the TopicID in the WHERE clause in the normal manner and combine it with a SQL statement of a query containing the Books table and the BookTopics table, displaying only the fields from the Books table and applying the Topic criteria to the TopicID in the BookTopics table. This is then set as the RecordSource for the Form. Everything works fine this way.
Except, the form is not updatable. A similar query is not updatabale. The underlying tables are all updatable. I know that sometimes queries are not updatabable, but I've never seen one this simple that wasn't updatable.
Can someone suggest a solution?
The Books form has a subform called BookTopics, to allow more than one topic for a book, which is driven by a query which joins BookTopics and Topics by the TopicID fields. The parent child relationship between these two is on the BookID field
I have another subform, unbound and not visible. When one clicks on the select records button on the main form, the unbound subform is made visible with a series of field matching the main form's fields plus an Topics field. Criteria are entered in these fields and a where clause is created which is used as the filter property of the form.
A selection in the authors field loops through the records in the BookTopics table and selects all books having a TopicID matching the selection from the criteria subform, which are entered in an IN(BookID1, BookID2, etc.) The problem shows up on one topic for which there are so many books the the filter string containing the BookID for every book of that topic is so long that I get an error message saying the string was too big.
As an alternative, I tried changing the Topics criteria process to merely include the TopicID in the WHERE clause in the normal manner and combine it with a SQL statement of a query containing the Books table and the BookTopics table, displaying only the fields from the Books table and applying the Topic criteria to the TopicID in the BookTopics table. This is then set as the RecordSource for the Form. Everything works fine this way.
Except, the form is not updatable. A similar query is not updatabale. The underlying tables are all updatable. I know that sometimes queries are not updatabable, but I've never seen one this simple that wasn't updatable.
Can someone suggest a solution?
