Adding records in linked tables 
Author Message
 Adding records in linked tables

Hi Everone -

First of all, many thanks to all who responded to my last post. Your
suggestions proved to be very valuable.

I have another question: If you have two tables that have related key
fields, what is the best way to update the related key fields of one
table when you add a new record to the other table? Here's my database:

It includes three tables, one containing information about the
individual store, one containing employee personnel data and the last
containing payroll information about each employee. They are set up as
follows:

Table 1: StoreData
Key:
StoreNum (Integer)
Non-key:
Description (Text, 30 Ch)

Table 2: EmpData
Keys:
EmpNum (Integer)
StoreNum (Foreign Key, Integer)
Non-key:
SSN (Text, 11 ch)
FirstName (Text, 15 ch)
LastName (Text, 15ch)
Addr1 (Text, 30 ch)
Addr2 (Text, 30 ch)
City (Text, 20 ch)
State (Text, 2 ch)
Zipcode (Text, 10)
PhoneNum(Text, 12 ch)

Table 3: PayData
Keys:
StoreNum (Foreign key, Integer)
EmpNum (Foreign key, Integer)
SSN (Text, 11 ch)
Non-keys:
Wage (Currency)
Salaried (Boolean)
ExemptStat (Boolean)
Exemptions (Integer)
ExtraDeductions (Currency)

I have set up a couple of screens to access the data in each table. When
I add a new employee to the EmpData table, I would like to migrate the
keys to the PayData table, then bring up the screen for PayData and have
the user enter the rest of the data at that point. What's the best way
to do this?

Thanks in advance

Pete Davini

                       \\\\|||||////
                        \\       //
                         \ ~   ~ /
                       (~  0   0  ~)
  --------------------o000^--V--^000o-----------------------
       Pete Davini                   Heurikon Corporation

             "Everything should be made as simple
                 as possible, but no simpler"
                       Albert Einstein    
  ----------------------------------------------------------



Fri, 18 Sep 1998 03:00:00 GMT  
 Adding records in linked tables
I have set up a couple of screens to access the data in each table. When
I add a new employee to the EmpData table, I would like to migrate the
keys to the PayData table, then bring up the screen for PayData and have
the user enter the rest of the data at that point. What's the best way
to do this?

----

You should be able to pick up the current values of the keys from
either the controls on the employee form or from the recordset. Then
open the pay data form, set the values of the key fields on the form,
and then display it for the user to complete data entry. Depending on
the rules which apply to the PayData table, you might also be able to
just insert the new record into the table based on the keys available
from the employee form using DAO or a SQL Insert. Then you could just
open the pay data form and display the new record.

My preference is, generally, to work with unbound data on forms and
use code to manipulate the underlying tables. IMHO, bound controls
are more trouble than they're worth in the long haul.

        Joe



Tue, 22 Sep 1998 03:00:00 GMT  
 Adding records in linked tables

Quote:

>I have set up a couple of screens to access the data in each table. When
>I add a new employee to the EmpData table, I would like to migrate the
>keys to the PayData table, then bring up the screen for PayData and have
>the user enter the rest of the data at that point. What's the best way
>to do this?

>----

>You should be able to pick up the current values of the keys from
>either the controls on the employee form or from the recordset. Then
>open the pay data form, set the values of the key fields on the form,
>and then display it for the user to complete data entry. Depending on
>the rules which apply to the PayData table, you might also be able to
>just insert the new record into the table based on the keys available
>from the employee form using DAO or a SQL Insert. Then you could just
>open the pay data form and display the new record.

>My preference is, generally, to work with unbound data on forms and
>use code to manipulate the underlying tables. IMHO, bound controls
>are more trouble than they're worth in the long haul.

>    Joe

Thinking from a broader perspective ....

The correct way to do this is to have your dba define a view so the
front end does not need to manage the transaction.  If they are defined
properly, business rules and referential integrity are easier to manage.



Sat, 26 Sep 1998 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. need something like foreach record in table - add record to another table

2. Cannot add record to Visual Foxpro table after deleting all records in table

3. Checking if a record is duplicated before adding or during adding it to a table

4. deleting records in a table and then adding new records

5. ADO2.5, can't add new record to an empty table using record binding

6. Changing the linked table path without the linked table manager

7. Can't add new records when using Linked forms

8. Can't access records with sub form for linked sub table

9. Link Table - blank records

10. no records appear on form linked to table.

11. Help New Record on form stops refreshing of linked tables

12. Linking Records in Different Tables?

 

 
Powered by phpBB® Forum Software