What relationship have you defined linking Orders to Customer_Names?
(Selecting names in this fashion seems a bit chancy anyway as you can easily get two customers with the same first and last names. There are other methods for looking up customers by name that can handle such duplication while still working with unique ID numbers to tell one customer from another.)
Your last request can be performed with a script triggered when you exit the last name field, but first you have to get that value list working.
Thanks for your response.
My relationship is between the Orders:Last_Name field to the Customer_Names:Last_Name field. I do also have a Customer_Names:Customer_No field that I could link to (though I don't have it defined as a foreign key in the Orders table at this point.
I do realize and agree with you that having a customer with the same first and last names can be an issue, however, I simply don't know how to set up the system for doing what you described (especially in the way I am trying to set up the date entry interface. If you could tell me what I need to do to account for that possibility, I would appreciate it.
Here's the "simple" approach. There are more sophisticated methods that I've demo'ed in a file that I'll include the download link to at the end of this post, but getting the basic set up working first lays the ground work for more sophisticated approaches if you decide to try them. (The demo file illustrates approaches that make it easier to work with large value lists while still using ID numbers in place of names.
First, add the Customer_No field to Orders as your foreign key field. This field will let you link customer records to orders and the links will work for you even when you have two customers named "John Smith" or when "Mary Jones" get's married and changes her name to "Mary Adams". The link also still works when you start a new customer record for John Smith and then discover it's really John Schmidt.
Set up this relationship:
Customer_Names::Customer_No = Orders::Customer_No
Define a calculation field in Customer_Names called cFullName that has this expression: LastName & ", " & FirstName.
Define a value list using the specify field option to list Customer_Names::Customer_No as Field1 and Customer_Names::cFullName as field 2.
Put Orders::Customer_No on your orders layout and set it up as a pop-up menu or drop down list using this value list. Add Name fields from Customer_Names to your Orders layout and when you select a customer in the Customer_No field, the customer's name will automatically appear in these name fields.
Once you get that working, you can make your value list more sophisticated if you add more fields to your full name calculation so that you can tell the difference between two people of the same name. You can include a phone number or address field, to name two possibilities. That way, when you select "John Smith" you can ask your customer "are you the John Smith with phone number 555-5555?" to determine who's number to select.
More enhanced methods can be set up where you can search by partial names or use an auto-complete enabled drop down list of names--but both require scripting to pull up the ID numbers and to handle duplicate names effectively. Here's a demo file that shows off several such approaches: