hello
How can I update more than one tables on the same form? The
relationship is one to many from a master table to 3 other related
tables. on the form, I have to setvalue for a few fields(colums) in
the related tables based on the value I enter in the field from the
master table. I kept getting "you have to save the record before you
update..." kind of message. Do you have any idea how to handle this? I
feel I have to set the master table first before setup the related
tables. I did many attempt but somehow couldn't get it right. Any help
will be highly appreciated.

Donna

Hi Donna

The simplest approach is to use a form with subforms. The main form is bound
to your master table. The 3 related tables go in 3 subforms. If there's not
enough room, you can put the 3 subforms on different pages of a Tab control.

If you do need to base the form on a query containing multiple tables, it's
not too difficult to get something that is read-only. In general, the
multi-table query is useful where you are adding/editing records on the
"many" side of the relationship. If you are doing that, there is a cryptic
message something like the one you mentioned, caused by default values in
the fields in the 'one' side of the relationship.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"newbie" <technewbie88 (AT) yahoo (DOT) com> wrote

Hi Allen
Thanks for the reply.
However, I have a few questions here.
In the first approach you mentioned here, using subforms, besides the
key column they are connected to each other, I would like to have
other fields having bound to the master table since the master table
shares 2 or 3 fields with any one of other tables. I prefer not to let
the person who enter data to the master table enter mutlitple times to
other tables for the same data. Is it possible to bind fields accross
main forms and subforms?

By the way, I aslo wonder when the form submit the data to update, if
there is one to many referencial enforcement relationship, how the
databse update works? Does it update the "one side" first, then the
related tables in a one "save record" submition? Or it's necessary to
save the "one side" table first in a "save record" submit, then submit
data in another action to update the "many side" tables?

Thanks for the answer again and I appreciate greatly. I'll try to
check and see if there is a default value causing the problems.

Regards

Donna



"Allen Browne" <AllenBrowne (AT) SeeSig (DOT) Invalid> wrote

When you put a subform control on a form, it has properties called
MasterLinkFields and ChildLinkFields. In MasterLinkFields, enter the 3 field
names from the main form that match the subform fields, separated by
semicolons. Then enter the matching field names from the subform into the
ChildLinkFields. The subform will only show the records that match on the 3
fields, and when you enter a new field in the subform it will inherit the
values from the fields in the main form.

Where you have a relationship with R.I enforced, the data must exist in the
"one" side before you can create the entry in the "many" side. With the
form/subform, that means you have to enter the main form record first,
before entering the matching values in the subform.

Access does permit nulls in the foreign key fields, so mark them as Required
in your related table if you want to block this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"newbie" <technewbie88 (AT) yahoo (DOT) com> wrote

Hi, Allen
Thanks for your reply. It works. However, after I deleted some
inconsistant records in a subtable, the primary key field of an
autonumber type is not active in the subform any more. In another
word, I can not add in any new record to the subtable through the
master interface which is to copy some linked data fields to the
subform. What happened? The only change I made is I deleted some
records in the subtable which has R.I relationship to the master
table. Any idea how I can fix the problem? I still can add record to
the master table though through the same master interface.
Any tips will be highly appreciated.

Regards

Donna


"Allen Browne" <AllenBrowne (AT) SeeSig (DOT) Invalid> wrote

Sounds like you have set the AllowEdits property of the subform to No.

Either that or changed the Recordset to a non-updatable query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"newbie" <technewbie88 (AT) yahoo (DOT) com> wrote

Allen,
You are exactly right. I fixed the problem by reset the property.
By the way, when I fill out a field on a form which I setup as a combo
box, I run a select query to get the data based on the value in
another textbox on the same form. But it seems it always remember the
value from last record and I have to refresh the record(under menu
records) to get the current value. How can I fix it? Is there any
setting I can set to make it auto refresh?

Thank you very much and have a wonderful Thanksgiving

Donna

"Allen Browne" <AllenBrowne (AT) SeeSig (DOT) Invalid> wrote

That could be the subject fora new thread, perhaps?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"newbie" <technewbie88 (AT) yahoo (DOT) com> wrote

Allen,
it is a good idea It deserve a new thread.
However, I've resolved the problem by runing a requery macro. Thank
you for the idea.

Regards

Donna
"Allen Browne" <AllenBrowne (AT) SeeSig (DOT) Invalid> wrote