cant edit record in query

[Kevin_S]

Hi Everyone,

I am at my wits end here and so I turn to you good people to see if you can help me figure out what is going wrong with my query. I have a simple query with 6 tables. There is a main table (dbo.WIP_MAIN) with a primary key (SubmissionID). There are 5 other tables in the query each with SubmissionID as their primary key. All of the joins for these tables are on the primary key and are all 1-1 joins. All of the tables are linked through ODBC as the backend is in SQL Server 2000 and the front end is MS Access xp (although I don't see why a linked table would make much of a difference on this...?) A screen shot of the query is attached.

Basically , I want to make this query the recordsource for a form in the db but for some reason when I attempt to enter or edit data in the form I can edit all of the tables except 1...????? It doesn't matter which table it is either - as in when I delete all of the tables and then add them in a different order then a different one is lock... is there a limit to the number of tables? I have done alot of reseach in this forum and in MS help (help actually goes into this topic quite extensively as a matter of fact) and I have made sure the query meets all quidelines relayed in help to be updatedable but still nothing works...?

Anyone have a suggestion?
thx,
Kevin

 

[domoputro]

I don't think you can add, edit, delete data in the query.
So, if you're making a form based on a query, You'll be using them for viewing only.

At least that's what I think.

Anyone ?

 

[The_Doc_Man]

You can, indeed, edit records based on a query, except that if one of the elements of the query is an SQL aggregate, you lose that ability. Because then there comes about a question as to which record contributing to the aggregate is the one to be updated.

 

[Kevin_S]

Hey Doc - any idea why I am running into this problem? I have went over this query with a fine toothed comb.... I was able to get the query updateable by including the primary key fields from each table in the query - everything worked fine then BUT... a result of this was that the subqueries in the main form that are independent of this query (due to the fact that the tables in them are many-to-1 with the main table) will not link because there are too many instances of the primary key in the query and I get this message:

'The object doesn't contain the automation object 'dbo_WIP_MAIN'


so.... I was trying to avoid this by getting the query to run without including all of the primary key fields...

One side note* I went back to the Access db I used to upsize the tables to SQL Server and created the exact same query and everything worked fine..???????

Is this a problem with linked tables or perhaps SQL Server?

all help is appreciated...
Kev

 

[Kevin_S]

Follow Up:

I was able to get this resolved by including in the query all of the primary key fields from each of the linked tables and then manipulating my form and the other sub queries to look at the appropriate field...

Thanks to all that helped...
Kev