"locked" query

[Don]

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.

 

[Ken Snell \(MVP\)]

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/d...l/acconDeterminingWhenCanUpdateDataQueryS.asp
http://msdn2.microsoft.com/en-us/library/aa198446(office.10).aspx

Harnessing the Power of Updatable Queries
http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_harness.asp

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;209571&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default.aspx?scid=kb;en-us;328828&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Product=acc


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

 

[Don]

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!

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/d...l/acconDeterminingWhenCanUpdateDataQueryS.asp
http://msdn2.microsoft.com/en-us/library/aa198446(office.10).aspx

Harnessing the Power of Updatable Queries
http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_harness.asp

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;209571&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default.aspx?scid=kb;en-us;328828&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Product=acc


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

--

Ken Snell
<MS ACCESS MVP>

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.

 

[Ken Snell \(MVP\)]

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>

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!

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/d...l/acconDeterminingWhenCanUpdateDataQueryS.asp
http://msdn2.microsoft.com/en-us/library/aa198446(office.10).aspx

Harnessing the Power of Updatable Queries
http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_harness.asp

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;209571&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You
Try
to Update a Linked Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in
Queries
and in Forms
http://support.microsoft.com/default.aspx?scid=kb;en-us;328828&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Product=acc


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

--

Ken Snell
<MS ACCESS MVP>

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.