Multi-Table Entry Form - 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 code or example that I can look at?

Please advise.

Thanks in advance,
SK

 

[John Vinson]

Is this the best way of achieving what I want to do or
their is another better way?

Probably so: base your Form on the Employee table only, and on it put
a Subform based on the EmployeeMisc table using the EmployeeID as the
master/child linking field.

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).

This should be enforced in the Relationships window by creating a
relationship between the two tables, relational integrity enforced.
The form/subform will ensure it's enforced too, but the relationship
makes it impossible to add a violating record.