Query - Recordset not updateable

[Armitage2k]

OK, I used the search engine of the forum and found several related threads, but since I am not using any macros or anything special, it all does not really apply to me.

I am using a query to pull data from totally 3 tables (which are all related amongst each other), but now find that when I display the data in my form, that I cannot change any of it.
A closer look reveals the status bar telling me that "The recordset is not updateable" error is displayed. A quick google takes me to this helpful website, which I again do not understand because I am fairly new to access

I checked the stated settings in the design view of my query, but all of them seem to be ok and do not solve my problem...
Any ideas? I am using Access 2007.

EDIT: for the record. Query has unique entries disabled, uses dynaset, as well is a trusted database.

thanks for the help,
A2k

 

[SOS]

You might want to post the database, but simply put as well, if you are trying to include tables that have a one-to-many or many-to-many relationship you should use a main form for the one side and a subform to handle the many side and ONLY include the tables in each form's record source that apply to that part. Don't include more than necessary.

 

[Armitage2k]

as you wish. enclosed the file. Please note that it is 2007 only.

THANKS!!
A2k

 

[SOS]

Your problem is you are trying to use one query for everything. You need to use a query based on one table for the different parts. So, you have one query for Investments and that is the basis for the subform for Investments. One query for Client information and that is the basis of the client form. And one query with the products table for products, but in your case you need a junction table for ClientsProducts where you can store the products selected for the client.

In my revision of your database I have done away with the query you were trying to use and linked to the tables via their own queries. And the products subform currently is disabled because you can't link the products table to clients as you don't have any way to link them (and that's why you need the junction table - like this

tblClientsProducts
ClientProductID - Autonumber
ClientID - Long Integer
ProductID - Long Integer

 

[Armitage2k]

SOS, you are my personal Jesus, thanks man

Just one last thing. Since you deleted my query, I had to adjust some links and references in my other forms. Now, all of a sudden, my form which is listing all the clients, does open the client details form, but uses this form for the respective client ONLY. Which means, when I open the clients data, I can see and edit it, but can go to the next record...

I am sure this has to do with my embedded macro I use to open the form with the specific record. any chance you can help me out on this? preferably I am looking for a macro which opens me the record according to the clientID I click in my listing.

Again, thanks a lot for your help! Makes my day
A2k