By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Recordset not updateable

P: 44
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.
Jan 12 '07 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 12 '07 #2

P: 44
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 ?.
Jan 12 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 12 '07 #4

P: 44
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.
Jan 12 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Starting with this query first. If you run it on it's own can you add a new record to the query?

Expand|Select|Wrap|Line Numbers
  1. SELECT ARTSaddresses.Alumnicheck, Alumni.Alumni_ID,
  2. Alumni.ARTS_ID, ARTSaddresses.Country_ID AS Alumni_Country_ID, 
  3. Alumni.LastAddressUpdate, Alumni.[Image?], Alumni.[Abstract?], Alumni.[cv?], 
  4. Thesis.Entry_ID, Thesis.Graduationdate, Alumni.Gradyear_ID, Thesis.Thesistitle, 
  5. Thesis.Abstract, Thesis.Supervisor_ID, Thesis.Cosupervisor_ID, Alumni.link1, 
  6. Alumni.link2, Alumni.link3, Alumni.link4, ARTSaddresses.DateUpdated,
  7. ARTSaddresses.Clubcheck, ARTSaddresses.sex, ARTSaddresses.Title,
  8. ARTSaddresses.FirstName, ARTSaddresses.MiddleName, ARTSaddresses.LastName, 
  9. ARTSaddresses.Address1, ARTSaddresses.Address2, ARTSaddresses.Address3, 
  10. ARTSaddresses.City, ARTSaddresses.StateOrProvince, ARTSaddresses.PostalCode, 
  11. ARTSaddresses.Country, ARTSaddresses.EmailAddressoff, ARTSaddresses.EmailAddresspriv, 
  12. ARTSaddresses.HomePhone, ARTSaddresses.WorkPhone, ARTSaddresses.MobilePhone, 
  13. ARTSaddresses.FaxNumber, ARTSaddresses.Picture,
  14. ARTSaddresses.Country_ID AS ARTSaddresses_Country_ID
  15. FROM (ARTSaddresses INNER JOIN Alumni 
  16. ON ARTSaddresses.ARTS_ID = Alumni.ARTS_ID) 
  17. INNER JOIN Thesis 
  18. ON ARTSaddresses.ARTS_ID = Thesis.ARTS_ID
  19. WHERE (((ARTSaddresses.Alumnicheck)=True));
  20.  
Jan 12 '07 #6

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

Expand|Select|Wrap|Line Numbers
  1. SELECT ARTSaddresses.Alumnicheck, Alumni.Alumni_ID,
  2. Alumni.ARTS_ID, ARTSaddresses.Country_ID AS Alumni_Country_ID, 
  3. Alumni.LastAddressUpdate, Alumni.[Image?], Alumni.[Abstract?], Alumni.[cv?], 
  4. Thesis.Entry_ID, Thesis.Graduationdate, Alumni.Gradyear_ID, Thesis.Thesistitle, 
  5. Thesis.Abstract, Thesis.Supervisor_ID, Thesis.Cosupervisor_ID, Alumni.link1, 
  6. Alumni.link2, Alumni.link3, Alumni.link4, ARTSaddresses.DateUpdated,
  7. ARTSaddresses.Clubcheck, ARTSaddresses.sex, ARTSaddresses.Title,
  8. ARTSaddresses.FirstName, ARTSaddresses.MiddleName, ARTSaddresses.LastName, 
  9. ARTSaddresses.Address1, ARTSaddresses.Address2, ARTSaddresses.Address3, 
  10. ARTSaddresses.City, ARTSaddresses.StateOrProvince, ARTSaddresses.PostalCode, 
  11. ARTSaddresses.Country, ARTSaddresses.EmailAddressoff, ARTSaddresses.EmailAddresspriv, 
  12. ARTSaddresses.HomePhone, ARTSaddresses.WorkPhone, ARTSaddresses.MobilePhone, 
  13. ARTSaddresses.FaxNumber, ARTSaddresses.Picture,
  14. ARTSaddresses.Country_ID AS ARTSaddresses_Country_ID
  15. FROM (ARTSaddresses INNER JOIN Alumni 
  16. ON ARTSaddresses.ARTS_ID = Alumni.ARTS_ID) 
  17. INNER JOIN Thesis 
  18. ON ARTSaddresses.ARTS_ID = Thesis.ARTS_ID
  19. WHERE (((ARTSaddresses.Alumnicheck)=True));
  20.  

Ya im able to add a new record to this above query.
Jan 12 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT [ARTS-Alumni details Query].[ARTS_ID], 
  2. [ARTSaddresses].[DateUpdated], [ARTSaddresses].[Alumnicheck]
  3. FROM (ARTSaddresses LEFT JOIN [ARTS-Alumni details Query]
  4. ON [ARTSaddresses].[ARTS_ID]=[ARTS-Alumni details Query].[ARTS_ID]) 
  5.  
Expand|Select|Wrap|Line Numbers
  1. SELECT [ARTSaddresses].[DateUpdated], [ARTSaddresses].[Alumnicheck]
  2.  FROM (ARTSaddresses LEFT JOIN [ARTS-Alumni details Query]
  3.  ON [ARTSaddresses].[ARTS_ID]=[ARTS-Alumni details Query].[ARTS_ID]) 
  4.  
Jan 12 '07 #8

P: 44
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT [ARTS-Alumni details Query].[ARTS_ID], 
  2. [ARTSaddresses].[DateUpdated], [ARTSaddresses].[Alumnicheck]
  3. FROM (ARTSaddresses LEFT JOIN [ARTS-Alumni details Query]
  4. ON [ARTSaddresses].[ARTS_ID]=[ARTS-Alumni details Query].[ARTS_ID]) 
  5.  
Expand|Select|Wrap|Line Numbers
  1. SELECT [ARTSaddresses].[DateUpdated], [ARTSaddresses].[Alumnicheck]
  2.  FROM (ARTSaddresses LEFT JOIN [ARTS-Alumni details Query]
  3.  ON [ARTSaddresses].[ARTS_ID]=[ARTS-Alumni details Query].[ARTS_ID]) 
  4.  

I ran both the above queries and both are un updateable.
Jan 13 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Jan 13 '07 #10

P: 44
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.
Jan 13 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 14 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.