Multi-Table Forms - What is the best way?

[SK]

Greetings,

I am trying to design a multi-table Form in Access 2000.
The two tables in question(Employee and EmployeeMisc)
have a common Key(one-to-one relationship). Basically, I
have the most-oftenly used info in one table and the rest
of it in the other. I want to create a Form which can be
used to:

- Create New Employee Info based on both the tables
- Update existing Employee Info based on both the tables

So far, I have thought of the following:
1. Create a Form using the Wizard and choose both the
tables(and all of their fields). This sets the
Recorsource property to a query based on both the tables
and inherits the table relationship.

2. On the Form, set the Visible property of Employee Code
(and label) for the 2nd table(EmployeeMisc) to False.

3. On the Before Update Event, set the following:
EmployeeCodeOf2ndTable = EmployeeCodeOf1st Table

Is this the best way of achieving what I want to do or
their is another better way? Also, I do not want to
create orphan records(I want to make sure that records in
Employee table can exist even though they maynot exist in
the 2nd table and NOT vice-versa).

Is their any sample DB or program I can look at?

Please advise.

Thanks in advance,
SK

 

[Marty Suckstorff]

-----Original Message-----
Greetings,

I am trying to design a multi-table Form in Access 2000.
The two tables in question(Employee and EmployeeMisc)
have a common Key(one-to-one relationship). Basically, I
have the most-oftenly used info in one table and the rest
of it in the other. I want to create a Form which can be
used to:

- Create New Employee Info based on both the tables
- Update existing Employee Info based on both the tables

So far, I have thought of the following:
1. Create a Form using the Wizard and choose both the
tables(and all of their fields). This sets the
Recorsource property to a query based on both the tables
and inherits the table relationship.

2. On the Form, set the Visible property of Employee Code
(and label) for the 2nd table(EmployeeMisc) to False.

3. On the Before Update Event, set the following:
EmployeeCodeOf2ndTable = EmployeeCodeOf1st Table

Is this the best way of achieving what I want to do or
their is another better way? Also, I do not want to
create orphan records(I want to make sure that records in
Employee table can exist even though they maynot exist in
the 2nd table and NOT vice-versa).

Is their any sample DB or program I can look at?

Please advise.

Thanks in advance,
SK
.
If your two tables have a one-to-one relationship and the

two things you want to do is create new records in the
manner you describe and update existing records in the
same manner I see absolutely no reason for having two
tables to begin with! Maybe two different input forms,
two different queries, or something. But why two tables?
Did you read this somewhere and misinterpret what was
being told to you?

 

[SK]

No, I do not think that I misinterpreted anything. I have
broken the table into two tables so that I do not end up
with one large table. This will help in the performance
(as in most of the queries I need data from one table and
will not have to load all the information when I am
dealing with queries or recordsets) and also allow the
tables to be more flexible(adding fields) as I think that
this may happen in the future.

Also, it will be easier to secure data(if I have to do it
later on).

SK