By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,385 Members | 847 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,385 IT Pros & Developers. It's quick & easy.

help wanted

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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" <te**********@yahoo.com> wrote in message
news:5a*************************@posting.google.co m...
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

Nov 13 '05 #2

P: n/a
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" <Al*********@SeeSig.Invalid> wrote in message news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...
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" <te**********@yahoo.com> wrote in message
news:5a*************************@posting.google.co m...
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

Nov 13 '05 #3

P: n/a
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" <te**********@yahoo.com> wrote in message
news:5a**************************@posting.google.c om...
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" <Al*********@SeeSig.Invalid> wrote in message
news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...
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" <te**********@yahoo.com> wrote in message
news:5a*************************@posting.google.co m...
> 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

Nov 13 '05 #4

P: n/a
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" <Al*********@SeeSig.Invalid> wrote in message news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...
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" <te**********@yahoo.com> wrote in message
news:5a**************************@posting.google.c om...
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" <Al*********@SeeSig.Invalid> wrote in message
news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...
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" <te**********@yahoo.com> wrote in message
news:5a*************************@posting.google.co m...
> 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

Nov 13 '05 #5

P: n/a
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" <te**********@yahoo.com> wrote in message
news:5a**************************@posting.google.c om...
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" <Al*********@SeeSig.Invalid> wrote in message
news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...
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.
"newbie" <te**********@yahoo.com> wrote in message
news:5a**************************@posting.google.c om...
> 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" <Al*********@SeeSig.Invalid> wrote in message
> news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...
>> 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" <te**********@yahoo.com> wrote in message
>> news:5a*************************@posting.google.co m...
>> > 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

Nov 13 '05 #6

P: n/a
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" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...
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" <te**********@yahoo.com> wrote in message
news:5a**************************@posting.google.c om...
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" <Al*********@SeeSig.Invalid> wrote in message
news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...
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.
"newbie" <te**********@yahoo.com> wrote in message
news:5a**************************@posting.google.c om...
> 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" <Al*********@SeeSig.Invalid> wrote in message
> news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...
>> 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" <te**********@yahoo.com> wrote in message
>> news:5a*************************@posting.google.co m...
>> > 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

Nov 13 '05 #7

P: n/a
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" <te**********@yahoo.com> wrote in message
news:5a**************************@posting.google.c om...
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

Nov 13 '05 #8

P: n/a
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" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...
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" <te**********@yahoo.com> wrote in message
news:5a**************************@posting.google.c om...
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

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.