Multi-Table query data source won't allow edits in Form

[Tom Mackay]

I am trying to update an existing Access db form, Clients, that maintains a
table of the exact same name; I want to add in a new table of information and
allow editing of that information via the Clients form.

The current design puts a huge amount of data on one table, Clients, with a
single key called SortID. Rather than add more fields to this table, I have
created a new table, Retirement, and added the SortID to that table.
However, when I update the datasource query from Clients table to Clients
table-joined-to-Retirement table, no edits via the form are allowed anymore.

Is there any feature of this query...or the form...that will allow the edits
to continue? I understand from looking at some similar posts that I can go
down the subform route...but before I do that, I'd appreciate any advice on
how to proceed otherwise. The group using this database has over the years
always simply added more and more fields to the Clients table only because
they wanted to be able to continue using this form to make all their
edits...and they felt that using sub-forms inhibited their form
design/usability.

Any help is appreciated,
Txs,
Tom

 

[Ken Snell MVP]

Sounds like you've built a nonupdatable query for use as the form's Record
Source. Without seeing the SQL statement of the query, it's not possible to
give specific suggestions. But there is a lot of information on the web
about these types of queries. You must use an updatable query (one that lets
you edit fields' data and add/delete records) if you want to edit/update
data.

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

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



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

 

[Banana]

To add to Ken's excellent advices, it's routine (but not mandatory) to
assign only one table or a query based on only one table to a form. In
cases where we need to handle records from more than one table, we use
subforms.

I can only imagine the cases where we needed one form with updatable
query based on multiple tables would be quite rare.

 

[John W. Vinson]

The current design puts a huge amount of data on one table, Clients,

Then it's quite possible that the design is flawed and at fault for your
problem.

If you have more than thirty fields in a table, look carefully to see if you
have some hidden "one to many" or "many to many" relationships embedded in the
record. Are you storing data in fieldnames? What are some of your typical
fieldnames? Might this not in fact be better split into two or more tables in
one-to-many relationships?

 

[Tom Mackay]

I am going through the info in the links, thank you very much for all of your
ideas. I will post results when I get to them, this is an after-work project
for now so it moves slowly.

One question about sub-forms? When compared to Forms, don;t they have a
much less functional offering, in terms of how you can report informatoin and
arrange it?

 

[John W. Vinson]

One question about sub-forms? When compared to Forms, don;t they have a
much less functional offering, in terms of how you can report informatoin and
arrange it?

Not at all. You can do anything with a Subform that you do with a Form.

Note that Access, by default, creates Subforms in Datasheet view, which is
indeed limited; but you're not restricted to that view, and in fact I rarely
use it. Open the form you're using as a subform in design view and change its
Default View property to Single (if you want to see one record at a time) or
Continuous (the best of both, you can see multiple records but have full
flexibility to use headers, footers, varied types of controls, colors, labels,
etc.)

 

[Tom Mackay]

Thanks for the advice and ideas. I was able to do exactly what the users
were looking to do...I had some trouble creating the sub-forms at first, but
once I got the order of operations worked out, it was very easy.

If you would to help out any more...ideas on how to format data on the form
differently, from record to record, depending on the specific value of the
field? ie highlight a currency value in bold red if it was negative? Thta's
what I am now looking for.

Txs again,
Tom

 

[Tom Mackay]

Sorry...before you waste any time, I easily found the Conditional Formatting
feature and completed my desired action. Thanks again!