Update Field By Code

[Parker]

I have a field that VB says can't be updated. I have 5 tables in the query.
If I use the tables as the base for the query VB updates the field. If I use
queries based on the tables as the base for the query VB says the field
cannot be updated. Each primary key is used in the next query. I'm using
Access 2002 sp3.Thanks for any help.

 

[Ken Snell]

General information about updatable / nonupdatable queries:

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

 

[Parker]

Ken, Thanks for the resources.

General information about updatable / nonupdatable queries:

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

 

[David-W-Fenton]

Ken, Thanks for the resources.

I don't know if any of the links mention it, but there's also the
Access-specific DISTINCTROW predicate, which can sometimes make a
query editable when it's otherwise not.