Auto-filling fields based on mutiple fields

Discussion created by bobrace on Jan 24, 2014
Latest reply on Jan 24, 2014 by keywords

I have 2 tables :


SSN_NAMES Table (names with social security number)

TRANSACTIONS Table (where tranactions for customers are stored.


These table are joined by SERIAL fields


The transactions table has 6 name fields in it - name_1 thru Name_6.  The transactions table also has 6 soical security number fields - SNN_1 thru SNN_6.

What I would like the user to be able to do is when they are in the TRANSACTIONS table, and they enter a social security number in SNN_1, pull over that person's name into thre NAME_1 field. When the user goes to the SNN_2 field and enters a valid sociel security number, pull over the person's name ( associated with this social security number) for NAME_2, and so on.


I have tried to do this with a value list in the names field and also tried with a lookup function for these fields, but when I use a look up function in the name fields, and I type a social, it puts the same name in all name fields. 


How do I get it to pull the matching name for each social entered:


in TRANSACTION Table - ssn field, type 123456789 and in the TRANSACTION Table - name field, autofill with name os person matching the entered social.