Recordset not updateable

[pugalenthi]

I have a database with many tables, queries and forms. The problem actually lies in one of the tables. This table has a field for which i had set previously as indexed and duplicates ok. But now when i change this property to no duplicates then my form becomes not updateable.

Actually a query is based on this table and the form in turn in based on this query.

Please help me solve this problem.

[MMcCarthy]

I have a database with many tables, queries and forms. The problem actually lies in one of the tables. This table has a field for which i had set previously as indexed and duplicates ok. But now when i change this property to no duplicates then my form becomes not updateable.

Actually a query is based on this table and the form in turn in based on this query.

Please help me solve this problem.

What is the full sql of the query behind the form and which field are we talking about.

Mary

[pugalenthi]

What is the full sql of the query behind the form and which field are we talking about.

Mary

The field is of data type number. The property of this particular field in the table was initially set as Indexed : Yes(duplicates ok). But now when i tried to change the property to Yes( no duplicates) then the form became not updateable. This table already has a primary key field.

There is a query based on this and another table. And based on the result of this query is the form. Now the form is un updateable.

I hope im clear ?.

[MMcCarthy]

The field is of data type number. The property of this particular field in the table was initially set as Indexed : Yes(duplicates ok). But now when i tried to change the property to Yes( no duplicates) then the form became not updateable. This table already has a primary key field.

There is a query based on this and another table. And based on the result of this query is the form. Now the form is un updateable.

I hope im clear ?.

I need the actual query. If you don't know how to get the sql then open the access query window and change the view to sql then just copy and past the query in here.

I will also need to know the name of the field in the query that we are talking about.

Mary

[pugalenthi]

I need the actual query. If you don't know how to get the sql then open the access query window and change the view to sql then just copy and past the query in here.

I will also need to know the name of the field in the query that we are talking about.

Mary

The name of the table is Alumni. The name of the field in this table is ARTS_ID.

A query named ARTS-Alumni details Query is based on this table and two other tables. The query is as follows :


SELECT ARTSaddresses.Alumnicheck, Alumni.Alumni_ID, Alumni.ARTS_ID, ARTSaddresses.Country_ID AS Alumni_Country_ID, Alumni.LastAddressUpdate, Alumni.[Image?], Alumni.[Abstract?], Alumni.[cv?], Thesis.Entry_ID, Thesis.Graduationdate, Alumni.Gradyear_ID, Thesis.Thesistitle, Thesis.Abstract, Thesis.Supervisor_ID, Thesis.Cosupervisor_ID, Alumni.link1, Alumni.link2, Alumni.link3, Alumni.link4, ARTSaddresses.DateUpdated, ARTSaddresses.Clubcheck, ARTSaddresses.sex, ARTSaddresses.Title, ARTSaddresses.FirstName, ARTSaddresses.MiddleName, ARTSaddresses.LastName, ARTSaddresses.Address1, ARTSaddresses.Address2, ARTSaddresses.Address3, ARTSaddresses.City, ARTSaddresses.StateOrProvince, ARTSaddresses.PostalCode, ARTSaddresses.Country, ARTSaddresses.EmailAddressoff, ARTSaddresses.EmailAddresspriv, ARTSaddresses.HomePhone, ARTSaddresses.WorkPhone, ARTSaddresses.MobilePhone, ARTSaddresses.FaxNumber, ARTSaddresses.Picture, ARTSaddresses.Country_ID AS ARTSaddresses_Country_ID
FROM (ARTSaddresses INNER JOIN Alumni ON ARTSaddresses.ARTS_ID = Alumni.ARTS_ID) INNER JOIN Thesis ON ARTSaddresses.ARTS_ID = Thesis.ARTS_ID
WHERE (((ARTSaddresses.Alumnicheck)=True));


There is another query named Arts-Admin Database which is based on the above mentioned query , 3 more other queries and 1 table. The query is as follows :

SELECT [ARTS-Alumni details Query].[ARTS_ID], [Arts-club Member details Query].[ARTS_ID], [Arts-Student details Query].[ARTS_ID], [ARTS Advisor details query].[ARTS_ID], [ARTSaddresses].[ARTS_ID], [Arts-club Member details Query].[Member_id], [Arts-club Member details Query].[Join_date], [Arts-club Member details Query].[Resign_date], [Arts-club Member details Query].[Member_status], [Arts-club Member details Query].[Bank_name], [Arts-club Member details Query].[Account_no], [Arts-club Member details Query].[Bank_no], [Arts-club Member details Query].[Abbucherm], [Arts-club Member details Query].[confirmationsent?], [ARTSaddresses].[DateUpdated], [ARTSaddresses].[Alumnicheck], [ARTSaddresses].[Studentcheck], [ARTSaddresses].[sex], [ARTSaddresses].[Title], [ARTSaddresses].[FirstName], [ARTSaddresses].[MiddleName], [ARTSaddresses].[LastName], [ARTSaddresses].[Address1], [ARTSaddresses].[Address2], [ARTSaddresses].[Address3], [ARTSaddresses].[City], [ARTSaddresses].[StateOrProvince], [ARTSaddresses].[PostalCode], [ARTSaddresses].[Country], [ARTSaddresses].[EmailAddressoff], [ARTSaddresses].[EmailAddresspriv], [ARTSaddresses].[HomePhone], [ARTSaddresses].[WorkPhone], [ARTSaddresses].[MobilePhone], [ARTSaddresses].[FaxNumber], [ARTSaddresses].[Picture], [ARTSaddresses].[Country_ID], [Arts-Student details Query].[Entry_ID], [Arts-Student details Query].[MatrikelNo], [Arts-Student details Query].[Donor], [Arts-Student details Query].[Advisor_ID], [Arts-Student details Query].[Graduationdate], [Arts-Student details Query].[Thesistitle], [Arts-Student details Query].[Supervisor_ID], [Arts-Student details Query].[Cosupervisor_ID], [Arts-Student details Query].[Library_reference], [Arts-Student details Query].[Clubfee_first_term?], [Arts-Student details Query].[Clubfee_second_term?], [Arts-Student details Query].[Clubfee_third_term?], [Arts-Student details Query].[Clubfee_fourth_term?], [ARTSaddresses].[Clubcheck], [ARTS-Alumni details Query].[Alumni_ID], [ARTS-Alumni details Query].[LastAddressUpdate], [ARTS-Alumni details Query].[Image?], [ARTS-Alumni details Query].[Abstract?], [ARTS-Alumni details Query].[cv?], [ARTS-Alumni details Query].[Entry_ID], [ARTS-Alumni details Query].[Graduationdate], [ARTS-Alumni details Query].[Gradyear_ID], [ARTS-Alumni details Query].[Thesistitle], [ARTS-Alumni details Query].[Abstract], [ARTS-Alumni details Query].[Supervisor_ID], [ARTS-Alumni details Query].[Cosupervisor_ID], [ARTS-Alumni details Query].[link1], [ARTS-Alumni details Query].[link2], [ARTS-Alumni details Query].[link3], [ARTS-Alumni details Query].[link4], [ARTSaddresses].[Advisorcheck], [ARTS Advisor details query].[Advisor_ID], [ARTS Advisor details query].[Institute_ID], [ARTS Advisor details query].[Advisorwebsite], [ARTS Advisor details query].[Active], [ARTSaddresses].[Dropoutcheck], [Arts-club Member details Query].[ID_card?]
FROM (((ARTSaddresses LEFT JOIN [ARTS-Alumni details Query] ON [ARTSaddresses].[ARTS_ID]=[ARTS-Alumni details Query].[ARTS_ID]) LEFT JOIN [Arts-Student details Query] ON [ARTSaddresses].[ARTS_ID]=[Arts-Student details Query].[ARTS_ID]) LEFT JOIN [Arts-club Member details Query] ON [ARTSaddresses].[ARTS_ID]=[Arts-club Member details Query].[ARTS_ID]) LEFT JOIN [ARTS Advisor details query] ON [ARTSaddresses].[ARTS_ID]=[ARTS Advisor details query].[ARTS_ID]
ORDER BY [ARTSaddresses].[LastName];

And finally the form is based on this above query.

[MMcCarthy]

Starting with this query first. If you run it on it's own can you add a new record to the query?

SELECT ARTSaddresses.Alumnicheck, Alumni.Alumni_ID,
Alumni.ARTS_ID, ARTSaddresses.Country_ID AS Alumni_Country_ID, 
Alumni.LastAddressUpdate, Alumni.[Image?], Alumni.[Abstract?], Alumni.[cv?], 
Thesis.Entry_ID, Thesis.Graduationdate, Alumni.Gradyear_ID, Thesis.Thesistitle, 
Thesis.Abstract, Thesis.Supervisor_ID, Thesis.Cosupervisor_ID, Alumni.link1, 
Alumni.link2, Alumni.link3, Alumni.link4, ARTSaddresses.DateUpdated,
ARTSaddresses.Clubcheck, ARTSaddresses.sex, ARTSaddresses.Title,
ARTSaddresses.FirstName, ARTSaddresses.MiddleName, ARTSaddresses.LastName, 
ARTSaddresses.Address1, ARTSaddresses.Address2, ARTSaddresses.Address3, 
ARTSaddresses.City, ARTSaddresses.StateOrProvince, ARTSaddresses.PostalCode, 
ARTSaddresses.Country, ARTSaddresses.EmailAddressoff, ARTSaddresses.EmailAddresspriv, 
ARTSaddresses.HomePhone, ARTSaddresses.WorkPhone, ARTSaddresses.MobilePhone, 
ARTSaddresses.FaxNumber, ARTSaddresses.Picture,
ARTSaddresses.Country_ID AS ARTSaddresses_Country_ID
FROM (ARTSaddresses INNER JOIN Alumni 
ON ARTSaddresses.ARTS_ID = Alumni.ARTS_ID) 
INNER JOIN Thesis 
ON ARTSaddresses.ARTS_ID = Thesis.ARTS_ID
WHERE (((ARTSaddresses.Alumnicheck)=True));
 

[pugalenthi]

Starting with this query first. If you run it on it's own can you add a new record to the query?

SELECT ARTSaddresses.Alumnicheck, Alumni.Alumni_ID,
Alumni.ARTS_ID, ARTSaddresses.Country_ID AS Alumni_Country_ID, 
Alumni.LastAddressUpdate, Alumni.[Image?], Alumni.[Abstract?], Alumni.[cv?], 
Thesis.Entry_ID, Thesis.Graduationdate, Alumni.Gradyear_ID, Thesis.Thesistitle, 
Thesis.Abstract, Thesis.Supervisor_ID, Thesis.Cosupervisor_ID, Alumni.link1, 
Alumni.link2, Alumni.link3, Alumni.link4, ARTSaddresses.DateUpdated,
ARTSaddresses.Clubcheck, ARTSaddresses.sex, ARTSaddresses.Title,
ARTSaddresses.FirstName, ARTSaddresses.MiddleName, ARTSaddresses.LastName, 
ARTSaddresses.Address1, ARTSaddresses.Address2, ARTSaddresses.Address3, 
ARTSaddresses.City, ARTSaddresses.StateOrProvince, ARTSaddresses.PostalCode, 
ARTSaddresses.Country, ARTSaddresses.EmailAddressoff, ARTSaddresses.EmailAddresspriv, 
ARTSaddresses.HomePhone, ARTSaddresses.WorkPhone, ARTSaddresses.MobilePhone, 
ARTSaddresses.FaxNumber, ARTSaddresses.Picture,
ARTSaddresses.Country_ID AS ARTSaddresses_Country_ID
FROM (ARTSaddresses INNER JOIN Alumni 
ON ARTSaddresses.ARTS_ID = Alumni.ARTS_ID) 
INNER JOIN Thesis 
ON ARTSaddresses.ARTS_ID = Thesis.ARTS_ID
WHERE (((ARTSaddresses.Alumnicheck)=True));
 

Ya im able to add a new record to this above query.

[MMcCarthy]

Ok, now check both of the following queries to see if you can add a record. I don't expect the first one to be updateable and I want to see if the second one is:

SELECT [ARTS-Alumni details Query].[ARTS_ID], 
[ARTSaddresses].[DateUpdated], [ARTSaddresses].[Alumnicheck]
FROM (ARTSaddresses LEFT JOIN [ARTS-Alumni details Query]
ON [ARTSaddresses].[ARTS_ID]=[ARTS-Alumni details Query].[ARTS_ID]) 
 

SELECT [ARTSaddresses].[DateUpdated], [ARTSaddresses].[Alumnicheck]
 FROM (ARTSaddresses LEFT JOIN [ARTS-Alumni details Query]
 ON [ARTSaddresses].[ARTS_ID]=[ARTS-Alumni details Query].[ARTS_ID]) 
 

[pugalenthi]

Ok, now check both of the following queries to see if you can add a record. I don't expect the first one to be updateable and I want to see if the second one is:

SELECT [ARTS-Alumni details Query].[ARTS_ID], 
[ARTSaddresses].[DateUpdated], [ARTSaddresses].[Alumnicheck]
FROM (ARTSaddresses LEFT JOIN [ARTS-Alumni details Query]
ON [ARTSaddresses].[ARTS_ID]=[ARTS-Alumni details Query].[ARTS_ID]) 
 

SELECT [ARTSaddresses].[DateUpdated], [ARTSaddresses].[Alumnicheck]
 FROM (ARTSaddresses LEFT JOIN [ARTS-Alumni details Query]
 ON [ARTSaddresses].[ARTS_ID]=[ARTS-Alumni details Query].[ARTS_ID]) 
 

I ran both the above queries and both are un updateable.

[MMcCarthy]

I ran both the above queries and both are un updateable.

That's strange.

If you run the form query in full, is it updatable outside the form?

[pugalenthi]

That's strange.

If you run the form query in full, is it updatable outside the form?

I guess you mean the query namely Arts-Admin Database on which the form is based.

No that query is not updateable. But once i change the property of that particular field in the table stated before back to Indexed : Yes (duplicates ok) then the updation problem vanishes.

[MMcCarthy]

I guess you mean the query namely Arts-Admin Database on which the form is based.

No that query is not updateable. But once i change the property of that particular field in the table stated before back to Indexed : Yes (duplicates ok) then the updation problem vanishes.

Obviously you are creating duplicate records. One of the tables you are joining in the final query has more than a one to one relationship with the Alumni table. If that table has more than one record for each record in the Alumni table then you can't include it in the form query. You will have to create a subform to allow the records for this table to be added. If you're not sure which table it is go to each one and in the ArtsID field or other foreign key (field on which you join) change the index to no duplicates. The table which won't allow you is the table which will need to go into a subform.

Mary