Field not updating in Form based on Query.

[Guest]

I have a form based on a query joining 3 tables. It was working fine until i
added another table to the query. Now it won't update the table when the
field on the form is updated. The field was being updated before.

 

[John W. Vinson]

I have a form based on a query joining 3 tables. It was working fine until i
added another table to the query. Now it won't update the table when the
field on the form is updated. The field was being updated before.

A four-table query is very unlikely to allow updating, and - though it makes
sense for a Report - it would be very unusual to base a Form on such a query.
Normally a Form would be based on (a query using) one table, or possibly two,
with Subforms for "child" tables and combo boxes for "lookup" tables.


John W. Vinson [MVP]

 

[Guest]

So is it IMPOSSIBLE to do an update from a form based on a query having 4
tables??? I'm finding it a bit hard to believe...

 

[John W. Vinson]

So is it IMPOSSIBLE to do an update from a form based on a query having 4
tables??? I'm finding it a bit hard to believe...

No, it's not impossible. It depends on the four tables and how they are
related. If you have a main table with three "lookup" tables, and for some
peculiar reason don't want to use combo boxes (to display a human-meaningful
value while storing an ID), you can probably have it updateable.

Are you really averse to using the powerful tools that Access provides - combo
boxes, subforms, etc. - and doing it the hard way yourself (using a monster
query)? If so, why?

John W. Vinson [MVP]

 

[Guest]

Its not as simple as "lookup" values that are stored in those tables. They
are related tables containing more than just lookup values. e.g. Employees,
Timesheets, EmployeeRates based on different time periods etc.
Is there anyone else who can help???

 

[John W. Vinson]

Its not as simple as "lookup" values that are stored in those tables. They
are related tables containing more than just lookup values. e.g. Employees,
Timesheets, EmployeeRates based on different time periods etc.
Is there anyone else who can help???

Well, I suspect others will suggest that you use Combo Boxes and Subforms. If
the answer that works is unacceptable because it's not the Way We Do It Here
then I'm sort of at a loss!

I'll be glad to try to help you do it your way if you wish. Please open the
Query in SQL view and post the SQL text here, and indicate which fields you
want to display and which you want to update; also indicate why subforms and
combo boxes are unacceptable as tools.

John W. Vinson [MVP]

 

[Guest]

I made it work.

So I guess a form based on a 4 table query CAN be used to update data!!!