update complex query data in a form

[Mark R]

Looks like this question got skipped over or it is too
tough, possibly to understand !

The "prep" form's underlying Query:

Select Table.hasOnlyOneRecordawithOneFiled,
Table2.hasOnlyOneRecordwithseveralfields, Maintable.field1,
Maintable.field60etc, table4.field1, table4.field2and3,
table5.field1through8

FROM table1, table2, (table5 JOIN maintable on ID=ID)
INNER JOIN (table4 INNER JOIN (table3 INNER JOIN table2
ON table3.fieldX = table2.fieldy) [note join complexity is
aproximated here)

GROUP BY etc


I was hoping that when the data is displayed, I can update
any field from any table by setting dynaset to
Inconsistant Updates. At first it looked like it worked,
but then Iwhen I tried to close the form I got an error
message that maintablefield5 was a required field and
could not be left empty, which of course it was NOT empty.

I could just make the user open up the original entry form
and update Maintable while viewing this "prep" form.

I need guidance............the vagueness on the Join
nesting above exists because I don't fully follow the
nesting logic yet, I used wizard, so if it is critical to
your ability to advise me, bare with me and I 'll try to
provide a better skeleton.
..

 

[Stewart Tanner]

Hi there Mark

this one is difficult to understand from the description. Obviously you had
a query that was not updateable. To try and get around this you have set the
recordsource type of your query to "dynaset(Inconsistent Updates)"

Now because of this you can update data.

One of the key fields, maintablefield5 is not being updated when you try to
save the record.

Is this correct?

I'd look and make sure that the control is actually bound to that table and
field, particularly if it is a foreign key from another table, you may be
updating the field but not in the table you thought you were.

Failing that if no one else helps you email me a zipped copy of the tables,
the query and your form that is failing and I'll try and figure out what is
wrong. I'm on dial up so if you have a heap of data in your system cut it
back to a few records and do a compact on the db before zipping and sending.
Also include instructions as to how to replicate the error.

BTW I'm running access 97 and access XP so if it's a 2003 database I can't
help unless you save it as an earlier version.