lookup table won't refresh

[Julia Flint]

I have a client with two tables -- one for HR, one for Benefits. The person working on the HR table is responsible for updating addresses, job titles, etc. The Benefits table use the lookup function to pull about 10 fields from the HR database.

When the Benefits person enters a new record, she inputs the social security number and the corresponding demographic information pulls into the database no problem.

However, if the HR person makes a *change* to an existing employee, the corresponding information is not changed in the Benefits table.

What am I missing here? Is there a way to "refresh" the data so that the changes come over to the Benefits database?

Thanks much.
Julia Flint

[Tom Cone Jr]

RE: lookup table won't refresh

It sounds as though the ten fields in the Benefits table are 'filled in' by lookups to the HR table as new records are entered by the Benefits person, right?

There are several possible solutions here, but understanding how you have things set up to begin with is critical to making the right choice.

Can you walk us through the data entry process in smaller steps?
Are the tables linked in a set? If so, how?
How are the ten fields in Benefits 'filled in' from the HR table?
Does every HR record *always* have a corresponding Benefit record in the second table?

-- tom

[Julia Flint]

RE: lookup table won't refresh

>>Are the tables linked in a set? If so, how?>How are the ten fields in Benefits 'filled in' from the HR table?>Does every HR record *always* have a corresponding Benefit record in the second table?

[Tom Cone Jr]

RE: lookup table won't refresh

Julia, it sounds to me as though you might want to attach a script to the OnSave event in the HR table's data entry form. When a new record is entered there, or a change is saved, the script could
...store the necessary fields in the current record to variables
...go open the Benefits table
...determine if an employee record exists there or not
...if not stop
...if present, then copy the values from the variables to the corresponding records in the Benefit table
...then close the Benefits table
...then stop

This sequence should work fine unless your form repositions the record pointer in the HR table immediately after saving a record. Since the OnSave script fires after the record is saved I'm not sure it would fire before the report pointer moves, if you see what I mean.

Another possible alternative would be to 'post' values from the HR table to the Benefits table, but I'm uncertain how that would work if there is no matching employee record waiting for the update/replace in the Benefits table. This approach would involve defining a post field rule for the fields in the HR table you want to always be mirrored in the Benefits table. Choose the option to 'replace' fields in the destination table, not 'add' or 'subtract'.

Yet another approach would be drop the 'duplicate' fields from the Benefits table altogether. Since your SSAN is available to create a 1:1 link between HR and Benefits Table you could design a set, with Benefits as parent and HR as child (or vice versa). Fields from *both* tables would be available in report layouts and in forms, provided the form layout is based on the set... not either table. Database purists would recommend this approach first since you don't waste disk storage space keeping 'duplicate' information in multiple tables... also, you save the hassle of synchronizing changes, altogether. If don't want the Benefits person to change values in the HR table, just base your form on the set and make those fields read-only in your form. Then use a separate form for the data entry person working with the HR table.

Lots of options here.

Good luck.

-- tom