Tab Form with multiple forms

[Eric]

I have 4 forms that have different information, all tied to their
corresponding tables. All 4 tables/forms have a common field (Charter ID).
Table 1 has the Primary key for this field; all others have the same field as
a foreign key not indexed. I have the relationships set to one-to-many from
Table 1.

I'm trying to pre-populate the Charter ID field to the three forms/tables...
from Form 1/table 1. This is to allow the user to start a new record in form
1, and then move through the form tabs 2-4 and the Charter ID is already
filled in and recorded in the tables 2, 3 and 4.

I have tried to use SetValue macros etc. but can't seem to get even close.
I have tried all combinations for on Open, After update, on exit etc. and
can't seem to get it.

I'll try and appreciate any and all ideas.

Form1 Primary Key Form2 Form 3 Form 4
(Charter ID) --> (Charter ID) --> (Charter ID) --> (Charter ID)

Thanks for your time.

 

[Allen Browne]

So you have a form with a tab control with 4 pages in it.

The first page is bound to Table1, which has the Charter ID primary key.

The 2nd page contains a subform bound to Table2. With the main form open in
design view, right-click the edge of the subform control, and choose
Properties. Set:
Link Master Fields Charter ID
Link Child Fields Charter ID
Save the changes. Now this subform will just the records from Table2 that
match the record in the main form. When you enter a new record in this
subform, Access will automatically assign the Charter ID value to match the
value in the main form. You don't need any code or macros.

Repeat that process for the subforms in your other 2 tab pages.

 

[Eric]

Perfect! That was great.

Ok now all the records populate from the forms but the Charter ID from the
master form is not replicating/coping to the other child tables (Charter ID).

when you look in the child tables, all data is there except the Charter ID.

Is there a way to append or copy the Charter ID from the master form to the
other sub forms on the fly, and thus record the Charter ID to the other
tables?

Thank so much, I spent hours trying to get that to work and should have
asked sooner.

 

[Allen Browne]

Access does not create related records in the other tables when you create
one in the main table.

If you need to do that, it would be possible to execute a series of append
query statements in the AfterInsert event procedure of the main form. You
would need some programming experience and some understanding of SQL to
achieve that.

But before you do, this sounds suspiciously like an unnormalized database.
I can't imagine a reason why you would want to create one automatic record
in several other tables if the database is correctly normalized.

Or perhaps I'm just being mislead by your 'rookie' designation.

 

[JvC]

Eric,

It would appear that you have a parent->child relationship between
Table 1 and your tables on Tabs 2-4, however you have set up your form
without this relationship. You need to:
-Make the datasource for the Main form table 1
-Recreate Subform 1 on Tab 1, using the data from the main form's
recordsource. You can copy the controls from Subform 1. Now you have
created the parent form.
-Get rid of Subform 1 on Tab 1
-Rebind the subforms to the main form, as Alan stated.

That should work!

John

Eric formulated on Thursday :

 

[Eric]

No, you could be right Allen.

Maybe I don't need the Charter ID field in each table? What do you think?
I guess if I have my relationships set up properly then I could only have
that field in the master and merely query calling the Charter ID from the
master and the rest of the fields from the various other tables.

Your comments are valuable, thanks

 

[Eric]

Thanks JVC, so if I'm understanding, I should only have (1) field [Charter
ID] in the master and no where else.

Then just have the other tables use [Charter ID] via relationships.

Should the forms then have a query as the source (to allow the Charter ID
field to show) or keep the source as a table?

Thanks to all!

 

[JvC]

Eric,

Let's call your main table Charters, just for fun. It has an identity
field named [Charter ID]. All data from Charters that you need to edit,
goes on Tab 1. Let's say your second table is Crew. In your Crew table
you would have [Charter ID] and [Crew Member ID]. You would set this up
as a subform, and put it on Tab 2. You would then link the [Charter ID]
fields as Link Master and Link Child, as Allen stated. When you go to
Tab 2, and add a crew member, the [Charter ID] will be automatically
populated.

John

Eric submitted this idea :

Thanks JVC, so if I'm understanding, I should only have (1) field [Charter
ID] in the master and no where else.

Then just have the other tables use [Charter ID] via relationships.

Should the forms then have a query as the source (to allow the Charter ID
field to show) or keep the source as a table?

Thanks to all!

 

[Eric]

Ok, I think I get it! I will try and let you know.

Thanks agian
--
Eric the Rookie

Eric,

Let's call your main table Charters, just for fun. It has an identity
field named [Charter ID]. All data from Charters that you need to edit,
goes on Tab 1. Let's say your second table is Crew. In your Crew table
you would have [Charter ID] and [Crew Member ID]. You would set this up
as a subform, and put it on Tab 2. You would then link the [Charter ID]
fields as Link Master and Link Child, as Allen stated. When you go to
Tab 2, and add a crew member, the [Charter ID] will be automatically
populated.

John

Eric submitted this idea :

Thanks JVC, so if I'm understanding, I should only have (1) field [Charter
ID] in the master and no where else.

Then just have the other tables use [Charter ID] via relationships.

Should the forms then have a query as the source (to allow the Charter ID
field to show) or keep the source as a table?

Thanks to all!
--
Eric the Rookie

Eric,

It would appear that you have a parent->child relationship between
Table 1 and your tables on Tabs 2-4, however you have set up your form
without this relationship. You need to:
-Make the datasource for the Main form table 1
-Recreate Subform 1 on Tab 1, using the data from the main form's
recordsource. You can copy the controls from Subform 1. Now you have
created the parent form.
-Get rid of Subform 1 on Tab 1
-Rebind the subforms to the main form, as Alan stated.

That should work!

John

Eric formulated on Thursday :
Perfect! That was great.

Ok now all the records populate from the forms but the Charter ID from the
master form is not replicating/coping to the other child tables (Charter
ID).

when you look in the child tables, all data is there except the Charter ID.


Is there a way to append or copy the Charter ID from the master form to the
other sub forms on the fly, and thus record the Charter ID to the other
tables?

Thank so much, I spent hours trying to get that to work and should have
asked sooner.
--
Eric the Rookie


:

So you have a form with a tab control with 4 pages in it.

The first page is bound to Table1, which has the Charter ID primary key.

The 2nd page contains a subform bound to Table2. With the main form open
in design view, right-click the edge of the subform control, and choose
Properties. Set:
Link Master Fields Charter ID
Link Child Fields Charter ID
Save the changes. Now this subform will just the records from Table2 that
match the record in the main form. When you enter a new record in this
subform, Access will automatically assign the Charter ID value to match
the value in the main form. You don't need any code or macros.

Repeat that process for the subforms in your other 2 tab pages.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have 4 forms that have different information, all tied to their
corresponding tables. All 4 tables/forms have a common field (Charter
ID).
Table 1 has the Primary key for this field; all others have the same
field as
a foreign key not indexed. I have the relationships set to one-to-many
from
Table 1.

I'm trying to pre-populate the Charter ID field to the three
forms/tables...
from Form 1/table 1. This is to allow the user to start a new record in
form
1, and then move through the form tabs 2-4 and the Charter ID is already
filled in and recorded in the tables 2, 3 and 4.

I have tried to use SetValue macros etc. but can't seem to get even
close. I have tried all combinations for on Open, After update, on exit
etc. and can't seem to get it.

I'll try and appreciate any and all ideas.

Form1 Primary Key Form2 Form 3 Form 4
(Charter ID) --> (Charter ID) --> (Charter ID) --> (Charter ID)

Thanks for your time.

 

[Eric]

After some great advice from Allen, I will study up on normalization and
hopefully be able to answer some of my own questions.

Thanks you guys, I appreciate this site and your helpful thoughts.
--
Eric the Rookie

Ok, I think I get it! I will try and let you know.

Thanks agian
--
Eric the Rookie

Eric,

Let's call your main table Charters, just for fun. It has an identity
field named [Charter ID]. All data from Charters that you need to edit,
goes on Tab 1. Let's say your second table is Crew. In your Crew table
you would have [Charter ID] and [Crew Member ID]. You would set this up
as a subform, and put it on Tab 2. You would then link the [Charter ID]
fields as Link Master and Link Child, as Allen stated. When you go to
Tab 2, and add a crew member, the [Charter ID] will be automatically
populated.

John

Eric submitted this idea :

Thanks JVC, so if I'm understanding, I should only have (1) field [Charter
ID] in the master and no where else.

Then just have the other tables use [Charter ID] via relationships.

Should the forms then have a query as the source (to allow the Charter ID
field to show) or keep the source as a table?

Thanks to all!
--
Eric the Rookie


:

Eric,

It would appear that you have a parent->child relationship between
Table 1 and your tables on Tabs 2-4, however you have set up your form
without this relationship. You need to:
-Make the datasource for the Main form table 1
-Recreate Subform 1 on Tab 1, using the data from the main form's
recordsource. You can copy the controls from Subform 1. Now you have
created the parent form.
-Get rid of Subform 1 on Tab 1
-Rebind the subforms to the main form, as Alan stated.

That should work!

John

Eric formulated on Thursday :
Perfect! That was great.

Ok now all the records populate from the forms but the Charter ID from the
master form is not replicating/coping to the other child tables (Charter
ID).

when you look in the child tables, all data is there except the Charter ID.


Is there a way to append or copy the Charter ID from the master form to the
other sub forms on the fly, and thus record the Charter ID to the other
tables?

Thank so much, I spent hours trying to get that to work and should have
asked sooner.
--
Eric the Rookie


:

So you have a form with a tab control with 4 pages in it.

The first page is bound to Table1, which has the Charter ID primary key.

The 2nd page contains a subform bound to Table2. With the main form open
in design view, right-click the edge of the subform control, and choose
Properties. Set:
Link Master Fields Charter ID
Link Child Fields Charter ID
Save the changes. Now this subform will just the records from Table2 that
match the record in the main form. When you enter a new record in this
subform, Access will automatically assign the Charter ID value to match
the value in the main form. You don't need any code or macros.

Repeat that process for the subforms in your other 2 tab pages.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have 4 forms that have different information, all tied to their
corresponding tables. All 4 tables/forms have a common field (Charter
ID).
Table 1 has the Primary key for this field; all others have the same
field as
a foreign key not indexed. I have the relationships set to one-to-many
from
Table 1.

I'm trying to pre-populate the Charter ID field to the three
forms/tables...
from Form 1/table 1. This is to allow the user to start a new record in
form
1, and then move through the form tabs 2-4 and the Charter ID is already
filled in and recorded in the tables 2, 3 and 4.

I have tried to use SetValue macros etc. but can't seem to get even
close. I have tried all combinations for on Open, After update, on exit
etc. and can't seem to get it.

I'll try and appreciate any and all ideas.

Form1 Primary Key Form2 Form 3 Form 4
(Charter ID) --> (Charter ID) --> (Charter ID) --> (Charter ID)

Thanks for your time.