A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"locked" query



 
 
Thread Tools Display Modes
  #1  
Old May 30th, 2008, 08:22 PM posted to microsoft.public.access.queries
Don
external usenet poster
 
Posts: 992
Default "locked" query

Hoping someone can help this novice!

I have created a query that is based upon two separate tables. In one table
"tblReviewTasks" I have a field [strReviewID]. In another table "tblInv" I am
trying to get the related field [strNPI]. Both tables share [strReviewID] and
these are tied with a relationship and a join property. Both are text fields.
My problem is that whenever I add the second table to my query, the query
becomes "locked" and I cannot enter any data.

1) how can you make queries with joined tables (or queries) so that you can
add data? For some queries I have no problem, but with others I get locked up
and I cannot see any difference between them.

2) Can this be better accomplished using a Dlookup? I am using multiple
fields from "tblReviewTasks" but I only need one field from "tblInv". If this
can be done via a Dlookup in the query, how do I write this for the text
field?

Please address both options so that I have a better understanding.
--
Thanks!
  #2  
Old June 2nd, 2008, 12:59 AM posted to microsoft.public.access.queries
Ken Snell \(MVP\)
external usenet poster
 
Posts: 2,506
Default "locked" query

An updatable query allows you to edit existing data or to add new records or
to delete existing records when the query is open in datasheet view. A
nonupdatable query will not let you do any of these things, and if you build
a form based on such a query the form also will not let you do such things.

See these articles for information about what can make a query nonupdatable
(watch for line-wrapping):

When can I update data from a query?
http://msdn.microsoft.com/library/de...DataQueryS.asp
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

Harnessing the Power of Updatable Queries
http://msdn.microsoft.com/library/de...dn_harness.asp

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default...71&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default...79&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default...28&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default...42&Product=acc


Additionally, Allen Browne (MVP) has information about updatable and
nonupdatable queries:
http://allenbrowne.com/ser-61.html

--

Ken Snell
MS ACCESS MVP

"Don" wrote in message
...
Hoping someone can help this novice!

I have created a query that is based upon two separate tables. In one
table
"tblReviewTasks" I have a field [strReviewID]. In another table "tblInv" I
am
trying to get the related field [strNPI]. Both tables share [strReviewID]
and
these are tied with a relationship and a join property. Both are text
fields.
My problem is that whenever I add the second table to my query, the query
becomes "locked" and I cannot enter any data.

1) how can you make queries with joined tables (or queries) so that you
can
add data? For some queries I have no problem, but with others I get locked
up
and I cannot see any difference between them.

2) Can this be better accomplished using a Dlookup? I am using multiple
fields from "tblReviewTasks" but I only need one field from "tblInv". If
this
can be done via a Dlookup in the query, how do I write this for the text
field?

Please address both options so that I have a better understanding.
--
Thanks!



  #3  
Old June 2nd, 2008, 06:38 PM posted to microsoft.public.access.queries
Don
external usenet poster
 
Posts: 992
Default "locked" query

Ken, to the best of my understanding I have checked all of these items and I
cannot identify what is making my query non updatable. Perhaps I should try a
Dlookup instead. Can you please address the Dlookup question in my thread as
an alternate solution?
--
Thanks!


"Ken Snell (MVP)" wrote:

An updatable query allows you to edit existing data or to add new records or
to delete existing records when the query is open in datasheet view. A
nonupdatable query will not let you do any of these things, and if you build
a form based on such a query the form also will not let you do such things.

See these articles for information about what can make a query nonupdatable
(watch for line-wrapping):

When can I update data from a query?
http://msdn.microsoft.com/library/de...DataQueryS.asp
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

Harnessing the Power of Updatable Queries
http://msdn.microsoft.com/library/de...dn_harness.asp

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default...71&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default...79&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default...28&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default...42&Product=acc


Additionally, Allen Browne (MVP) has information about updatable and
nonupdatable queries:
http://allenbrowne.com/ser-61.html

--

Ken Snell
MS ACCESS MVP

"Don" wrote in message
...
Hoping someone can help this novice!

I have created a query that is based upon two separate tables. In one
table
"tblReviewTasks" I have a field [strReviewID]. In another table "tblInv" I
am
trying to get the related field [strNPI]. Both tables share [strReviewID]
and
these are tied with a relationship and a join property. Both are text
fields.
My problem is that whenever I add the second table to my query, the query
becomes "locked" and I cannot enter any data.

1) how can you make queries with joined tables (or queries) so that you
can
add data? For some queries I have no problem, but with others I get locked
up
and I cannot see any difference between them.

2) Can this be better accomplished using a Dlookup? I am using multiple
fields from "tblReviewTasks" but I only need one field from "tblInv". If
this
can be done via a Dlookup in the query, how do I write this for the text
field?

Please address both options so that I have a better understanding.
--
Thanks!




  #4  
Old June 4th, 2008, 03:18 AM posted to microsoft.public.access.queries
Ken Snell \(MVP\)
external usenet poster
 
Posts: 2,506
Default "locked" query

Show us the SQL statement of the query that is not updatable. That will
assist us in identifying the better solution.

--

Ken Snell
MS ACCESS MVP


"Don" wrote in message
...
Ken, to the best of my understanding I have checked all of these items and
I
cannot identify what is making my query non updatable. Perhaps I should
try a
Dlookup instead. Can you please address the Dlookup question in my thread
as
an alternate solution?
--
Thanks!


"Ken Snell (MVP)" wrote:

An updatable query allows you to edit existing data or to add new records
or
to delete existing records when the query is open in datasheet view. A
nonupdatable query will not let you do any of these things, and if you
build
a form based on such a query the form also will not let you do such
things.

See these articles for information about what can make a query
nonupdatable
(watch for line-wrapping):

When can I update data from a query?
http://msdn.microsoft.com/library/de...DataQueryS.asp
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

Harnessing the Power of Updatable Queries
http://msdn.microsoft.com/library/de...dn_harness.asp

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default...71&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You
Try
to Update a Linked Table
http://support.microsoft.com/default...79&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in
Queries
and in Forms
http://support.microsoft.com/default...28&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default...42&Product=acc


Additionally, Allen Browne (MVP) has information about updatable and
nonupdatable queries:
http://allenbrowne.com/ser-61.html

--

Ken Snell
MS ACCESS MVP

"Don" wrote in message
...
Hoping someone can help this novice!

I have created a query that is based upon two separate tables. In one
table
"tblReviewTasks" I have a field [strReviewID]. In another table
"tblInv" I
am
trying to get the related field [strNPI]. Both tables share
[strReviewID]
and
these are tied with a relationship and a join property. Both are text
fields.
My problem is that whenever I add the second table to my query, the
query
becomes "locked" and I cannot enter any data.

1) how can you make queries with joined tables (or queries) so that you
can
add data? For some queries I have no problem, but with others I get
locked
up
and I cannot see any difference between them.

2) Can this be better accomplished using a Dlookup? I am using multiple
fields from "tblReviewTasks" but I only need one field from "tblInv".
If
this
can be done via a Dlookup in the query, how do I write this for the
text
field?

Please address both options so that I have a better understanding.
--
Thanks!






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.