Recordset is not Updatable...

[Nad]

Hi,
I have a Project Update Form, and the DataSource of the form is a query.
I put a Tab Control on the form and i put all the fields on the tabcontrol
page.
But, when i try to edit any field the message "Recordset is not updatable"
appearing
in the taskbar.
Please... help to solve this problem.
Regards,

 

[Tom Wickerath]

Hi Nad,

Is your query updateable?

Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html

____________________________________


Hi,
I have a Project Update Form, and the DataSource of the form is a query. I
put a Tab Control on the form and i put all the fields on the tabcontrol
page. But, when i try to edit any field the message "Recordset is not
updatable" appearing in the taskbar.

Please... help to solve this problem.
Regards,

 

[Nad]

Hi Tom,
No, My query is not Updatable.

Thanks for your reply.

 

[Tom Wickerath]

Hi Nad,

Well, then that could very well be the problem. Figure out why your query is
not updateable and you'll probably solve the problem you reported. Take a
look at this listing of possibilities:

Why is my query read-only?
http://allenbrowne.com/ser-61.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html

________________________________________


Hi Tom,
No, My query is not Updatable.

Thanks for your reply.

 

[Nad]

I checked every possibility, but i did not find any thing wrong.
I have a very simple query with no condition/criteria, (nothing).
The qeury type is Select.

 

[Ron2006]

One table? Multiple Tables?

If multiple, Left Join, right join, or matching?

Any summarizations?

Any created fields ?

 

[John W. Vinson]

I checked every possibility, but i did not find any thing wrong.
I have a very simple query with no condition/criteria, (nothing).
The qeury type is Select.

We'll be glad to try to help if you'll show us the query (post the SQL). Is
the table upon which the query is based updateable? Have you Compacted the
database?

John W. Vinson [MVP]

 

[Tom Wickerath]

Hi Nad,

In addition to what Ron2006 and John Vinson have asked, does your query
include either a field set as the primary key, or a uniquely indexed field. A
quick review of Allen's list includes this:

"The fields in a JOIN are not indexed correctly: there is no
primary key or unique index on the JOINed fields."

But you should still include a primary key field even if the query is based
on only one table (ie. no joins involved).

Post the SQL statement please, and wherever there is a FROM
{TableOrQueryName} or a join statement that involves a table or field name,
make sure to tell us whether it is a table or another query.

Are any of the table(s) involved linked tables? Can you add records
directly to the table(s) involved? Make sure to do a Compact and repair, as
John suggested.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Nad]

Hi Tom,Ron2006,
Thanks all of U for ansewring.
I am using two table but no relationship between them. I have also parameter
value in my query.Here is the SQL statement
SELECT UPDATE.[PROJECT ENGINEER], UPDATE.[PROJECT NO], UPDATE.[PROJECT
TITLE], UPDATE.[PLANT/UNIT/DEPT], UPDATE.[SET BACKS], UPDATE.[FORWARD PLAN],
UPDATE.[ORIGINAL SCOPE], UPDATE.[REVISED SCOPE], UPDATE.[CURRENT PHASE],
UPDATE.[POTENTIAL CONCERN], UPDATE.SCHEDULE, UPDATE.[CURRENT STATUS],
[PROJECT DATES].[START DATE], [PROJECT DATES].[COMPLETION DATE],
UPDATE.BUDGET, UPDATE.SCOPE
FROM [UPDATE], [PROJECT DATES]
WHERE (((UPDATE.[PROJECT NO])=[Enter Project Number] And (UPDATE.[PROJECT
NO])=[project dates].[project no]));

Regards,

 

[Tom Wickerath]

Hi Nad,

I am using two table but no relationship between them.

Why? I think you just located the source of your update problem. Besides,
why would you want a cartesian product result? (I can think of a few reasons,
but I'd like you to state your reasons first).

For example, I can do the following query in the sample Northwind database,
which produces a cartesian product result which returns 7470 records in a
"clean" copy of Northwind 2003:

SELECT Employees.EmployeeID, Employees.LastName,
Employees.FirstName, Orders.OrderID, Orders.OrderDate,
Orders.ShipCity, Orders.EmployeeID
FROM Employees, Orders;

While the query includes the primary keys of each table (EmployeeID and
OrderID), and a foreign key as well, Orders.EmployeeID, it is not editable in
the least. Add the join back in, as in this example, and the query now
returns only 830 records. I can now edit records and add new records:

SELECT Employees.EmployeeID, Employees.LastName,
Employees.FirstName, Orders.OrderID, Orders.OrderDate,
Orders.ShipCity, Orders.EmployeeID
FROM Employees
INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID;

Also, I'm not too keen on the use of special characters or reserved words
for anything that you assign a name to in Access. Update is a reserved word
(it's used as a SQL keyword for an update query). As a minimum, it should be
bracketed everywhere it is used, as in:

SELECT [UPDATE].[PROJECT ENGINEER], [UPDATE].[PROJECT NO], etc.

You've also got field names with spaces and special characters, such as "/",
as in [PLANT/UNIT/DEPT]. You'd be much better off with a fieldname of
"PlantUnitDept".

I suggest two things:

1.) Use a proper join between your tables, with a uniquely index field on
one side of the join and

2.) Get rid of any reserved words and special characters. Access MVP Allen
Browne offers a free utility that you can use to scan your database for this
issue:

Database Issue Checker Utility
http://allenbrowne.com/AppIssueChecker.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi Tom,Ron2006,
Thanks all of U for ansewring.
I am using two table but no relationship between them. I have also parameter
value in my query.Here is the SQL statement
SELECT UPDATE.[PROJECT ENGINEER], UPDATE.[PROJECT NO], UPDATE.[PROJECT
TITLE], UPDATE.[PLANT/UNIT/DEPT], UPDATE.[SET BACKS], UPDATE.[FORWARD PLAN],
UPDATE.[ORIGINAL SCOPE], UPDATE.[REVISED SCOPE], UPDATE.[CURRENT PHASE],
UPDATE.[POTENTIAL CONCERN], UPDATE.SCHEDULE, UPDATE.[CURRENT STATUS],
[PROJECT DATES].[START DATE], [PROJECT DATES].[COMPLETION DATE],
UPDATE.BUDGET, UPDATE.SCOPE
FROM [UPDATE], [PROJECT DATES]
WHERE (((UPDATE.[PROJECT NO])=[Enter Project Number] And (UPDATE.[PROJECT
NO])=[project dates].[project no]));

Regards,

Hi Nad,

In addition to what Ron2006 and John Vinson have asked, does your query
include either a field set as the primary key, or a uniquely indexed field. A
quick review of Allen's list includes this:

"The fields in a JOIN are not indexed correctly: there is no
primary key or unique index on the JOINed fields."

But you should still include a primary key field even if the query is based
on only one table (ie. no joins involved).

Post the SQL statement please, and wherever there is a FROM
{TableOrQueryName} or a join statement that involves a table or field name,
make sure to tell us whether it is a table or another query.

Are any of the table(s) involved linked tables? Can you add records
directly to the table(s) involved? Make sure to do a Compact and repair, as
John suggested.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Hi Nad,

I just took another look at your SQL statement, and I see that you are
apparently avoiding a cartesian product result by including this in the WHERE
clause:

And (UPDATE.[PROJECT NO])=[project dates].[project no]));

So, adding something similar to the SQL example that I provided for the
sample Northwind database yields the 830 records one would normally expect:

SELECT Employees.EmployeeID, Employees.LastName,
Employees.FirstName, Orders.OrderID, Orders.OrderDate, Orders.ShipCity
FROM Employees, Orders
WHERE [Employees].[EmployeeID]=[Orders].[EmployeeID]

However, this query is still not updateable.

Why not replace this part of your WHERE clause with a join:

And (UPDATE.[PROJECT NO])=[project dates].[project no]));

Specifically an Inner Join between these two fields. Is [UPDATE].[PROJECT
NO] a primary key or uniquely indexed field?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html

 

[Nad]

Hi Tom,
As per your advise, i made some changes in my query and database and its
working fine. Now i can update my query.
Thanks a lot for your advise,support...
U people are very kind.
Thanks again!

 

[Tom Wickerath]

Hi Nad,

You're very welcome. Good luck on your project.

By the way, did you get rid of the reserved words and special characters?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[John Wyman]

Nad,
I ran into this problem too... had no idea why. After reading some of the
replys to your question I checked all of my queries (in Design View) and
found out I had some Tables attached that shouldn't have been (too much
messing around with design). After I removed these extraneous tables, the
proble "Recordset is not Updatable" went away. Hope this helps you too.

 

[Nurse Nancy]

Hi
I am having a similar problem and after reading the all the posts, i am not
sure what to do

A few days ago the form was updateble and then i started adding some
calculated fields and not sure what else,, i saved the backup that works so
I'll post both.

The other thing that you may be wondering is why is this so f'''d up,

Well what i was trying to do was...
I have a table that is created by a an append query.
I built a query to grab some other fields and a form to view and update the
table

Now,, for the question, since the query that the form is based on has lots
of fields that are the same for each record ie..
Customer
Product
Product Category, etc, etc
I wanted to try to use a form for the common info and a subform for the info
that is different on each record....
But in doing so, i seemed to have to create a query for the form Weekly Buy
Query and then another query for the subform,,, which is what i have pasted
below.

here's the SQL that doesn't work

SELECT [Weekly Buy Query].STATION, [Weekly Buy Query].[Buy Status], [Weekly
Buy Query].Keep, [Weekly Buy Query].MISC, [Weekly Buy Query].[source #],
[Weekly Buy Query].M, [Weekly Buy Query].T, [Weekly Buy Query].W, [Weekly Buy
Query].TH, [Weekly Buy Query].F, [Weekly Buy Query].[Total Calls], [Weekly
Buy Query].CPC, [Weekly Buy Query].[Primary Day Part], [Weekly Buy
Query].[Rotator Day Part], [Weekly Buy Query].[Other Day Part], [Weekly Buy
Query].Comments, [Weekly Buy Query].[Format ID], [Weekly Buy Query].[Station
Rep], [Weekly Buy Query].[Market ID], [Weekly Buy Query].State, [Weekly Buy
Query].RS.[Opt In], [Weekly Buy Query].[Rep ID], [Weekly Buy Query].[Contact
ID], [Weekly Buy Query].[First Name_Group], [Weekly Buy Query].[Last
Name_Group], [Weekly Buy Query].[Full Name_Group], [Weekly Buy Query].[#
Prime Spots], [Weekly Buy Query].[# Rot Spots], [Weekly Buy Query].[# Other
Spots], Sum(([# Prime Spots]*[Prime])+([# Rot Spots]*[ROS])) AS Total,
[Weekly Buy Query].Prime, [Weekly Buy Query].ROS, [MARKETS TABLE].Market,
[MARKETS TABLE].[State ID]
FROM [Weekly Buy Query] INNER JOIN [MARKETS TABLE] ON [Weekly Buy
Query].[Market ID] = [MARKETS TABLE].[Market ID]
GROUP BY [Weekly Buy Query].STATION, [Weekly Buy Query].[Buy Status],
[Weekly Buy Query].Keep, [Weekly Buy Query].MISC, [Weekly Buy Query].[source
#], [Weekly Buy Query].M, [Weekly Buy Query].T, [Weekly Buy Query].W, [Weekly
Buy Query].TH, [Weekly Buy Query].F, [Weekly Buy Query].[Total Calls],
[Weekly Buy Query].CPC, [Weekly Buy Query].[Primary Day Part], [Weekly Buy
Query].[Rotator Day Part], [Weekly Buy Query].[Other Day Part], [Weekly Buy
Query].Comments, [Weekly Buy Query].[Format ID], [Weekly Buy Query].[Station
Rep], [Weekly Buy Query].[Market ID], [Weekly Buy Query].State, [Weekly Buy
Query].RS.[Opt In], [Weekly Buy Query].[Rep ID], [Weekly Buy Query].[Contact
ID], [Weekly Buy Query].[First Name_Group], [Weekly Buy Query].[Last
Name_Group], [Weekly Buy Query].[Full Name_Group], [Weekly Buy Query].[#
Prime Spots], [Weekly Buy Query].[# Rot Spots], [Weekly Buy Query].[# Other
Spots], [Weekly Buy Query].Prime, [Weekly Buy Query].ROS, [MARKETS
TABLE].Market, [MARKETS TABLE].[State ID];



Here's the SQL that works

SELECT [Weekly Buy Query].STATION, [Weekly Buy Query].[Buy Status], [Weekly
Buy Query].Keep, [Weekly Buy Query].MISC, [Weekly Buy Query].[source #],
[Weekly Buy Query].M, [Weekly Buy Query].T, [Weekly Buy Query].W, [Weekly Buy
Query].TH, [Weekly Buy Query].F, [Weekly Buy Query].[Total Calls], [Weekly
Buy Query].CPC, [Weekly Buy Query].[Primary Day Part], [Weekly Buy
Query].[Rotator Day Part], [Weekly Buy Query].[Other Day Part], [Weekly Buy
Query].Comments, [Weekly Buy Query].[Format ID], [Weekly Buy Query].[Station
Rep], [Weekly Buy Query].[Market ID], [Weekly Buy Query].State, [Weekly Buy
Query].RS.[Opt In], [Weekly Buy Query].[Rep ID], [Weekly Buy Query].[Contact
ID], [Weekly Buy Query].[First Name_Group], [Weekly Buy Query].[Last
Name_Group], [Weekly Buy Query].[Full Name_Group], [Weekly Buy Query].[#
Prime Spots], [Weekly Buy Query].[# Rot Spots], [Weekly Buy Query].[# Other
Spots], Sum(([# Prime Spots]*[Prime])+([# Rot Spots]*[ROS])) AS Total,
[Weekly Buy Query].Prime, [Weekly Buy Query].ROS, [MARKETS TABLE].Market,
[MARKETS TABLE].[State ID]
FROM [Weekly Buy Query] INNER JOIN [MARKETS TABLE] ON [Weekly Buy
Query].[Market ID] = [MARKETS TABLE].[Market ID]
GROUP BY [Weekly Buy Query].STATION, [Weekly Buy Query].[Buy Status],
[Weekly Buy Query].Keep, [Weekly Buy Query].MISC, [Weekly Buy Query].[source
#], [Weekly Buy Query].M, [Weekly Buy Query].T, [Weekly Buy Query].W, [Weekly
Buy Query].TH, [Weekly Buy Query].F, [Weekly Buy Query].[Total Calls],
[Weekly Buy Query].CPC, [Weekly Buy Query].[Primary Day Part], [Weekly Buy
Query].[Rotator Day Part], [Weekly Buy Query].[Other Day Part], [Weekly Buy
Query].Comments, [Weekly Buy Query].[Format ID], [Weekly Buy Query].[Station
Rep], [Weekly Buy Query].[Market ID], [Weekly Buy Query].State, [Weekly Buy
Query].RS.[Opt In], [Weekly Buy Query].[Rep ID], [Weekly Buy Query].[Contact
ID], [Weekly Buy Query].[First Name_Group], [Weekly Buy Query].[Last
Name_Group], [Weekly Buy Query].[Full Name_Group], [Weekly Buy Query].[#
Prime Spots], [Weekly Buy Query].[# Rot Spots], [Weekly Buy Query].[# Other
Spots], [Weekly Buy Query].Prime, [Weekly Buy Query].ROS, [MARKETS
TABLE].Market, [MARKETS TABLE].[State ID];


HELPPPPPPPPPPPPPPPPPPPPPP MEEEEEEEEEEEEEEEEE PLEASEEEEEEE!!

 

[Nurse Nancy]

Hi
I needed to get this fixed asap, and Didn't want anyone to waste time on this.
I ended up going back to my saved db and started by adding 1 calc field at
a time and then checking to see if it was still updatable.
I think the issue happened when I tried to put totals and group bys....
which i need to do at some point, but not yet.

So no need to trouble shoot this, unless you are feeling frisky and still
want to.

Now my issue is,, in the form, I only want some of the fields to be editable
by users

how can i do this?
If I can't, then I wanted to put in formatting into the columns/fields that
should be available for editing,, but this doen't work since the form is
displayed in datasheet view... is there another way?

thanks
nn


--
Nancy

Hi
I am having a similar problem and after reading the all the posts, i am not
sure what to do

A few days ago the form was updateble and then i started adding some
calculated fields and not sure what else,, i saved the backup that works so
I'll post both.

The other thing that you may be wondering is why is this so f'''d up,

Well what i was trying to do was...
I have a table that is created by a an append query.
I built a query to grab some other fields and a form to view and update the
table

Now,, for the question, since the query that the form is based on has lots
of fields that are the same for each record ie..
Customer
Product
Product Category, etc, etc
I wanted to try to use a form for the common info and a subform for the info
that is different on each record....
But in doing so, i seemed to have to create a query for the form Weekly Buy
Query and then another query for the subform,,, which is what i have pasted
below.

here's the SQL that doesn't work

SELECT [Weekly Buy Query].STATION, [Weekly Buy Query].[Buy Status], [Weekly
Buy Query].Keep, [Weekly Buy Query].MISC, [Weekly Buy Query].[source #],
[Weekly Buy Query].M, [Weekly Buy Query].T, [Weekly Buy Query].W, [Weekly Buy
Query].TH, [Weekly Buy Query].F, [Weekly Buy Query].[Total Calls], [Weekly
Buy Query].CPC, [Weekly Buy Query].[Primary Day Part], [Weekly Buy
Query].[Rotator Day Part], [Weekly Buy Query].[Other Day Part], [Weekly Buy
Query].Comments, [Weekly Buy Query].[Format ID], [Weekly Buy Query].[Station
Rep], [Weekly Buy Query].[Market ID], [Weekly Buy Query].State, [Weekly Buy
Query].RS.[Opt In], [Weekly Buy Query].[Rep ID], [Weekly Buy Query].[Contact
ID], [Weekly Buy Query].[First Name_Group], [Weekly Buy Query].[Last
Name_Group], [Weekly Buy Query].[Full Name_Group], [Weekly Buy Query].[#
Prime Spots], [Weekly Buy Query].[# Rot Spots], [Weekly Buy Query].[# Other
Spots], Sum(([# Prime Spots]*[Prime])+([# Rot Spots]*[ROS])) AS Total,
[Weekly Buy Query].Prime, [Weekly Buy Query].ROS, [MARKETS TABLE].Market,
[MARKETS TABLE].[State ID]
FROM [Weekly Buy Query] INNER JOIN [MARKETS TABLE] ON [Weekly Buy
Query].[Market ID] = [MARKETS TABLE].[Market ID]
GROUP BY [Weekly Buy Query].STATION, [Weekly Buy Query].[Buy Status],
[Weekly Buy Query].Keep, [Weekly Buy Query].MISC, [Weekly Buy Query].[source
#], [Weekly Buy Query].M, [Weekly Buy Query].T, [Weekly Buy Query].W, [Weekly
Buy Query].TH, [Weekly Buy Query].F, [Weekly Buy Query].[Total Calls],
[Weekly Buy Query].CPC, [Weekly Buy Query].[Primary Day Part], [Weekly Buy
Query].[Rotator Day Part], [Weekly Buy Query].[Other Day Part], [Weekly Buy
Query].Comments, [Weekly Buy Query].[Format ID], [Weekly Buy Query].[Station
Rep], [Weekly Buy Query].[Market ID], [Weekly Buy Query].State, [Weekly Buy
Query].RS.[Opt In], [Weekly Buy Query].[Rep ID], [Weekly Buy Query].[Contact
ID], [Weekly Buy Query].[First Name_Group], [Weekly Buy Query].[Last
Name_Group], [Weekly Buy Query].[Full Name_Group], [Weekly Buy Query].[#
Prime Spots], [Weekly Buy Query].[# Rot Spots], [Weekly Buy Query].[# Other
Spots], [Weekly Buy Query].Prime, [Weekly Buy Query].ROS, [MARKETS
TABLE].Market, [MARKETS TABLE].[State ID];



Here's the SQL that works

SELECT [Weekly Buy Query].STATION, [Weekly Buy Query].[Buy Status], [Weekly
Buy Query].Keep, [Weekly Buy Query].MISC, [Weekly Buy Query].[source #],
[Weekly Buy Query].M, [Weekly Buy Query].T, [Weekly Buy Query].W, [Weekly Buy
Query].TH, [Weekly Buy Query].F, [Weekly Buy Query].[Total Calls], [Weekly
Buy Query].CPC, [Weekly Buy Query].[Primary Day Part], [Weekly Buy
Query].[Rotator Day Part], [Weekly Buy Query].[Other Day Part], [Weekly Buy
Query].Comments, [Weekly Buy Query].[Format ID], [Weekly Buy Query].[Station
Rep], [Weekly Buy Query].[Market ID], [Weekly Buy Query].State, [Weekly Buy
Query].RS.[Opt In], [Weekly Buy Query].[Rep ID], [Weekly Buy Query].[Contact
ID], [Weekly Buy Query].[First Name_Group], [Weekly Buy Query].[Last
Name_Group], [Weekly Buy Query].[Full Name_Group], [Weekly Buy Query].[#
Prime Spots], [Weekly Buy Query].[# Rot Spots], [Weekly Buy Query].[# Other
Spots], Sum(([# Prime Spots]*[Prime])+([# Rot Spots]*[ROS])) AS Total,
[Weekly Buy Query].Prime, [Weekly Buy Query].ROS, [MARKETS TABLE].Market,
[MARKETS TABLE].[State ID]
FROM [Weekly Buy Query] INNER JOIN [MARKETS TABLE] ON [Weekly Buy
Query].[Market ID] = [MARKETS TABLE].[Market ID]
GROUP BY [Weekly Buy Query].STATION, [Weekly Buy Query].[Buy Status],
[Weekly Buy Query].Keep, [Weekly Buy Query].MISC, [Weekly Buy Query].[source
#], [Weekly Buy Query].M, [Weekly Buy Query].T, [Weekly Buy Query].W, [Weekly
Buy Query].TH, [Weekly Buy Query].F, [Weekly Buy Query].[Total Calls],
[Weekly Buy Query].CPC, [Weekly Buy Query].[Primary Day Part], [Weekly Buy
Query].[Rotator Day Part], [Weekly Buy Query].[Other Day Part], [Weekly Buy
Query].Comments, [Weekly Buy Query].[Format ID], [Weekly Buy Query].[Station
Rep], [Weekly Buy Query].[Market ID], [Weekly Buy Query].State, [Weekly Buy
Query].RS.[Opt In], [Weekly Buy Query].[Rep ID], [Weekly Buy Query].[Contact
ID], [Weekly Buy Query].[First Name_Group], [Weekly Buy Query].[Last
Name_Group], [Weekly Buy Query].[Full Name_Group], [Weekly Buy Query].[#
Prime Spots], [Weekly Buy Query].[# Rot Spots], [Weekly Buy Query].[# Other
Spots], [Weekly Buy Query].Prime, [Weekly Buy Query].ROS, [MARKETS
TABLE].Market, [MARKETS TABLE].[State ID];


HELPPPPPPPPPPPPPPPPPPPPPP MEEEEEEEEEEEEEEEEE PLEASEEEEEEE!!

--
Nancy

Hi,
I have a Project Update Form, and the DataSource of the form is a query.
I put a Tab Control on the form and i put all the fields on the tabcontrol
page.
But, when i try to edit any field the message "Recordset is not updatable"
appearing
in the taskbar.
Please... help to solve this problem.
Regards,

 

[Nurse Nancy]

scratch that,, i am still in need of help
As soon as I added the Calculation the form becomes recordset not updateable

Here's the SQL now
SELECT WBQ.STATION, WBQ.[Buy Status], WBQ.Keep, WBQ.MISC, WBQ.[source #],
WBQ.M, WBQ.T, WBQ.W, WBQ.TH, WBQ.F, WBQ.[Total Calls], WBQ.CPC, WBQ.[Primary
Day Part], WBQ.[Rotator Day Part], WBQ.[Other Day Part], WBQ.Comments,
WBQ.[Format ID], WBQ.[Station Rep], WBQ.[Market ID], WBQ.RS.[Opt In],
WBQ.[Rep ID], WBQ.[Contact ID], WBQ.[First Name_Group], WBQ.[Last
Name_Group], WBQ.[Full Name_Group], WBQ.ROS, M.[State ID], WBQ.Prime, WBQ.[#
Prime Spots], WBQ.[Prime Spots Posted], WBQ.[# Rot Spots], WBQ.[Rot Spots
Posted], WBQ.[# Other Spots], WBQ.[Other Spots Posted], WBQ.[Total Calls],
[Prime]*[# Prime Spots] AS Total
FROM [Weekly Buy Query] AS WBQ INNER JOIN [MARKETS TABLE] AS M ON
WBQ.[Market ID] = M.[Market ID];



If i delete this: [Prime]*[# Prime Spots] AS Total

Then I can update the form

please help me

--
Nancy

Hi
I am having a similar problem and after reading the all the posts, i am not
sure what to do

A few days ago the form was updateble and then i started adding some
calculated fields and not sure what else,, i saved the backup that works so
I'll post both.

The other thing that you may be wondering is why is this so f'''d up,

Well what i was trying to do was...
I have a table that is created by a an append query.
I built a query to grab some other fields and a form to view and update the
table

Now,, for the question, since the query that the form is based on has lots
of fields that are the same for each record ie..
Customer
Product
Product Category, etc, etc
I wanted to try to use a form for the common info and a subform for the info
that is different on each record....
But in doing so, i seemed to have to create a query for the form Weekly Buy
Query and then another query for the subform,,, which is what i have pasted
below.

here's the SQL that doesn't work

SELECT [Weekly Buy Query].STATION, [Weekly Buy Query].[Buy Status], [Weekly
Buy Query].Keep, [Weekly Buy Query].MISC, [Weekly Buy Query].[source #],
[Weekly Buy Query].M, [Weekly Buy Query].T, [Weekly Buy Query].W, [Weekly Buy
Query].TH, [Weekly Buy Query].F, [Weekly Buy Query].[Total Calls], [Weekly
Buy Query].CPC, [Weekly Buy Query].[Primary Day Part], [Weekly Buy
Query].[Rotator Day Part], [Weekly Buy Query].[Other Day Part], [Weekly Buy
Query].Comments, [Weekly Buy Query].[Format ID], [Weekly Buy Query].[Station
Rep], [Weekly Buy Query].[Market ID], [Weekly Buy Query].State, [Weekly Buy
Query].RS.[Opt In], [Weekly Buy Query].[Rep ID], [Weekly Buy Query].[Contact
ID], [Weekly Buy Query].[First Name_Group], [Weekly Buy Query].[Last
Name_Group], [Weekly Buy Query].[Full Name_Group], [Weekly Buy Query].[#
Prime Spots], [Weekly Buy Query].[# Rot Spots], [Weekly Buy Query].[# Other
Spots], Sum(([# Prime Spots]*[Prime])+([# Rot Spots]*[ROS])) AS Total,
[Weekly Buy Query].Prime, [Weekly Buy Query].ROS, [MARKETS TABLE].Market,
[MARKETS TABLE].[State ID]
FROM [Weekly Buy Query] INNER JOIN [MARKETS TABLE] ON [Weekly Buy
Query].[Market ID] = [MARKETS TABLE].[Market ID]
GROUP BY [Weekly Buy Query].STATION, [Weekly Buy Query].[Buy Status],
[Weekly Buy Query].Keep, [Weekly Buy Query].MISC, [Weekly Buy Query].[source
#], [Weekly Buy Query].M, [Weekly Buy Query].T, [Weekly Buy Query].W, [Weekly
Buy Query].TH, [Weekly Buy Query].F, [Weekly Buy Query].[Total Calls],
[Weekly Buy Query].CPC, [Weekly Buy Query].[Primary Day Part], [Weekly Buy
Query].[Rotator Day Part], [Weekly Buy Query].[Other Day Part], [Weekly Buy
Query].Comments, [Weekly Buy Query].[Format ID], [Weekly Buy Query].[Station
Rep], [Weekly Buy Query].[Market ID], [Weekly Buy Query].State, [Weekly Buy
Query].RS.[Opt In], [Weekly Buy Query].[Rep ID], [Weekly Buy Query].[Contact
ID], [Weekly Buy Query].[First Name_Group], [Weekly Buy Query].[Last
Name_Group], [Weekly Buy Query].[Full Name_Group], [Weekly Buy Query].[#
Prime Spots], [Weekly Buy Query].[# Rot Spots], [Weekly Buy Query].[# Other
Spots], [Weekly Buy Query].Prime, [Weekly Buy Query].ROS, [MARKETS
TABLE].Market, [MARKETS TABLE].[State ID];



Here's the SQL that works

SELECT [Weekly Buy Query].STATION, [Weekly Buy Query].[Buy Status], [Weekly
Buy Query].Keep, [Weekly Buy Query].MISC, [Weekly Buy Query].[source #],
[Weekly Buy Query].M, [Weekly Buy Query].T, [Weekly Buy Query].W, [Weekly Buy
Query].TH, [Weekly Buy Query].F, [Weekly Buy Query].[Total Calls], [Weekly
Buy Query].CPC, [Weekly Buy Query].[Primary Day Part], [Weekly Buy
Query].[Rotator Day Part], [Weekly Buy Query].[Other Day Part], [Weekly Buy
Query].Comments, [Weekly Buy Query].[Format ID], [Weekly Buy Query].[Station
Rep], [Weekly Buy Query].[Market ID], [Weekly Buy Query].State, [Weekly Buy
Query].RS.[Opt In], [Weekly Buy Query].[Rep ID], [Weekly Buy Query].[Contact
ID], [Weekly Buy Query].[First Name_Group], [Weekly Buy Query].[Last
Name_Group], [Weekly Buy Query].[Full Name_Group], [Weekly Buy Query].[#
Prime Spots], [Weekly Buy Query].[# Rot Spots], [Weekly Buy Query].[# Other
Spots], Sum(([# Prime Spots]*[Prime])+([# Rot Spots]*[ROS])) AS Total,
[Weekly Buy Query].Prime, [Weekly Buy Query].ROS, [MARKETS TABLE].Market,
[MARKETS TABLE].[State ID]
FROM [Weekly Buy Query] INNER JOIN [MARKETS TABLE] ON [Weekly Buy
Query].[Market ID] = [MARKETS TABLE].[Market ID]
GROUP BY [Weekly Buy Query].STATION, [Weekly Buy Query].[Buy Status],
[Weekly Buy Query].Keep, [Weekly Buy Query].MISC, [Weekly Buy Query].[source
#], [Weekly Buy Query].M, [Weekly Buy Query].T, [Weekly Buy Query].W, [Weekly
Buy Query].TH, [Weekly Buy Query].F, [Weekly Buy Query].[Total Calls],
[Weekly Buy Query].CPC, [Weekly Buy Query].[Primary Day Part], [Weekly Buy
Query].[Rotator Day Part], [Weekly Buy Query].[Other Day Part], [Weekly Buy
Query].Comments, [Weekly Buy Query].[Format ID], [Weekly Buy Query].[Station
Rep], [Weekly Buy Query].[Market ID], [Weekly Buy Query].State, [Weekly Buy
Query].RS.[Opt In], [Weekly Buy Query].[Rep ID], [Weekly Buy Query].[Contact
ID], [Weekly Buy Query].[First Name_Group], [Weekly Buy Query].[Last
Name_Group], [Weekly Buy Query].[Full Name_Group], [Weekly Buy Query].[#
Prime Spots], [Weekly Buy Query].[# Rot Spots], [Weekly Buy Query].[# Other
Spots], [Weekly Buy Query].Prime, [Weekly Buy Query].ROS, [MARKETS
TABLE].Market, [MARKETS TABLE].[State ID];


HELPPPPPPPPPPPPPPPPPPPPPP MEEEEEEEEEEEEEEEEE PLEASEEEEEEE!!

--
Nancy

Hi,
I have a Project Update Form, and the DataSource of the form is a query.
I put a Tab Control on the form and i put all the fields on the tabcontrol
page.
But, when i try to edit any field the message "Recordset is not updatable"
appearing
in the taskbar.
Please... help to solve this problem.
Regards,

 

[Tom Wickerath]

Hi Nurse Nancy,

If i delete this: [Prime]*[# Prime Spots] AS Total
Then I can update the form

Then do the calculation in the form instead of the query. You're already
selecting the two fields, [Prime] and [# Prime Spots] in the SELECT
statement, so these fields are available to the form. Just set the Control
Source for a text box as follows:

=[Prime]*[# Prime Spots]

Make sure to give this text box a different name from either of the fields
involved. So, perhaps name it: txtTotal

Suggestion:
Get rid of any special characters (including spaces, # signs, etc.) and
reserved words in things that you assign a name to within Access, such as
fields, tables, queries, forms, reports, macros, modules, controls on forms &
reports, and variables in VBA code. Access MVP Allen Browne offers a free
utility that you can use to scan your tables & queries for this issue:

Database Issue Checker Utility
http://allenbrowne.com/AppIssueChecker.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

scratch that, i am still in need of help
As soon as I added the Calculation the form becomes recordset not updateable

Here's the SQL now
SELECT WBQ.STATION, WBQ.[Buy Status], WBQ.Keep, WBQ.MISC,
WBQ.[source #], WBQ.M, WBQ.T, WBQ.W, WBQ.TH, WBQ.F,
WBQ.[Total Calls], WBQ.CPC, WBQ.[Primary Day Part],
WBQ.[Rotator Day Part], WBQ.[Other Day Part], WBQ.Comments,
WBQ.[Format ID], WBQ.[Station Rep], WBQ.[Market ID], WBQ.RS.[Opt In],
WBQ.[Rep ID], WBQ.[Contact ID], WBQ.[First Name_Group], WBQ.[Last
Name_Group], WBQ.[Full Name_Group], WBQ.ROS, M.[State ID],
WBQ.Prime, WBQ.[# Prime Spots], WBQ.[Prime Spots Posted],
WBQ.[# Rot Spots], WBQ.[Rot Spots Posted], WBQ.[# Other Spots],
WBQ.[Other Spots Posted], WBQ.[Total Calls], [Prime]*[# Prime Spots] AS Total
FROM [Weekly Buy Query] AS WBQ INNER JOIN [MARKETS TABLE] AS M
ON WBQ.[Market ID] = M.[Market ID];



If i delete this: [Prime]*[# Prime Spots] AS Total

Then I can update the form

please help me

 

[Tom Wickerath]

Hi Nurse Nancy,

I think the issue happened when I tried to put totals and group bys....

Yep. "That'll do-it". Any query with the Totals enabled will be read only.
See the first bullet in this listing:

Why is my query read-only?
http://allenbrowne.com/ser-61.html

Now my issue is, in the form, I only want some of the fields to be editable
by users

Set the Enabled property for these controls to No to prevent editing the data.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Nurse Nancy]

THANKS SO MUCH
That worked!!!
--
Nancy

Hi Nurse Nancy,


Yep. "That'll do-it". Any query with the Totals enabled will be read only.
See the first bullet in this listing:

Why is my query read-only?
http://allenbrowne.com/ser-61.html


Set the Enabled property for these controls to No to prevent editing the data.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________