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.