1 form... 2 tables

  • Thread starter Thread starter David M. Levine
  • Start date Start date
D

David M. Levine

I have a form for new employee data which is linked to
the employee table. However, I need the data in several
text boxes on the form which contain salary informatin to
be dumped to a second table. I am having trouble sending
that data to its correct place using VBA. Ive been
programming in proprietary languages for too long and
cant seem to get a syntax/method which will work for me.
Any help would be appreciated. I know it should be easy
but...
 
Sorry.. miskey

Table Employee:
ID, Name, Address, Phone, SS#, Start Date.. etc.

Table Salary
ID, RaiseDate, Amount, Interval (hourly/salary)

The form has all fields from Employee and 2 text boxes
for amount and interval. I need to take the StartDate
from Employee and add that with the amount and interval
to give the initial pay in the Salary table. Currently,
I have the form linked to the Employee Table, but that
can be changed. What else can I tell ya?

Thanks
 
Is there just one entry per person in the Salary table, or
are you tracking the history of changes with multiple
records? Either way you could add EmployeeID to the salary
table and then base a subform on that table that would be
placed on the Employee form and linked by that ID. This
would allow you to view/add/edit all of the related salary
records directly from the main form.

The ease of this with subforms is one of the great features
of Access that you may not have had with your other
programming languages.

If you don't think this is the appropriate method, get back
and we can find another one.

Gary Miller
Sisters, OR
 
The Salary table tracks history. I the ID field on the
Salary table relates to the Key fild on the Employee
Table (an autonumber field). I added another
relationship so ID relates to ID. Should I delete the ID
= key field? Im concerned because the ID may change..
that is why i was linking to the Employee Key. Or is
there a way I can ensure that a change on 1 table is
duplicated on the other? Sorry if my thinking is
fragmented.. a lot going on.
 
Ok, if it is a history, I suggest that the subform method is
by far the easiest way to go.

You do not want to change the ID's at all, but if your
salary table just has one ID field it sounds like you are
using it to store the ID of the parent EmployeeID which is
correct. The table should probably have another unique
record autonumberID for a primary key. You may never use it
for anything, but it is a good design practice that every
record has a unique PK.

Maybe you do want to consider renaming at least one of the
ID fields that are holding the EmployeeID as if you join the
two in a query, Access will tend to tag on the table names
when it finds two identically named fields in the two
tables. One method would be EmpID or ID for the Employee
table and EmpFK for the Salary table and have the Salary
record use SalaryID for it's PK.

Gary Miller
Sisters, OR
 
Back
Top