When joining several tables, I need one to be 'Updateable'

[omniaccess]

My goal is to have a list with each item having a selection checkbox for the user to select which items in the list they want to act upon (delete, email, add to a group, etc.)

The list is created by joining several tables together to get the information I need. That part works fine. I get all of the appropriate records listed with all of their detail. But, when I join another table that simply has a yes/no field and the ClientID (linked on the clientID to the other joined tables), I get the message "This recordset is not updateable". The reason for having a separate selection table is because each person has this table located on their own machine so that each one can make their own selections to the list independent of each other.

I have tried to connect this additional selection table to the other tables in every combination, with every joining type I could - It yields the same answer. I have even used an event to fill the selection table with matching entries, but still no luck.

I have been able to connect this selection table to just one other table and it work fine. But when it is connected to either multiple joined tables or a query of multiple joined tables, I get the "not updateable" message again.

What I have done temporarily until I can get this figured out is the following:

I created a temporary table by using a Make Table query to match all of the fields of the multiple-joined tables. I have an Append Query run to fill the temporary table with the results of the query. Each record in this temp table has a Yes/No checkbox as a field. Now, when the user checks a check box, it works fine because each record showing on the list is simply one row from one table and there is no problem with the "Not an Updateable Recordset" message.

It works okay, but the drawback is that the data is not live, and, whenever the client refreshes the list to get the most current data, they loose all of the checkmarks because the recordset is deleted and then recreated each time they refresh the list.

Again, ultimately I want them to be able to simply check off which items on the list they want.

 

[BNPMike]

There are a number of rules for making queries updatable. Search on microsoft.com.

The first one I'll try on you is Jet needs to know that the ClientID field on your checkbox table has unique values. Is it a declared primary key or does it have a unique index on it?

 

[willir]

omniaccess

Access needs to know which side is updatable. When you add a second table to the query, Access is confused on what to update and how.

Rather than fighting with complex queries, my preference is to keep it simple by using a main form and display the other tables in subforms. I can still update the data in the subform by using event procedures, but there is no ambigous SELECT statements.

If you still want to pursue the complex query approach, perhaps we can help you better if you post the query.

Richard

 

[BNPMike]

I've got it now:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp