Pull Data from Table or Return New Data to Said Table 1

[roxannep]

I am trying to set up a module that pulls the address information (address, city, st, zip) from a table called HOUSEHOLDS to the table called PLAYERS when I enter the phone in PLAYERS. It should compare it to HOUSHOLDS and pull the appropriate data if it finds a match. I keep getting an error for new records that don't yet have a match in HOUSEHOLDS. This is the second step. If there is not record in HOUSEHOLDS that matches on PHONE, then I need to have the address information from the current record I'm editing in PLAYERS copy into HOUSEHOLDS once I've entered the data in fields PHONE, ADDRESS, CITY, ST, ZIP.

Of course, I have it working great when it finds a match, just not when it's new and then needs to copy it for future use.

 

[ImpMan]

It sounds as if you have made a link from the two tables on phone number. This is not quite right because you are using the two table as two separate databases (not a good idea).

A better solution would be to have only one address area. What is going to happen when someone moves house?? You then have two tables to keep in sync. What if you only update one table (by mistake)? Then you don't know which is the latest. A better solution would be to do the following:
1. If the phonenumber exists in the households table then in your players table add the player name and a HouseID.

2. If the address odes not exist add it to the households table and then do the step above.

3. Link HouseID in the player table to the HouseID in the households table in relationships view.

Easy

Anyway back to the original problem. If there is a link remove it.