Ability to update two fields in a form based upon a multiple table query

[Randy]

I have a simple application. We are converting to a new
chart of account. Our parent company has made some
recomendations on the proposed new account number. It is
called Project One Chart of Account (POCA)

The application has 4 tables

tbl1 current chart of account
acct
acctdesc
POCAcomments

tbl2 crossreference
acct
pocaacct
comments
responsibleperson

tbl3 pocaacctlist
pocaacct
pocaacctdesc

tbl4
acct
acctbalance


I have a query it brings in all four tables. I have a form
based upon this query. this form shows the following

frm tbl 1 pocacomments
frm tbl 2 acct, pocaacct, comments
frm tbl 3 pocaacctdesc
fm tbl 4 acctbalance

This has been great and worked well. But now we want to
update the comments and pocaacct fields in tbl2 as we hold
meetings and discuss options. When we try to update either
field we get the message This Recordset is not updateable.

In the query tb4 is set as the main table with the join to
the other three as all values from tbl4 and only those
matcing etc. etc.

I really need to make this form updateable. Is my form
incorrect, or is it in my query? I am assuming the
pocaacct field is an issue, as access does not know which
table to update tbl2 (the one I want to update) or tbl3
(the one I do not want changed). But the comments field is
only in one table (tbl2) So on this form, I want all the
values to show, but I only want to actually update two
fields from tbl2.

Any suggestions on what I am missing?

 

[Gary Miller]

Randy,

Probably it is the query being a non-updatable query that is
the problem. To test, just open the query in datasheet view
and see if you can make changes to the fields in question
there. If not, you have found the problem.

Gary Miller

 

[Guest]

excellent suggestion. I did that and yes I got the same
error. So how do I make the query updateable?

 

[Marshall Smith]

The best way to do this is to set relationships between the tables, and
enforce referential integrity. That way, Access knows exactly what to do if
you change one of the primary key fields. (The reason Access is making this
query non-updateable is that it doesn't trust you to only change the
comments fields.)

Another possibility is to break your form down into subforms. You can base
each subform on one of your tables. This will allow you to make sure that
everything is updateable. You may have to do some fancy footwork on the
Master/Child fields, though.

HTH,

Marshall Smith
Project Developers, Inc.