It's not really clear what you have set up here. Using actual field names instead of "field 1", "field 2", etc. makes it easier to see the big picture and this helps us to help you.
How are the two tables related?
If you have this relationship:
Customer::VendorID = Vendors::VendorID
(You use "vendor" in one part of your post as your table name and name it "agents" in another so I had to pick one of the two here.)
Then you can add any fields you choose from Vendor to your Customer layout and they will display the appropriate vendor data once you have entered or selected a matching value in the Customer::vendorID field.
I thought I had done what you described above. But the third field still isn't automatically filling in.
Here's more detail. Table 1 are Customers. Table 2 is Agents. The fields for Agent table are
Field1 = Refer Source (picked from one value list)
Field 2 - Agent Name (picked from a different value list) (I can display Field 3 from this value list)
Field 3 - Mktg Credit (auto fill from Agent Name)
Is the problem the value lists?
Then what are "Vendors"? I assumed they were "agents" but now I'm not sure. Is a "vendor" a "customer"? That seems a contradiction in terms. Also, Field 2 and Field 3 both show the agent name? Wouldn't that be the same name in every case?
I'm going to use my own table and field names here. You'll need to translate them into yours accordingly.
Customers::AgentID = Agents::AgentID
However you name them, you have many customer records linked to a second table, so the serial number field must be defined in that second table rather than customers here.
The Layout should be based on Customers, not Agents. (Layout should be based on whatever table you use on the left side of the above relationship.)
Field 1: (Refer Source) should be defined as a field in the Customers table. Otherwise, all customers for the same Agent will show the same Refer Source value here. Thus, it is not affected by the presence or absence of a related record in any way.
Field 2: AgentID this is not the agent name, but a number field and used to link a Customers record to an Agent record. Format it as a drop down or pop up of Agent ID's from Agents in field 1, includ Agent Name in field 2 so that selecting an Agent Name enters that Agent's ID number.
If you format Field 2 as a pop up with the show only second field option specified in its value list definition, Field 2 serves to show the agent name. If you prefer a drop down, add the Agent Name field from Agents to your layout so that the name will be displayed here when you exit the drop down list.
Field 3: You have two choices. You can add the field with this value from Agents to your customers layout or you can define a field in Customers that uses a looked up value field option to copy the value from Agents. Both produce the same results when you select or enter an Agent ID in the Customers::AgentID field, but with a looked up value setting, editing the value in the Agents table does not automatically update the looked up value field in Customers. This may or may not be a desirable thing, so you'll need to decide on methods here.
This is working great. Thank you,
I have another question regarding related tables. I have a section of each record that has insurance agent contact information and it is added to each record via (3) different relational tables (the tables are based on different "types" of agents.) After selecting which type of agent it fills in their contact data nicely. How would I fill in the contact information again in other fields in my layout (it's for billing purposes.)
So if InsideAdjuster:InsisdeAdjusterID were selected than Customer:Billt o would fill in as well.