Forms...Copying data from one table to another

  • Thread starter Thread starter Paul G.
  • Start date Start date
P

Paul G.

Using Access 2000, I have four tables and two forms. The first form will
update the table "Master" with three fields, one of which is StudyNumber,
the primary key for this and the other tables.
The second form will update the other three tables and will be populated by
a different user. This user will not see or enter the StudyNumber in the
second form.

How do I set my forms up so that when the second form is complete the
StudyNumber will be copied from the first table to the other three tables
and saved with their respective data.

The StudyNumber is sequential and is generated by the first user. The
StudyNumber needed by the second form will always be the max StudyNumber of
the first table. There is also a one to many relationship between Master
and the other tables.

I am new to the access arena, so any help will be appreciated.

Thanks
 
Using Access 2000, I have four tables and two forms. The first form will
update the table "Master" with three fields, one of which is StudyNumber,
the primary key for this and the other tables.
Click to expand...

Ummm... primary key in ALL THREE tables? Such one-to-one relationships
are sometimes valid but it's QUITE uncommon. See below.
The second form will update the other three tables and will be populated by
a different user. This user will not see or enter the StudyNumber in the
second form.

How do I set my forms up so that when the second form is complete the
StudyNumber will be copied from the first table to the other three tables
and saved with their respective data.
Click to expand...

If you *were* to set it up in this way it would be a mistake. Creating
empty "placeholder" records is essentially never the right way to go.
The StudyNumber is sequential and is generated by the first user. The
StudyNumber needed by the second form will always be the max StudyNumber of
the first table. There is also a one to many relationship between Master
and the other tables.
Click to expand...

This is a direct contradiction: The StudyNumber is the Primary Key, so
there can be by definition one and only one record for that
StudyNumber. I think you want the StudyNumber to be a *FOREIGN* key,
not the Primary Key, in order to get a one to many relationship.

The simplest way to do this is to use a Form based on the Master
table, or a Query based on the Master table. The user need not see the
StudyNumber if it's important to conceal it from them, but it should
be part of the Form's Recordsource query; on the Form you would have
Subforms for the related tables, using StudyNumber as the Master/Child
Link Field.

The Query could select the record with the largest StudyNumber by
putting a criterion

=DMax("[StudyNumber]", "[Master]")

as a criterion on the StudyNumber. Note that doing so will make it
impossible to view or edit existing records other than the single most
recent record.
 
I will try changing it to using one form
while incorporating the subforms and/or tabs and remove the restriction on
the user from seeing the StudyNumber.
Click to expand...

You can still maintain that restriction if you wish! The second user
could make use of a Form based on the study table, with the Study
Number as part of the form's recordsource but without any control
displaying it. They'll need SOME way to locate the desired record to
edit), of course.
 
Back
Top